1. Use this template for this question. (Reference how to complete assignments form a Google sheet template here if needed.) See the bottom of the assignment for spreadsheet tips.

You are considering two mutually exclusive investments. The projects expected net cash flows are as follows:

Expected Net Cash Flows

Year	Project X	Project Z
0	$(45,000)	$(50,000)
1	(20,000)	15,000
2	11,000	15,000
3	20,000	15,000
4	30,000	15,000
5	45,000	15,000

1. Construct NPV profiles for Projects X and Z (table AND line graph).

2. Calculate each projects IRR.

3. If the required rate of return for each project is 9 percent, which project should you select? If the required rate of return is 12 percent, what would be the proper choice? If the required rate of return is 15 percent, what would be the proper choice?

4. At what rate do the NPV profiles of the two projects cross?

2. Please see the income statement below - calculate:

   1. Degree of operating leverage

   2. Degree of financial leverage

   3. Degree of total leverage

Sales	$150,000
Variable Operating Costs	(105,000)
Gross Profit	45,000
Fixed Operating Costs	(20,000)
Net Operating Income	25,000
Interest	(15,000)
Earnings before Taxes	10,000
Taxes (40%)	(4,000)
Net Income	$6,000

3. Given the information below, which of the following firms would be considered riskiest? Explain your answer.

Company	DOL	DFL
Alps	1.5x	6.0x
Apex	3.0x	4.0x
Acme	5.0x	2.0x

4. Assume that Wonka Industries has a $500,000 capital budget planned for the coming year and its net income is forecasted at $850,000. Wonka Industries has 300,000 shares outstanding. Determine the DPS the Wonka Industries will pay for the coming year if they apply the following policies:

   1. Residual dividend policy

   2. Stable, predictable dividend policy and they paid a dividend of $1 per share last year

   3. Constant payout ratio of 55%

   4. Low regular dividend of $0.75 plus 40% of earnings greater than $750,000

Spreadsheet tips for question 1:

For part A

• Use the NPV function to complete the table.

= NPV(rate, range of values)

You should reference the rate and the range of values so that you can easily drag this formula to all the cells in the NPV profile. For the rate, you will want to use a dollar sign in front of the letter to lock the reference to that column. For the range of values, you will want to use a dollar sign in front of all the numbers to lock the references to the correct rows.

• Make sure you create a line graph to illustrate the profiles. Project X and Z will be their own series and the rate will be the x-axis.

For part B - use the IRR function.

=IRR(range of cash flows)

For part D - to find the crossover rate, you need to find the IRR of the difference in cash flows of the two project for each year.

You can use a simple subtraction equation to find the difference in cash flows and drag this formula down to each year (=B2-C2). Then use the IRR formula. (See information on this in a footnote on page 152.)

> First Project Assignment 5 202 Part 1 & 2 Assignmen X + Copy of 202 Part 1 & 2 AS X PDF - Google Drive C Get Homework Help With x | + docs.google.com/spreadsheets/d/1J17oqjyXbo4zALGLKVRUkHOp7y|ZR72B5Vn9n57A3hM/edit#gid=149153185 Copy of 202 Part 1 & 2 Assignment Template File Edit View Insert Format Data Tools Extensions Help ^ Ha Share J C5 Last edit was made on March 20 by Cornelia Alsheimer-Barthel Y 50% View only 31 A1:E1 fx Acme Corporation E G K L M N . 2 Acme Corporation Balance Sheet December 31, 2019 Acme Corportation Income Statement For the Year Ended December 31, 2109 3 (1-1) Calculation (1-2) Compare (1-3) Explain Sales COGS Gross Profit $4,290,000 3,580,000 710,000 o Assets Current Assets Cash A/R Inventory Total current assets $72,000 439,000 894.000 1,405,000 Liabilities Current Liabilities Accts & Notes Pay. Accruals Total Current Liabilities Long-term debt Total Liabilities $432,000 170,000 602 0001 404,290 1,006,290 Financial Ratios (1-1) Industry Ratios Acme Corp's Ratios Quick ratio Current ratio 2.7 Inventory turnover 5.8 Days sales outstanding 32 days Fixed assets turnover 13 Total assets turnover 26 Return on assets 9.10% Return on equity 18.20% Debt ratio 50% Profit margin on sales 3.50% 10 11 Operating Expenses Selling Expenses Administrative Expenses Depreciation Misc. Expenses Operating Profit (EBIT) 12 121,320 115,000 159,000 134.000 180.680 Long-term Assets Land and bldg Machinery Other Fixed Assets Total Long-term Assets 1a + 238,000 132.000 61,000 431,000 Owner's Equity Retained earnings Common stock Total Owner's Equity 15 16 17 254,710 575,000 829,7101 Taxes 72,272 Industry average ratios have been constant for the past four years. Based on year-end balance sheet figures (1-4) How is Acme doing compared to the rest of the industry? What are their strengths/what can they do to improve? 19 19 Total Assets $1,836,000 Total Liabilities & Owner's Equity $1,836,000 Net income $108,408 Add 1000 more rows at bottom. > Financial Statements & Ratios Time Value of Money > First Project Assignment 5 202 Part 1 & 2 Assignmen X + Copy of 202 Part 1 & 2 AS X PDF - Google Drive C Get Homework Help With x | + docs.google.com/spreadsheets/d/1J17oqjyXbo4zALGLKVRUkHOp7y|ZR72B5Vn9n57A3hM/edit#gid=149153185 Copy of 202 Part 1 & 2 Assignment Template File Edit View Insert Format Data Tools Extensions Help ^ Ha Share J C5 Last edit was made on March 20 by Cornelia Alsheimer-Barthel Y 50% View only 31 A1:E1 fx Acme Corporation E G K L M N . 2 Acme Corporation Balance Sheet December 31, 2019 Acme Corportation Income Statement For the Year Ended December 31, 2109 3 (1-1) Calculation (1-2) Compare (1-3) Explain Sales COGS Gross Profit $4,290,000 3,580,000 710,000 o Assets Current Assets Cash A/R Inventory Total current assets $72,000 439,000 894.000 1,405,000 Liabilities Current Liabilities Accts & Notes Pay. Accruals Total Current Liabilities Long-term debt Total Liabilities $432,000 170,000 602 0001 404,290 1,006,290 Financial Ratios (1-1) Industry Ratios Acme Corp's Ratios Quick ratio Current ratio 2.7 Inventory turnover 5.8 Days sales outstanding 32 days Fixed assets turnover 13 Total assets turnover 26 Return on assets 9.10% Return on equity 18.20% Debt ratio 50% Profit margin on sales 3.50% 10 11 Operating Expenses Selling Expenses Administrative Expenses Depreciation Misc. Expenses Operating Profit (EBIT) 12 121,320 115,000 159,000 134.000 180.680 Long-term Assets Land and bldg Machinery Other Fixed Assets Total Long-term Assets 1a + 238,000 132.000 61,000 431,000 Owner's Equity Retained earnings Common stock Total Owner's Equity 15 16 17 254,710 575,000 829,7101 Taxes 72,272 Industry average ratios have been constant for the past four years. Based on year-end balance sheet figures (1-4) How is Acme doing compared to the rest of the industry? What are their strengths/what can they do to improve? 19 19 Total Assets $1,836,000 Total Liabilities & Owner's Equity $1,836,000 Net income $108,408 Add 1000 more rows at bottom. > Financial Statements & Ratios Time Value of Money