| Below is an Income and cash flow statements that management has approved. (If there are errors or oversights, that is their problem, not yours). Start each question from the original data. Cells F14:F22 contain the original values in case you need to get back to them. |
|
|
|
| a |
Determine the unit price that would achieve a cash flow in week 6 of $500,000 |
|
|
|
| b |
Determine the sensitivity of the internal rate of return to a 10%, 25% and 50% increase in investment? (start with original values) |
|
|
|
| c |
Determine the expected present worth for the following data where Forecast 1 is the current values. (start with original values) |
|
|
|
| |
|
Forecast 1 (current Values) |
Forecast 2 |
Forecast 3 |
cell |
|
|
|
|
| |
Unit Price |
$35.99 |
$40.00 |
$35.00 |
C14 |
|
|
|
|
| |
COGS each |
$12.50 |
$15.00 |
$11.50 |
C15 |
|
|
|
|
| |
S.G. & A. |
$800,000 |
$1,000,000 |
$900,000 |
C16 |
|
|
|
|
| |
Sales Quantity Forecast year 1 |
50,000 |
40,000 |
60,000 |
D23 |
|
|
|
|
| |
Probability |
50% |
30% |
20% |
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
Original Values |
|
|
|
|
| |
Unit Price |
$35.99 |
|
|
$35.99 |
|
|
|
|
| |
COGS each |
$12.50 |
|
|
$12.50 |
|
|
|
|
| |
S.G.& A. |
$800,000 |
|
|
$800,000 |
|
|
|
|
| |
salvage |
$100,000 |
in year 6 |
|
$100,000 |
|
|
|
|
| |
Income tax rate |
35% |
|
|
35% |
|
|
|
|
| |
Capital Gains Tax rate |
15% |
|
|
15% |
|
|
|
|
| |
Working capital |
no change |
|
|
no change |
|
|
|
|
| |
MARR |
15% |
|
|
15% |
|
|
|
|
| |
Investment |
$2,000,000 |
|
|
$2,000,000 |
|
|
|
|
| |
Sales Quantity Forecast |
|
50,000 |
60,000 |
72,000 |
86,400 |
103,680 |
124,416 |
|
| |
Depreciation MACRS |
5 |
20.00% |
32.00% |
19.20% |
11.52% |
11.52% |
5.76% |
|
| |
|
|
|
|
|
|
|
|
|
| |
Income Statement |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
|
| |
Sales revenue |
|
$1,799,500 |
$2,159,400 |
$2,591,280 |
$3,109,536 |
$3,731,443 |
$4,477,732 |
|
| |
Cost of goods sold |
|
($625,000) |
($750,000) |
($900,000) |
($1,080,000) |
($1,296,000) |
($1,555,200) |
|
| |
Gross Margin |
|
$1,174,500 |
$1,409,400 |
$1,691,280 |
$2,029,536 |
$2,435,443 |
$2,922,532 |
|
| |
General, Sales and Admin. |
|
($800,000) |
($800,000) |
($800,000) |
($800,000) |
($800,000) |
($800,000) |
|
| |
Depreciation |
|
($400,000) |
($640,000) |
($384,000) |
($230,400) |
($115,200) |
($57,600) |
|
| |
EBIT |
|
($25,500) |
($30,600) |
$507,280 |
$999,136 |
$1,520,243 |
$2,064,932 |
|
| |
Income tax |
|
$8,925 |
$10,710 |
($177,548) |
($349,698) |
($532,085) |
($722,726) |
|
| |
Net income |
|
($16,575) |
($19,890) |
$329,732 |
$649,438 |
$988,158 |
$1,342,206 |
|
| |
|
|
|
|
|
|
|
|
|
| |
Cash Flow Statement |
|
|
|
|
|
|
|
|
| |
Net Income |
|
($16,575) |
($19,890) |
$329,732 |
$649,438 |
$988,158 |
$1,342,206 |
|
| |
Add depreciation |
|
$400,000 |
$640,000 |
$384,000 |
$230,400 |
$115,200 |
$57,600 |
|
| |
Investment |
(2,000,000) |
|
|
|
|
|
|
|
| |
Change in Working Capital |
|
($179,950) |
($35,990) |
($43,188) |
($51,826) |
($62,191) |
($74,629) |
|
| |
Salvage |
|
|
|
|
|
|
$100,000 |
|
| |
Tax on gain |
|
|
|
|
|
|
$15,000 |
|
| |
Cash flow |
($2,000,000) |
$203,475 |
$584,120 |
$670,544 |
$828,013 |
$1,041,167 |
$1,440,177 |
|
| |
|
|
|
|
|
|
|
|
|
| |
|
Present Worth = |
IRR |
|
|
|
|
|
|
| |
|
$673,198 |
23.95% |
|
0 comments