need excel formulas in boxes in red

Insert Dow P age Layout Formulas Data_Review View Developer Help Power Pivot Pe Copy Format Painter Me Cipboard Alignment B5B. SEQUITY WARNING Macros have been disabled. Table Content A Capital B DON Given Information Hurdle rate Taxtate: 8.5% 240% New product line information Year 1 revenues Annual Revenue Growth rate Initial COGS COGS at end of analysis 8,600 000 520% 45.0% Revenue on new Product Line COGS (use schedule below) Sales, General & Admin Costs Depreciation (straight line) EBIT Taxes Net Income Add Back Depreciation Operating Cash Flow Initial SGA Annual Reduction in SG&A 2,850 000 1 25% Start-up costs 1.500.000 Net Required Investment New Equipment Required: Useful Life machine 1 Cost 7,345.000 4,680 000 Capital Spending Stutup Costs Abor Tax proceeds on Disposals Disposal of Existing machinery Net Incremental Cash Flow PV of incremental Cash Flows Book Vale 1,250,000 530,000 machine a Proceeds 1150 000 425,000 After Tax Gairloss (see handout) Curative Cash Flow Cumulative Discounted Cash Flow Type here to se O E - e A 14 15 Initial COGS COGS% at end of analysis 520% 45 0% EBIT Taxes Net Income Add Back Depreciation Operating Cash Flow 17 18 Initial SGA Annual Reduction in SG&A 2,850,000 125% 20 Start-up costs: 1,500,000 Net Required Investment New Equipment Required: machine 1 machine 2 Useful Life 10 10 Cost 7.855.000 ,680,000 Capital Spending Start-up Costs After-Tax proceeds on Dispo 4 Disposal of Existing machinery 888888XNXXON Net Incremental Cash Flow PV of incremental Cash Flows Book Value 1.250.000 530 000 machine a machine b Proceeds 1,450.000 425,000 Cumulative Cash Flow Cumulative Discounted Cash FF After-Tax Gain/Loss (see handout) pre tax l a x on ol machine a 200.000 (48000) machine b (105,000) 25 200 total after-tax proceeds 1.402 000 4 50 200 1.852200 Net Presnt Value IRR Payback Discounted Payback Prontability Index: COGS ratio by year Student name main E o Type here to search P Q R Calculations 01 3 4 5 Revenue on new Product Line COGS (use schedule below) Sales, General Admin Costs Depreciation (straigone) Tues Net income 100 Add Back Deprecation 25% Operating Cash Flow 8,800,000 19,030,000 9.48150019.955,575 10453 354 10.975,021 11.574,82312 101,064 12,706,117 13,341423 4.472.000 4625,36724782,890120446025.110,5295280 686 5,455083 5633.717 5,816 578 6003640 2 850 000 28143752779.1952744 455 27101502,676 273 2,642 819 2.609.784 2577.162 2544.947 1,253,500 1283500 120.4001253,500 1,253,500 1253 500 1,253,5001252400 1.253.5001253500 24,500 336,768 6 65.915 1.013.0171,379,176 1,765,563 2,178,420 2,604,062 3.068 877 3,639,136 588080822159,870 243, 124331002423.7235521621624.075734,130849.440 18,620 256,936 506,095 769,893 1,048,173 1,341,8281,651,800 1,979,087 2,324.747 2,689,895 1,253,500 1.253.500 1.251,600 1,253,5001253.500 1.253.500 1.253.500 1253.500 1.253.500 1.253.500 1,272,120 1,509,436 1,759,695 2,023,393 2,301,673 2,695,328 2.906 300 3.232.687 3,678,247 3.943,395 Use positive numbers for entries in the calculations' section both rows and outlows In the totals/sublocals, add inflows and su 000 NetBe aves meat Enter outflows aseg um Capital Opeg Startup Cords Adler Tax proceeds on Disposals. 1.500.000 1,852,200 000 Net Incremental Cash Flow PV of incremental Cash Flows 1,272,120 1,609,436 1,769,695 1,172,451 1282 1991.377,001 2,023,393 1.400.029 2,301,673 2,695,328 15 1.7171.100,703 2.905,300 1041,280 3.232.587 1680 109 3,678,247 11/17 128 3.943,395 1.744.100 000 5,000 C Ca ave Can tive Discounted Cash Flow Net Present Value round ton s 1 000 450 200 Payback dd Back Depreciation Operating Cash Flow POR 11253 500 1253 500 1253, 500 1253 500 1253 500 1.253,500 1.253 500 1,253,500 1.253,500 1253,500 1.272.120 1,509,436 1,759,595 2,023,393 2,301,673 2.595,328 2,905,300 3,232,587 3,578,247 3,943,395 Use positive numbers for all entries in the Calculations section - both flows and outflows in the total subtotals, add inflows and subtract outflows let Required Investment Enter Outflow nepie number w as positive numbers in the Net Required Investment section Capital Spending Start-up Costs After Tax proceeds on Disposals 1.500.000) 1852 200 Net Incremental Cash Flow PV of incremental Cash Flows 1,272,120 1,509,436 1.759,595 2,023.393 1.172 461 1282 199 1377.601 1460029 2,301,673 1.530 717 2.595,328 1,590,793 2.905,300 1641280 3.232.587 1.683 109 3.578,247 1717,128 3.943.395 1744 106 Cumulative Cash Flow Cumulative Discounted Cash Flow 244000 cround to the nearest $1.000 Net Presnt Value VRR Payback Discounted Payback Profitability Index: COGS ratio by year 520051225044% 4967548.89% 48115473346584457845.00% BPM Developer Help Power Pivot Tell me what you want to do RELSE enue on new Product Line as (use schedule below) s. General & Admin Costs reciation (straight line) 8.800.000 19.030,000 9.481.500 | 9.965575 10.453,354 10.978021 11,524,823 12101,064 12,706 117 13.341,423 4412.000 40253674782.800 I SO S10629 5.280685455,085633717 15,816576 103, 640 2.800.000 12.814 375 2.770.1952.244. 050 2.670232042,8192 609,784257716212544,947 1.253.500 253,500 1253 500 253,50012 300 1283500 | 253 500 253 500 1253,500 1253,500 24,500 336.758666,9161.1.013.017 1.379.176. 1.765,5632.173.420 2.604,062 3.058,8773,539,335 5,88080822 159,820 M243124331002423735521621624975 734,130, 849 440 18,620 265,936 506.095 789,893 0.048.1731,341,8281.661.800 1.979.0872.324.7472.689.896 1 253,500 1253 500 1 253 500 253500 1253.500 2.500 1253 500 1253 500 1.253 600 1253.500 1,272.120 1.509.436 1.769.695 12.023.393 2.301.62 2.596.328 2.905 300 3.232.6873,678 247 3.943.395 Use positie numbers for entries in the Calculations Section In the totals/subtotal, add inflows and Subtract outfows Income Back Depreciation erating Cash Flow Regutegive pital Spending art-up Costs er proceeds on Disposals Enter Outlow as 12.535.000) (1 500,000) 1892 200 et Incremental Cash Flow of incremental Cash Flows (12.182.800 1.272.120 1.609,436 1.769.596 2,023,393 12/182 800), 10724611282 1991 1377 601 1460029 2,301.673 1830 Z 2.695.328 2,905,300 3,232,8873,878.247 243,396 7500 793105412801683, 109 0717128 0744108 Cumulative Cash Flow Cumulative Descounted Cash Flow 24140 et Presnt Value ayback iscounted Payback "rofitability Index: 4678245 00% 2800 444 .4894819 COGS ratio by year