# 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

waaay_flyy
 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. Show or explain how you obtained each answer. Please Provide Excel Sheet with formulas a Determine the unit price that would achieve a cash flow in year 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%
• 7 years ago
• 8