(g) Assuming that year 2 sales growth rate is 30%, what level of unit sales in year 1 will be just sufficient to accept this project? (5 points)

Note: I just need the formula on excel or the way to find it- so, you don't need to type the whole thing as your own to find the number!!!

AutoSave . OFF MBAAF 623 Homework 2 Q Q v Home Insert Draw Page Layout Formulas Data Review View Share Comments Arial 10 A" A" = Wrap Text v Currency Insert v Ex AY - O. Ex Delete v Paste BIU TV OVA E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Ideas Sensitivity Formatting as Table Styles Format X V F Filter Select D21 4 X V fx =D15*D16 B C D E F G H 1 J K L M N P Q R S GoPro's project (Problem 2) Year 0 Year Year 2 Year 3 Year 4 Year 5 3 Key Assumptions Sales Growth Rate 25.0% 55.0% 10.0% 45.0% 5 Change in Sales Growth Rate from previous period 30.0% 45.0% -55.0% 6 Inflation Rate 1.5% 1.5% 1.5% 1.5% 1.5% 7 Real Cost of Capital 15.0% 15.0% 5.0% 5.0% 15.0% 8 Tax Rate 40.0% 40.0% 0.0% 40.0% 9 10 Discounting 11 Discount Rate = Cost of Capital 61.0% 61.0% 61.0% 61.0% 31.0% 12 Cumulative Discount Factor 0.0% 31.0% 159.2% 317.3% 571.9% 081.8% 13 14 Price or Cost / Unit 15 Unit Sales 7,000,000 8,750,000 13,562,500 14,918,750 8,205,313 16 Sales Revenue / Unit $45.00 $45.68 $46.36 $47.06 $47.76 17 Variable Cost / Unit $27.00 $27.41 $27.82 $28.23 $28.66 18 Cash Fixed Costs $25,000,000 $25,375,000 $25,755,625 $26,141,959 $26,534,089 19 20 Cash Flow Forecasts 21 |Sales Revenue $ 315,000,000.00 $399,656,250 | $628,759,195 $702,009,642 $391,896,882 22 $ 189,000,000.00 $239,793,750 $377,255.517 $421,205,785 $235,138,129 23 Gross Margin $ 126,000,000.00 $159,862,500 $251,503,678 $280,803,857 $156,758,753 24 25 Cash Fixed Costs 325,000,000 $25,375,000 $25,755,625 $26,141,959 $26,534,089 26 Depreciation ($32,857,143) $32,857,143 ($32,857,143 $32,857,143 $32,857,143) 27 Total Fixed Costs ($7,857,143) $7,482,143) ($7, 101,518) ($6,715,183) ($6,323,054) 28 29 Operating Profit $ 133,857,142.86 $167,344,643 $258,605,196 $287,519,040 $163,081,807 30 $53,542,857 $66,937,857 $103,442,078 $115,007,616 $65,232,723 1 Net Profit $80,314,286 100,406,786 $155, 163, 118 $172,511,424 $97,849,084 32 33 Add Back Depreciation $32,857,143) ($32,857,143) ($32,857,143) ($32,857,143 ($32,857,143) 34 Operating Cash Flow (OCF) $47,457,143 $67,549,643 $122,305,975 $139,654,281 $64,991,941 35 36 Investment in Plant & Equip $220,000,000 $10,000,000 7 Cash Flows $220,000,000 $47,457,143 $67,549,643 $122,305,975 $139,654,281 $74,991,941 38 Present Value of Each Cash Flow $220,000,000 $29,476,486 $26,059,814 $29,306,911 $20,785,035 $6,932,426 39 Net Present Value $332,560,671 40 41 (h) Complete table to the right and indicate Excel formula in cell 42 D45 in this text box. You must use 43 Data Tables tool to complete this Sales Growth rate (year 2) 44 table. (5 points) $332,560,671 10% 15% 20% | 25% 45 =TABLE(D4,C15) 5,000,000 $271,395,560 $276,746,215 $282,470,827 $288,592,190 46 Unit Sales .000,000 $308,485,389 $315,976,305 $323,990,762 $332.560,671 47 (year 1) 9,000,000 $345,575,218 $355,206,396 $365,510,698 $376,529,152 1,000,000 $382,665,047 $394,436,487 $407,030,633 $420,497,633 49 Problem 1.1 Problem 1.2 1.3 (part d) Problem 2.1 Problem 2.2 Problem 3 Problem 4.1 Problem 4.2 + Calculate + 98%