Mid-Term Exam Financial Management Business

Anonymous
timer Asked: Mar 14th, 2019
account_balance_wallet $50

Question Description

Financial Management Financial Management Financial Management Financial Management midterm

Attachment preview

Problem 1
Format the area below to look as much like the picture to the right as possible. Do not include the arrows.

Problem 2
You are given the data for a company for 12 months. The company uses short-term loans and marketable securities investments to bring the cash balance to the desired level at the end of each month. Short-term loans will be paid down or paid off with any excess balances and marketable securities will be used only when the loan balance is zero. Marketable securities will be sold first to supply any needed cash infusions before loans are used. The company had no marketable securities and no short-term loans at the beginning of January. 

This is a continuation from Problem 2.  Using the Cash Budget in problem 2.  Name the input cells for collections Collect0, Collect1, and Collect 2. Then create and save three scenarios using the Scenario Manager that show the company's monthly net cash flow for April - December while changing the collection rates. The BEST scenario will Collect0 = 30%, Collect1 = 60%, and Collect2 = 10%, the BASE scenario will use the base case collection rates given here, and the WORST scenario will have Collect0 = 0%, Collect1 = 50%, and Collect2 = 50% . Save a summary of the scenarios on a separate tabbed page. 

Unformatted Attachment Preview

This is your personal Mid-Term Exam File. You are to only use the file that was sent t else's file as this will be considered a ACADEMIC DISHONESTY AND WILL RESULT IN A COURSE. Caution: This is an EXAMINATION that you are to complete outside of class. Do not be mistaken homework assignment. Because this is an examination you are expected to complete this on yo not to work on or discuss the examination with anyone else other than with your instructor. You assistance with any part of this exam from anybody other then your instructor. Any assistance o will be considered ACADEMIC DISHONESTY AND WILL RESULT IN A FAILING (F) GRADE FOR THIS C from the tutorials, homework assignments, practice problems, and your textbook to help you de questions found in this examination. DO NOT COPY AND PASTE COMPLETE MODELS OR ANY POR TUTORIAL, HOMEWORK ASSIGNMENTS, OR PRACTICE PROBLEMS. YOU MUST CREATE YOUR MO INFORMATION GIVEN IN THE EXAM. IMPORTANT: SAVE THIS SPREADSHEET TO THE DESKTOP OF THE COMPUTER YOU ARE USING WIT RESAVE IT OFTEN WHILE YOU ARE WORKING ON IT. SAVE THIS FILE BACK TO YOUR COMPUTER WITH YOUR NAME IN THE FILENAME AND RESAVE IT COMPLETING THE EXAM. IT IS YOUR RESPONSIBILiTY TO PROTECT YOUR WORK AGAINST TECHN BE EXTENDED DUE TO TECHNICAL FAILURES INCLUDING LOSS OF FILE OR CORRUPTION. IT IS ADV MULTIPLE LOCATIONS. When you have completed this exam spreadsheet: Save it one last time to your computer. Subm MARCH 15th by 11:59 pm. There are 100 points available from the take home portion of this exam. Points are shown on ea where possible. We will have the in class portion of the Mid-Term exam on March 12th at 9:30 in our classroom. be worth 25 points. There will not be any make ups given!! This Exam is worth a total of 125 points. Please let me know if you have any questions. This test belongs to Ibrahim Almusalit the file that was sent to you. Do not use somebody AND WILL RESULT IN A FAILING (F) GRADE FOR THIS class. Do not be mistaken thinking that this is a simple ted to complete this on your own. This means that you are n with your instructor. You are not to receive help and structor. Any assistance other then from an approved source LING (F) GRADE FOR THIS COURSE. You may use material ur textbook to help you develop your models and answer the LETE MODELS OR ANY PORTION OF THE MODELS FROM THE U MUST CREATE YOUR MODELS FROM SCRATCH USING PUTER YOU ARE USING WITH YOUR NAME IN THE FILENAME. ILENAME AND RESAVE IT OFTEN WHILE YOU ARE UR WORK AGAINST TECHNICAL DIFFICULTIES. TIME WILL NOT OR CORRUPTION. IT IS ADVISABLE TO SAVE YOUR WORK IN me to your computer. Submit it via the Canvas dropbox by m. Points are shown on each tab. Partial credit will be given h at 9:30 in our classroom. This portion of the Mid-Term will Problem 1: Format the area below to look as much like the picture to the right as possible. Do not include the arrows. Elvis Products International Income Statement For the Year Ended Dec. 31, 2018 Sales Cost of Goods Sold Gross Profit Selling and G&A Expenses Fixed Expenses Depreciation Expense EBIT Interest Expense Earnings Before Taxes Taxes Net Income 2018 6950000 6275000 675000 345000 120000 21250 188750 85000 103750 41500 62250 2017 6725000 6150000 575000 225000 120000 17900 212100 67500 144600 57840 86760 Rows 11-13 are 36 pixels high. Text is centered horizontally and vertically Col C Column B Col D Elvis Products International ows 11-13 are 6 pixels high. ext is centered orizontally and ertically Income Statement For the Year Ended Dec. 31, 2018 2018 Sales Cost of Goods Sold 2017 $ 6,950,000 6,275,000 $ 6,725,000 6,150,000 Gross Profit Selling and G&A Expenses Fixed Expenses Depreciation Expense $ 675,000 345,000 120,000 21,250 $ 575,000 225,000 120,000 17,900 EBIT Interest Expense $ 188,750 85,000 $ 212,100 67,500 Earnings Before Taxes Taxes $ 103,750 41,500 $ 144,600 57,840 Net Income $ 62,250 $ 86,760 Column B is 35 characters wide Columns C and D are 14 characters wide 00 00 00 00 00 00 00 00 00 40 60 Ibrahim Almusalit Ibrahim Almusalit Problem 2: You are given the data for a company for 12 months. The company uses short-term loans and marketabl the desired level at the end of each month. Short-term loans will be paid down or paid off with any exce only when the loan balance is zero. Marketable securities will be sold first to supply any needed cash inf Note that the marketable securities and no short-term loans at the beginning of January. inputs are in dollars but the The company expects to make an outlay for new capital equipment in May, June, July, August, Septemb Cell L24 for the user to select the month of the outlay. Title the list as "Capital Outlay" and create an inst statements Capital Outlay from the list below." Use this and other input information to complete below are in the Cash Budget. short-term loans at the end of each month in Row 52 and the balancethousands of marketable of securities at the en positive balances when marketable securities are being used, and it should show zero when marketable Format the range F45:N45 so that all values less than zero display as red numbers instead of black numb Name the input cells for collections Collect0, Collect1, and Collect 2. Then create and save three scenarios using th cash flow for April - December while changing the collection rates. The BEST scenario will Collect0 = 30%, Collect1 base case collection rates given here, and the WORST scenario will have Collect0 = 0%, Collect1 = 50%, and Collect titled "Scenario Summary. INPUTS Collections on sales in the month of the sale Collections on sales in the month following the sale Collections on sales in the second month following the sale 20% 55% 25% Cash Operating Expenses as a Percentage of Current Month Sales 40% Jan Expected Sales Feb Mar Apr May $ 7.875 $ 9.188 $ 9.975 $ 11.025 $ 7.350 Collections on Sales Cash Operating Expenses Fixed Expenses Lease Payments Capital Outlay Tax Payments $ $ $ $ 550 $ 675 $ 130 $ 550 - Total Cash Outflows $ 1.355 $ 550 - CASH BUDGET Mar Beginning Cash Balance Apr May Net Cash Flow Unadjusted Ending Cash Balance Adjustment Needed Ending Cash Balance 10.000 Short-Term Loans and Marketable Securi Mar Short-Term Loans Outstanding Marketable Securities Apr - May rm loans and marketable securities investments to bring the cash balance to or paid off with any excess balances and marketable securities will be used pply any needed cash infusions before loans are used. The company had no , July, August, September, October or November. Create a drop-down list in utlay" and create an instruction box saying "Select the desired month of the mplete the Cash Budget. Then complete the section that gives the balance of able securities at the end of each month in Row 53. The formulas should show w zero when marketable securities are not being used. s instead of black numbers for any values of the inputs. ve three scenarios using the Scenario Manager that show the company's monthly net ll Collect0 = 30%, Collect1 = 60%, and Collect2 = 10%, the BASE scenario will use the Collect1 = 50%, and Collect2 = 50% . Save a summary of the scenarios on the next tab NPUTS Capital Outlay for New Equipment $ 80.000 Month of the Capital Outlay Desired End-Of-Month cash balance Jun Jul Aug $ 15.000 Sep Oct Nov Dec $ 9.450 $ 11.130 $ 12.075 $ 9.450 $ 7.875 $ 6.825 $ 5.775 $ $ 550 $ $ 550 $ 675 $ 550 $ $ 550 $ $ 550 $ 675 $ $ - 130 $ - - $ 550 $ 1.355 $ $ $ 550 $ 130 $ 550 $ $ $ $ 550 - 550 $ 1.355 $ 550 $ 550 $ BUDGET Jun Jul Aug Sep Oct Nov Dec nd Marketable Securities Jun Jul Aug Sep Oct Nov Dec Ibrahim A Ibrahim Almusalit This is a continuation from Problem 2. Using the Cash Budget in problem 2. Name the input cells for collection scenarios using the Scenario Manager that show the company's monthly net cash flow for April Collect0 = 30%, Collect1 = 60%, and Collect2 = 10%, the BASE scenario will use the base case collection rates giv Collect1 = 50%, and Collect2 = 50% . Save a summary of the scenarios on a separate tabbed page. Name the input cells for collections Collect0, Collect1, and Collect 2. Then create and save three ash flow for April - December while changing the collection rates. The BEST scenario will the base case collection rates given here, and the WORST scenario will have Collect0 = 0%, arate tabbed page. Ibrahim Almusalit Ibrahim Almusalit Problem 3: You need forecast the 2019 and 2020 pro forma income statement and balance sheet for the firm whose 2017 and 201 income statements and balance sheets are given here. Inputs are provided for most items in the Inputs section below The cost of goods sold in 2019 and 2020 are expected to change with sales by 103% and 105% respectively arithmetic average of the proportion of this item in relation to sales for 2017 and 2018. Selling and G&A Expenses, Accounts receivable, Inventory, and Accounts Payable are expected to change with sales at 100% of the two arithmetic average of their percentage of sales for 2017 and 2018. The firm has planned an investment of $225,000 in equipment in 2019. This equipment will be depreciated at $45,000 per year. Depreciation on existing Plant/Equipmen be the same as it was in 2017. Interest expense for 2019 is computed on the 2018 ending balances in Short Term Note Payable and Long Term Debt. Inputs for those interest rates are provided in the Inputs section. Complete the pro-forma income statement and balance sheet for 2019 and 2020 using the information above, the inp below, and the values that are given in the statements. The 2019 and 2020 projected statements should accurately ad for any changes in the inputs. Compute the excess or deficit of financing for 2019 in the yellow box at the bottom of the Balance Sheet. This numbe should be positive if the firm will have more financing than is needed, and it should be negative if the firm has less financing than is needed. If you have excess financing for 2019 reduce your reduce the 2019 Long Term debt financing Calculate your 2020 interest expense using the 2019 ending balances in Short Term Notes Payable and Long Term Deb Inputs for those interest rates are provided in the input section Compute the excess or deficit of financing for 2020 in the yellow box at the bottom of the Balance Sheet. This numbe should be positive if the firm will have more financing than is needed, and it should be negative if the firm has less financing than is needed. INPUTS 2019 Percent Change in Sales from 2018 2020 Percent Change in Sales from 2019 Interest Rate on Short Term Notes Payable Interest Rate on Long Term Debt Tax Rate for 2019 and 2020 Common Stock Dividend for 2019 and 2020 Expected addition to Plant and Equipment in 2019 Additional depreciation on new Plant/Equip in 2019 and 2020 5,000% 5,000% 5,250% 6,750% 39,0% $125.000 $225.000 $45.000 Income Statement 2017 Sales Cost of Goods Sold Gross Profit 2018 3.514.000 2.284.100 3.795.120 2.656.584 1.229.900 1.138.536 2019 350.000 120.000 30.000 375.000 125.000 32.500 EBIT Interest Expense 729.900 56.000 606.036 62.900 Earnings Before Taxes Taxes 673.900 235.800 543.136 207.600 Net Income 438.100 335.536 Selling and G&A Expenses Fixed Expenses Depreciation Expense 130.000 Balance Sheet Assets Cash and Equivalents Accounts Receivable Inventory 52.000 406.000 854.000 2018 118.036 540.000 740.000 1.312.000 429.000 126.000 1.398.036 580.000 158.500 303.000 421.500 1.615.000 1.819.536 130.000 179.000 118.000 150.000 210.000 85.000 190.000 95.000 Total Current Liabilities Long-term Debt 427.000 614.000 445.000 500.000 525.000 Total Liabilities Common Stock Retained Earnings 1.041.000 395.000 179.000 945.000 395.000 479.536 574.000 874.536 1.615.000 1.819.536 Total Current Assets Plant & Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owner's Equity Accounts Payable Short-term Notes Payable Other Current Liabilities Total Shareholder's Equity Total Liabilities and Owner's Equity Excess/(Deficit) Financing for 2019 and 2020 2017 2019 118.036 395.000 $ - he firm whose 2017 and 2018 in the Inputs section below. respectively of the two-year lling and G&A Expenses, t 100% of the two-year n investment of $225,000 in new on existing Plant/Equipment will alances in Short Term Notes information above, the inputs ments should accurately adjust Balance Sheet. This number ative if the firm has less 19 Long Term debt financing. Payable and Long Term Debt. Balance Sheet. This number ative if the firm has less 2020 135.000 2020 118.036 185.000 100.000 525.000 395.000 $ - f Ibrahim Almusa Ibrahim Almusalit Problem 4: INSTRUCTIONS: Use the space beginning in Row 30 to create an amoritzation table model that will work for ANY ALLOWABLE values of changeable inputs are in red. Create restrictions on the input cells that prevent users from entering values that are not The amount of the loan must be a positive number. The balloon payment must be a positive number or zero and must be less than the amount of the loan. The term of the loan can be 1, 2, 3, 4, 5, 6, 7, 8, 9, or 10 years. The interest rate can be between 3% and 15%. The payment frequency can be annual, quarterly, or monthly. Use a drop-down list in Cell F26 with "Annual", "Semi "Quarterly" and "Monthly" as the choices. Use the results from that cell to set the payment frequency for computation Each row in your table should show the monthly payment, the interest portion of that payment, the principal portion o and the balance immediately following that payment for all payments within the term of the loan. Rows in the table th the term of the loan should show nothing (be blank) except for the payment number. All values in the table should be or zero. In cell H23, create a formula that computes the total dollar amount of interest that will be paid over the life of the loan inputs. In cell H26, create a formula that computes the effective annual interest rate for the loan given the inputs. INPUTS: Amount of Loan: Term of Loan in Years Annual Interest Rate on Loan: Balloon Payment Payment Frequency Payment Number 0 1 2 3 4 5 6 7 8 9 10 11 12 Payment Interest $250.000 10 3,00% $75.000 Semi-Annual Total Interest Paid over life of loan Principal Balance Effective Annual Interest Rate 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 work for ANY ALLOWABLE values of the inputs. Userrs from entering values that are not allowed. amount of the loan. in Cell F26 with "Annual", "Semi-Annual", payment frequency for computation in the table. hat payment, the principal portion of that payment, rm of the loan. Rows in the table that are beyond er. All values in the table should be positive numbers will be paid over the life of the loan. given the e loan given the inputs. 1 Ibrahim A Ibrahim Almusalit Problem 5: The MACRS depreciation rates are given in the table to the right. Using the inputs provided below, create whatever formulas are needed to compute the book value of the asset at the end of the year shown in the input. Your method must work for any allowed values of the inputs. The inputs for depreciation class and years owned are drop-down lists. Do not change those cells. Depreciation Class Purchase Price Years owned Book Value of the asset 7-Year $50.000 2 MACRS Pe Year 1 2 3 4 5 6 7 8 9 10 11 MACRS Percentages 3-year 33,33% 44,45% 14,81% 7,41% 5-year 20,00% 32,00% 19,20% 11,52% 11,52% 5,76% 7-year 14,29% 24,49% 17,49% 12,49% 8,93% 8,92% 8,93% 4,46% 10-year 10,00% 18,00% 14,40% 11,52% 9,22% 7,37% 6,55% 6,55% 6,56% 6,55% 3,28% 3-Year 5-Year 7-Year 10-Year 1 2 3 4 5 6 7 8 9 10 Ibrahim Ibrahim Almusalit Problem 6: The Supreme Shoe Company is considering the purchase of a new, fully-automated machine to replace a manually ope being replaced, now 1 year old, originally had an expected life of 4 years, is being depreciated as a 5-year MACRS asset of $103,500. It can be sold now for $49,500. If the old machine is used for 4 more years instead of being replaced now, $11,250 before-tax residual value at that time. The annual cash expenses for the old machine are $22,500. The replacement machine being considered has a purchase price of $135,000 and an expected salvage value of $56,250 life. There will also be shipping and installation expenses of $7,875. The constant investment in raw materials would inc $13,500 from the current level with the old machine if the new machine is purchased and the old one is salvaged. The c annual cash expenses for the new machine will be $19,125. The new machine will also result in additional productive ca increase by $33,750 per year due to increased output. The new machine will be depreciated as a 5-year MACRS Before considering this project, the company undertook an engineering analysis of current facilities to determine if oth necessitated by the purchase of the machine. The study cost the company $12,500 and determined that existing faciliti machine with no other changes. In order to purchase the new machine, the company would have to take on new debt interest, resulting in increased interest expense of $12,000 per year. The required rate of return for this project is 9% a marginal tax rate is 34%. Use the space below to compute the initial outlay, the annual cash flows, and the terminal cash flows for this project. to be linked to your computations are the ones shown. All other numbers can be entered straight into your formula need to done in the formulas. The MACRS depreciation percentages are in the table to the right. You do not need to d and the model only needs to work for this time period and for this depreciation type. Show all computations. Label al THE NUMBER RESULTS ARE WHAT IS IMPORTANT, NOT THE FORMATTING. YOU CAN SET THIS UP ANY WAY THAT YO THE 4 TOTAL CASH FLOWS GET COMPUTED CORRECTLY AND THEY ADJUST CORRECTLY TO THE INPUTS SHOWN. Calculate the NPV and IRR of this project. Explain whether you would replace the old machine with the new machin INPUTS Old Machine: Original Price Current Value Expected value in 4 years Annual Cash Expenses 103.500 49.500 11.250 22.500 New Machine: Original Price Installation Expenses Expected value in 3 years Annual Cash Expenses Increased Sales 135.000 7.875 56.250 19.125 33.750 CASH FLOWS CASH FLOWS AT t=0 CASH FLOWS FOR YEARS 1 to 4 Year 1 Project NPV Projects IRR Would you invest in the new machine? Why or why not? e to replace a manually operated one. The machine -year MACRS asset from its purchase price tead of being replaced now, it is expected to have a ne are $22,500. cted salvage value of $56,250 at the end of its 3-year nt in raw materials would increase by a total of he old one is salvaged. The company expects that ult in additional productive capacity so sales will year MACRS-class asset. MACRS Percentages facilities to determine if other changes would be ermined that existing facilities could support this new d have to take on new debt of $30,000 at 10% eturn for this project is 9% and the company's cash flows for this project. The only inputs that need d straight into your formulas but all calculations You do not need to do any lookup functions, ow all computations. Label all entries appropriately. THIS UP ANY WAY THAT YOU WANT AS LONG AS O THE INPUTS SHOWN. Year 1 2 3 4 5 6 7 8 9 10 11 3-year 33,33% 44,45% 14,81% 7,41% 5-year 20,00% 32,00% 19,20% 11,52% 11,52% 5,76% 7-year 10-year 14,29% 10,00% 24,49% 18,00% 17,49% 14,40% 12,49% 11,5 ...
Purchase answer to see full attachment

Tutor Answer

siyuwang
School: Boston College

Hello . Here is the progress. Would you please add time?

This is your personal Mid-Term Exam File. You are to only use the file that was sent t
else's file as this will be considered a ACADEMIC DISHONESTY AND WILL RESULT IN A
COURSE.

Caution: This is an EXAMINATION that you are to complete outside of class. Do not be mistaken
homework assignment. Because this is an examination you are expected to complete this on yo
not to work on or discuss the examination with anyone else other than with your instructor. You
assistance with any part of this exam from anybody other then your instructor. Any assistance o
will be considered ACADEMIC DISHONESTY AND WILL RESULT IN A FAILING (F) GRADE FOR THIS C
from the tutorials, homework assignments, practice problems, and your textbook to help you de
questions found in this examination. DO NOT COPY AND PASTE COMPLETE MODELS OR ANY POR
TUTORIAL, HOMEWORK ASSIGNMENTS, OR PRACTICE PROBLEMS. YOU MUST CREATE YOUR MO
INFORMATION GIVEN IN THE EXAM.

IMPORTANT: SAVE THIS SPREADSHEET TO THE DESKTOP OF THE COMPUTER YOU ARE USING WIT
RESAVE IT OFTEN WHILE YOU ARE WORKING ON IT.

SAVE THIS FILE BACK TO YOUR COMPUTER WITH YOUR NAME IN THE FILENAME AND RESAVE IT
COMPLETING THE EXAM. IT IS YOUR RESPONSIBILiTY TO PROTECT YOUR WORK AGAINST TECHN
BE EXTENDED DUE TO TECHNICAL FAILURES INCLUDING LOSS OF FILE OR CORRUPTION. IT IS ADV
MULTIPLE LOCATIONS.

When you have completed this exam spreadsheet: Save it one last time to your computer. Subm
MARCH 15th by 11:59 pm.

There are 100 points available from the take home portion of this exam. Points are shown on ea
where possible.
We will have the in class portion of the Mid-Term exam on March 12th at 9:30 in our classroom.
be worth 25 points. There will not be any make ups given!!
This Exam is worth a total of 125 points.
Please let me know if you have any questions.

This test belongs to Ibrahim Almusalit

the file that was sent to you. Do not use somebody
AND WILL RESULT IN A FAILING (F) GRADE FOR THIS

class. Do not be mistaken thinking that this is a simple
ted to complete this on your own. This means that you are
n with your instructor. You are not to receive help and
structor. Any assistance other then from an approved source
LING (F) GRADE FOR THIS COURSE. You may use material
ur textbook to help you develop your models and answer the
LETE MODELS OR ANY PORTION OF THE MODELS FROM THE
U MUST CREATE YOUR MODELS FROM SCRATCH USING

PUTER YOU ARE USING WITH YOUR NAME IN THE FILENAME.

ILENAME AND RESAVE IT OFTEN WHILE YOU ARE
UR WORK AGAINST TECHNICAL DIFFICULTIES. TIME WILL NOT
OR CORRUPTION. IT IS ADVISABLE TO SAVE YOUR WORK IN

me to your computer. Submit it via the Canvas dropbox by

m. Points are shown on each tab. Partial credit will be given

h at 9:30 in our classroom. This portion of the Mid-Term will

Problem 1:

Format the area below to look as much like the picture to the right as
possible. Do not include the arrows.

Elvis Products International
Income Statement
For the Year Ended Dec. 31, 2018
Sales
Cost of Goods Sold
Gross Profit
Selling and G&A Expenses
Fixed Expenses
Depreciation Expense
EBIT
Interest Expense
Earnings Before Taxes
Taxes
Net Income

$
$

$
$
$

2018
6,950,000
6,275,000
675,000
345,000
120,000
21,250
188,750
85,000
103,750
41,500
62,250

$
$

$
$
$

2017
6,725,000
6,150,000
575,000
225,000
120,000
17,900
212,100
67,500
144,600
57,840
86,760

he right as
Col
C

Column
B

Col
D

Elvis Products International

Rows 11-13 are
36 pixels high.
Text is centered
horizontally and
vertically

Income Statement
For the Year Ended Dec. 31, 2018
2018
Sales
Cost of Goods Sold

2017

$

6,950,000
6,275,000

$

6,725,0
6,150,0

Gross Profit
Selling and G&A Expenses
Fixed Expenses
Depreciation Expense

$

675,000
345,000
120,000
21,250

$

575,0
225,0
120,0
17,9

EBIT
Interest Expense

$

188,750
85,000

$

212,1
67,5

Earnings Before Taxes
Taxes

$

103,750
41,500

$

144,6
57,8

Net Income

$

62,250

$

86,7

Column B is
35 characters
wide

Columns C and
D are 14
characters wide

Col
C

Col
D

nternational

tement

d Dec. 31, 2018
2018

2017

$

6,950,000
6,275,000

$

6,725,000
6,150,000

$

675,000
345,000
120,000
21,250

$

575,000
225,000
120,000
17,900

$

188,750
85,000

$

212,100
67,500

$

103,750
41,500

$

144,600
57,840

$

62,250

$

86,760

Columns C and
D are 14
characters wide

Ibrahim Almusalit

Problem 2:

You are given the data for a company for 12 months. The company uses short-term loans and marketabl
the desired level at the end of each month. Short-term loans will be paid down or paid off with any exce
only when the loan balance is zero. Marketable securities will be sold first to supply any needed cash inf
Note that the
marketable securities and no short-term loans at the beginning of January.

inputs are in
dollars
but the
The company expects to make an outlay for new capital equipment in
May, June,
July, August, Septemb
Cell L24 for the user to select the month of the outlay. Title the list as "Capital
Outlay" and create an inst
statements
Capital Outlay from the list below." Use this and other input information
to complete
below
are in the Cash Budget.
short-term loans at the end of each month in Row 52 and the balancethousands
of marketable
of securities at the en

positive balances when marketable securities are being used, and it should show zero when marketable

Format the range F45:N45 so that all values less than zero display as red numbers instead of black numb

Name the input cells for collections Collect0, Collect1, and Collect 2. Then create and save three scenarios using th
cash flow for April - December while changing the collection rates. The BEST scenario will Collect0 = 30%, Collect1
base case collection rates given here, and the WORST scenario will have Collect0 = 0%, Collect1 = 50%, and Collect
titled "Scenario Summary.

INPUTS
Collections on sales in the month of the sale
Collections on sales in the month following the sale
Collections on sales in the second month following the sale

20%
55%
25%

Cash Operating Expenses as a Percentage of Current Month Sales

40%

Jan
Expected Sales
Collections on Sales

Feb

Mar

Apr

May

$ 7,875 $ 9,188 $ 9,975 $ 11,025 $ 7,350
$ 9,896 $ 8,610 $ 9,450

Cash Operating Expenses
Fixed Expenses
Lease Payments
Capital Outlay
Tax Payments

$ 4,410 $ 2,940
$
550 $
550
$
675 $
$ 80,000 $
$
130 $
-

Total Cash Outflows

$ 85,765 $ 3,490

CASH BUDGET
Mar
Beginning Cash Balance

9,896

Apr
8,610

May
9,450

Net Cash Flow

(77,155)

Unadjusted Ending Cash Balance
Adjustment Needed
Ending Cash Balance

8,610
(130)
10,000

(77,285)

5,960
9,450
(130)
5,830

Short-Term Loans and Marketable Securi
Mar
Short-Term Loans Outstanding
Marketable Securities

5,000
14,896

Apr
92,285
100,765

May
9,170
18,490

rm loans and marketable securities investments to bring the cash balance to
or paid off with any excess balances and marketable securities will be used
pply any needed cash infusions before loans are used. The company had no

, July, August, September, October or November. Create a drop-down list in
utlay" and create an instruction box saying "Select the desired month of the
mplete the Cash Budget. Then complete the section that gives the balance of
able securities at the end of each month in Row 53. The formulas should show
w zero when marketable securities are not being used.

s instead of black numbers for any values of the inputs.

ve three scenarios using the Scenario Manager that show the company's monthly net
ll Collect0 = 30%, Collect1 = 60%, and Collect2 = 10%, the BASE scenario will use the
Collect1 = 50%, and Collect2 = 50% . Save a summary of the scenarios on the next tab

NPUTS
Capital Outlay for New Equipment

$ 80,000

Month of the Capital Outlay

May

Desired End-Of-Month cash balance

$ 15,000

Jun

Jul

Aug

Sep

Oct

Nov

Dec

$ 9,450 $ 11,130 $ 12,075 $ 9,450 $ 7,875 $ 6,825 $ 5,775
$ 11,030 $ 11,230 $ 9,581 $ 7,928 $ 6,773 $ 4,541 $ 1,155
$ 3,780 $ 4,452 $ 4,830 $ 3,780 $ 3,150 $ 2,730 $ 2,310
$
550 $
550 $
550 $
550 $
550 $
550 $
550
$
$
675 $
$
$
675 $
$
$
$
$
$
$
$
$
$
$
130 $
$
$
130 $
$
$ 4,330 $ 5,807 $ 5,380 $ 4,330 $ 4,505 $ 3,280 $ 2,860

BUDGET
Jun
11,030

Jul
11,230

Aug
9,581

Sep

Oct

7,928

6,773

Nov
4,541

Dec
1,155

6,700

5,423

4,201

3,598

2,268

1,261

(1,705)

11,030
(130)

11,230
(130)

9,581
(130)

7,928
(130)

6,773
(130)

4,541
(130)

1,155
(130)

6,570

5,293

4,071

3,468

2,138

1,131

(1,835)

Aug

Sep

Oct

Nov

Dec

10,929
20,380

11,533
19,330

12,863
19,505

13,869
18,280

16,835
17,860

nd Marketable Securities
Jun
8,430
19,330

Jul
9,707
20,807

Ibrahim A

Ibrahim Almusalit

Scenario Summary
Current Values:

BASE

BEST

Changing Cells:
$F$22
0%
20%
30%
$F$23
50%
55%
60%
$F$24
50%
25%
10%
Result Cells:
$B$57
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.

WORST

0%
50%
50%

This is a continuation from Problem 2. Using the Cash Budget in problem 2. Name the input cells for collection
scenarios using the Scenario Manager that show the company's monthly net cash flow for April
Collect0 = 30%, Collect1 = 60%, and Collect2 = 10%, the BASE scenario will use the base case collection rates giv
Collect1 = 50%, and Collect2 = 50% . Save a summary of the scenarios on a separate tabbed page.

Scenario Summary
Current Values:

BASE

BEST

WORST

Created by
Created by
Created by
Windows User Windows User Windows User
on 3/15/2019 on 3/15/2019 on 3/15/2019
Modified by
Windows User
on 3/15/2019

Changing Cells:
$F$22
0%
20%
30%
0%
$F$23
50%
55%
60%
50%
$F$24
50%
25%
10%
50%
Result Cells:
$B$57
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
$F$22:$F$24
(All)
by
Row Labels$C$57
BASE
BEST
WORST

Name the input cells for collections Collect0, Collect1, and Collect 2. Then create and save three
ash flow for April - December while changing the collection rates. The BEST scenario will
the base case collection rates given here, and the WORST scenario will have Collect0 = 0%,
arate tabbed page.

Ibrahim Almusalit

Ibrahim Almusalit

Problem 3:

You need forecast the 2019 and 2020 pro forma income statement and balance sheet for the firm whose 2017 and 201
income statements and balance sheets are given here. Inputs are provided for most items in the Inputs section below

The cost of goods sold in 2019 and 2020 are expected to change with sales by 103% and 105% respectively
arithmetic average of the proportion of this item in relation to sales for 2017 and 2018. Selling and G&A Expenses,
Accounts receivable, Inventory, and Accounts Payable are expected to change with sales at 100% of the two
arithmetic average of their percentage of sales for 2017 and 2018. The firm has planned an investment of $225,000 in
equipment in 2019. This equipment will be depreciated at $45,000 per year. Depreciation on existing Plant/Equipmen
be the same as it was in 2017. Interest expense for 2019 is computed on the 2018 ending balances in Short Term Note
Payable and Long Term Debt. Inputs for those interest rates are provided in the Inputs section.

Complete the pro-forma income statement and balance sheet for 2019 and 2020 using the information above, the inp
below, and the values that are given in the statements. The 2019 and 2020 projected statements should accurately ad
for any changes in the inputs.

Compute the excess or deficit of financing for 2019 in the yellow box at the bottom of the Balance Sheet. This numbe
should be positive if the firm will have more financing than is needed, and it should be negative if the firm has less
financing than is needed. If you have excess financing for 2019 reduce your reduce the 2019 Long Term debt financing
Calculate your 2020 interest expense using the 2019 ending balances in Short Term Notes Payable and Long Term Deb
Inputs for those interest rates are provided in the input section

Compute the excess or deficit of financing for 2020 in the yellow box at the bottom of the Balance Sheet. This numbe
should be positive if the firm will have more financing than is needed, and it should be negative if the firm has less
financing than is needed.

INPUTS
2019 Percent Change in Sales from 2018
2020 Percent Change in Sales from 2019
Interes...

flag Report DMCA
Review

Anonymous
Thanks, good work

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors