Question

AutoSave $ u- Capital Budgeting Assignment FNCE 301 119 template-Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Developer Share Comments 2! Insert Times New Roman 10 A A = = = D 2Wraa Text General E AY-O. 4 Delete 2 Conditional Format Call Sort & Sensitivity Pesce Find & a Ideas B B IU E E B Merge & Center A $ - % Formatting as Table Styles I! Farmat Xu Filter Select - Ford Alignment Number Editing Sensitivity A5 x f x Toefield Inc. has developed a powerful efficient snow remover that is significantly less polluting than existing snow removers currently on the market. The company spent $2,000,000 developing this product and the LL Cloboud Capital Budgeting Assigament Template Turn Inchas dould p rillicit ownever that is significantly Imex pellilling than scisting An In currently to the market. The company spent $2.00100 de luping this product and the marketing department spent mother $300,000 to sess the market ded. It would cost $30 million at Yer to buy de equipa. c r y to manufacture the emot uw blower. The project would require Het working capital at the beginning of each year qual a 20 osales (NCWC 20%/Naloll NOWCU 20% de L The didicit me llame would lllur 9,000 per unit, and Tuled ladies that variable wuld amunt $190 per unit. The company tapes that The prin yariable casts aud into the nation rate of 2% after var 1. The rempany's adalaran wunld hp 90.000 in Year 1 and are capered to increase with an intim. The eldet som blomer project would be fe of 4 years. If the project is undertaken, at must be conducd for the citire 4 years. Also, the project's retumis are expected to be highly correlated with returns on the firm's adierasvetu The firm belies it will usils per your The quipment would be deprecated using CCA rate of 30%. The estimanal market value of the equipment at the end of the project's 4-year Life is S500,000. Toefield has utber assets in this c ase. Told Loc.'s been plus previocini tramite is 90%. Its cost of capital is 9% for iskrojects, Low-risk in an evaluated with WACC 6%, and high-risk projects at 12%. 13 17 a. Develop a sprctdsheet model and use it to find the projcct's NPV. IRR ud payback Key Output VPY - 21 Part 1. Input Data do thousands of dollars except Bor unit MOLD) 31.46% 16.33% MIRR Market value of equipment at Yair 5.00 29 Eyment and 24 Net Operating Wisata 25 Yerly sales dio units) 26 Salos pirlo per unit 27 Variable si per unit 28 Non-variable cuests WACXC Tn Nation CCA TA 31 Part 2. CCA Schedule 32 Years B CC CCA 33 year 1 34 year 2 30 year 3 11.900 36 uur End UCC $17,000 11.900 3,570 2.299 41 Part 3. Pri Cash Flow Time line of annual si Mawa) Ourus al Tine Zena 44 P M 45 Epment $ 24,000 Spreadsheet Prob Template # 2 -— — + 100%
can someone please help me! I am so lost

Answer 1

The solution is attached as JPEG

The project is viable in all the options except the scenario wheein all the variables will fluctuate by -25%. As there is no probability given, we presume that there is very less probaility of this event. Hence the project can be accepted.

1405 =1+E403/F403) Calculation of Depreciation Machinery Year 2 Investment 20000000 14000000 9800000 6860000 Depreciation Rate 30% 30% 30% Depreciation 6000000 1200000 2940000 2058000 Value at Year End 14000000 9800000 6860000 4802000 CASH FLOW CALCULATION OF THE PROJECT CABE SEGUREN Year Qty. to be sold (A) 5000 5000 5000 5000 3,000 3,060 Sales Price (B) 3.121 3,184 Variable Cost (C) 790 806 838 Sheet1692

A48 Contribution D=(B-C) 2,210 2,254 2,299 2,345 Total value (AD) 11,050,000 11,271,000 11,496,420 11,726,348 Other Fixed Cost 800000 816000 832320 848966.4 Depreciation 6000000 4200000 2940000 2058000 Total Fixed Cost 6800000 5016000 3772320 2906966.4 Total 4250000 6255000 7724100 8819382 Loss on Assets 4302000 Net Income before Tax 4250000 6255000 7724100 4517382 Tax @30% 1275000 1876500 2317230 1355214.6 Post Tax 2975000 4378500 5406370 3162167.4 Add Depreciation & Non cash Cost 6000000 46 4200000 2940000 6360000 Cash Flow from Income Side 8975000 8578500 8346870 9522167.4

Format Palster Merge & Center - 8 Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format Sort Find FileSet Clipboard Alignment A70 NWC recovery 3,183,624.0 Net Cash Flow 8975000 8578500 8346870 12705791.4 NOWC 3,000,000.0 3,060,000.0 3,121,200.0 3,183,624.0 Additional WC Required 3,000,000.0 60,00.0 61,200.0 62.424.0 Cumulative Cash Flow 8975000 17553500 25900370 38,606,161.4 Preceding Year Balance recovered Cash Flow in 3rd Year A 2nd Year 5446500 8346870 Payback period D=A+B/C 2.65 Years Calculation of IRR Calculation of NPV Calculation of NPV (20,000,000) Cash Outflow Machiney 20000000 Machinery Working 3,000,000.0 Capital 23000000 working Capital (3,000,000) (23,000,000) 0 16

a x cut Copy Calibri General Wap Text 3 Merge Center Conditional Format Formatting - as Table Cell Styles Clear sasta Pinda - Sale Paste Format Painter Clipboard A82 Font Alignment 2nd Year 5446500 84680 Payback Period D-A+B/C 2.65 Years Calculation of IRR Calculation of NPV Calculation of NPV Cash Outflow Machiney (20,000,000) 20000000 Machinery Working 2,000,000.0 Capital 23000000 working Capital (3,000,000) (23,000,000) 0 Cash In Flow Income Working Capital Total 8975000 (60,000.0) 8578500 (61,200.0) 8346870 (62.424.0) 9522167,4 3,188,624.0 Discounting Factor @9% WACC 8915000 0.9174 8517300 0.8417 8284446 0.7722 12705791.4 0.7084 8,178,899.08 7,168,841.01 6,397,112.34 9,001,102.95 8,915,000 8,517,300 8,284,446 12,705,791 30,745,955,39 7,745,955.39 IRR 22.73% o f the Proiect is

TERF Merge & Center Conditional Format Formatting Table Cell Styles - Format Printer Chipboard Insert Delete Format Sort Filter - Sed Font Alignment A109 - CASH FLOW CALCULATION OF THE PROJECT-10% increase in Unit sales, Sales Price & Variable Cost Calculation of Depreciation Machinery Year 2 Investment 20000000 14000000 SRXOO GB60000 Depreciation Rate 30% 30% 30% 30% Depreciation 6000000 4200000 2940000 2058000 Value at Year End 14000000 9800000 6860000 4802000 CASH FLOW CALCULATION OF THE PROJECT Qty, to be sold (A) 5500 5500 5500 5500 Sales Price (B) 3,300 3,366 3,433 3,502 106 107 108 109 Variable Cost (C) Sheet11 Sheet1Sheet2 869 Sheet3 886 Sheet 12 904 Sheet14 922 Sheet16 Sheets Sheet13 Sheet15

& Paste Insert Delete Format Merge Center Alignment Format Painter Chipboard Conditional Format Formattingas Table Cell Styles Clear Sort t er Celting Find Select Number A133 Contribution D-B-C) 2,431 2,480 2,529 2,580 Total value (AD) 13,370,500 13,637,910 13,910,668 14, 188,882 Other Fixed Cost 800000 816000 832320 148966.4 Depreciation 6000000 4200000 2940000 205800 Total Fixed cost 6800000 5016000 3772320 29069664 Total 6570500 8621910 10138348.2 11281915.16 Loss on Assets 4302000 Net Income before Tax 6570500 8621910 10138348.2 6979915.164 Tax @30% 1971150 2586573 3041504.46 2093974.549 Post Tax 4599350 6035337 7096843.74 4885940,615 Add: Depreciation & Non cash Cost 5000000 4200000 2940000 6360000 Cash Flow from Income Side 10599350 10235337 10036843.74 hthashet ht 1 1245940.61 heets Sheet16

Calibel General Center Conditional Form Forang C e i nsert Delete Format Merge Agent Formater Opboard A 155 s Sort & Find FerSet- Edling NWC recovery 3,852, 185.0 Net cash flow 10599350 102 15:37 100368413.74 15098125.65 NOWc 3,630,000.0 3,702,600.0 3,776,652.0 3,852 IRSO Additional WC Required 3,630,000.0 72,600.0 74,052.0 75,533.0 Cumulative Cash Flow 10599350 20834687 3 08/1530.74 4 5969656.39 Precedine Year Balance receuvered cash flow in 3rd Year 2nd Year 10036843.74 Payback Penod D-A+B/C Calculation of IRR Calculation of NPV Calculation of NPV Cash Outflow Machiney (20,000,000) 20000000 Machinery Working 3.630,000.0 Capital 23630000 1 0 working Capital 155 156 u (3,630,000) (23,630,000) Shooths sheath Sheer Shorts Shopts Shorts 2 Shee S heet14 Sheet15 Sheet16

H eral X out Pe Copy Format Pants Paste Wrap Test Merge Center Alignment tronal ng cell Shya e r for - A168 148 2nd Year 245313 10036843.74 Payback period D-A+B/C 22 Years Calentino Calculation of NPV Calculation of NPV Cash Outflow Machiney 120,000,000 20000000 Machinery Working 3,630,000.0 Capital 23630000 0 working Capital (3,680,000 .820,000 2 Cash in Flow Income Working Capital Total 10599350 (72,600.0) 10235337 (74,052.0) 10036843.74 (75,533.0) 11245940.61 3,852,185.0 Discounting Factor @9% WACC 10526750 0.9174 10161285 0.8417 9961310.7 0.7722 15098125.65 0.7084 9,657,568.81 8,552,550.29 7,691,959.56 10,695,892.85 10,525,750 10,161,285 9,961,311 15,098, 126 3 4 36,597,971.51 30.72% 12,967,921.51 NPV of the Project is

-11 A - General Wrap Tent Merge Center ΤΙΣ Αυτοτυπη Copy Η Conditional Format Formatting as Tables Cell moon Delete Formate con Clear Sort Be Find Fit Set Clipboard font Alannt A194 - CASH FLOW CALCULATION OF THE PROJECT-10% Decrease in Unitsales, Sales Variable cost Calculation of Depreciation Machinery Year Investment 20000000 140000 SROODOO Depreciation Rate 3096 30% 30% 30% Depreciation 6000000 4200000 2940000 2058000 Value at Year End 14000000 6860000 4802000 CASH FLOW CALCULATION OF THE PROJECT 190 Qty. to be sold (A) 4500 4500 4500 4500 Sales Price (B) 2,700 2,754 2,809 2,865 192 193 1941 Variable Cost C) 711 725 740 755 Sheet16

B U A Merge & Center e d Conditional te Formatting - as Table Styles Clear Sort Filter find Select- Paste Format Painter Clipboard A218 Nurser G Contribution D-B-C) 1,989 2,029 2.069 2.0 Total value (AD) 8,950,500 9,129,510 9,312, 100 9,498,342 Other Fixed Cost 800000 816000 832320 848966.4 Depreciation 6000000 4200000 2940000 2058000 Total Fixed Cost 6800000 5016000 3772320 2906965.4 Total 2150500 4113510 5539780.2 6591375.804 Loss on Assets 4302000 Net Income before Tax 2150500 4113510 5539780.2 2289375.804 Tax @30% 645150 1234053 1661934.06 686812.7412 Post Tax 1505350 2879457 3877846.14 1602563.063 Add: Depreciation & Non cash Cost 6000000 4200000 2940000 6360000 Cash Flow from Income Side 7505350 7079457 6817846.14 7962563.063

Wrap General Paste Merge Center Format Painter Conditional Format Formatting -as Table Cell intentDelete Format Styles Sort Filter Fund Seled A240 NWC recovery 7,578,7854 Net Cash Flow 7505350 7079457 105112985 NOWC 2,430,000.0 2,478,600.0 2,528,172.0 2,578,735.4 Additional WC Required 2,430,000.0 48,600.0 49,572.0 Cumulative Cash Flow 7505350 14584807 21402653.14 3 1943951.64 Preceding Year Balance reveovered Cash Flow in 3rd Year 3rd Year 1027316.86 10541298,5 Payback Period D-A1B/C 3.10 Years Calculation of IRR Calculation of NPV Calculation of NPV Cash Outflow Machiney (20,000,000) 20000000 Machinery Working 2.430,000.0 Capital 22430000 working Capital (2,430,000) (22,430,000) 0

Home Insert Lavt omu Review Call - A General Wise Tea Merge & Center Conditional Format Formatting as Table Ce Styles insert Dette F Sant Hind er-Select- Format Pointer Clipboard Number A757 Calculation of IRR 237 Calculation of NPV Calculation of NPV Cash Outflow Machiney (20,000,000) 20000000 Machinery Working 2.430,000.0 Capital 22430000 working Capital (2.430,000) 22.430,000) Cash In Flow Total 6,841,055.05 Income Working Capital 7505350 (18,600.0) 7079457 (49,572.0) 5817846.14 (50,563.4) 7962563.061 2,578,735.4 Discounting Factor 9% WACC 7456750 0.9174 7029885 0.8417 6/67282.7 0.7722 10541298.5 0.7084 + 5,916.913.56 7,456, 750 7,029,885 6,767,283 10,541.299 5,225,583.91 7,467,721.62 4 25,451,274.13 NPV of the Project is 3,021, 274.13 14.68% CASH FLOW CALCULATION OF THE PROJ les Price & Variable cost

Home Insert Lavt omu Review Call - A General Wise Tea Merge & Center Conditional Format Formatting as Table Ce Styles insert Dette F Sant Hind er-Select- Format Pointer Clipboard Number A757 Calculation of IRR 237 Calculation of NPV Calculation of NPV Cash Outflow Machiney (20,000,000) 20000000 Machinery Working 2.430,000.0 Capital 22430000 working Capital (2.430,000) 22.430,000) Cash In Flow Total 6,841,055.05 Income Working Capital 7505350 (18,600.0) 7079457 (49,572.0) 5817846.14 (50,563.4) 7962563.061 2,578,735.4 Discounting Factor 9% WACC 7456750 0.9174 7029885 0.8417 6/67282.7 0.7722 10541298.5 0.7084 + 5,916.913.56 7,456, 750 7,029,885 6,767,283 10,541.299 5,225,583.91 7,467,721.62 4 25,451,274.13 NPV of the Project is 3,021, 274.13 14.68% CASH FLOW CALCULATION OF THE PROJ les Price & Variable cost

n Mergt & Center intentDelete Format Conditional Format Formatting Table cell Styles Sortin Clear Format Painter Chipboard A2M) - Alignmen A B CASH FLOW CALCULATION OF THE PROJECT-25% Dearea Sales Price R Variable Cost calculation of Depreciation Machinery Investment 2000000 14000000 9800000 6860000 Depreciation Rate 3096 30% 30% Depreciation 6000000 4200000 2058000 2940000 Value at Year End 4802000 14000000 9800000 68160000 CASH FLOW CALCULATION OF THE PROJECT Qty. to be sold (A) 3750 3750 3750 2,250 Sales Price (8) 2,295 2,388 593 604 279 Variable Cost (C) 629 Sheet16

M Merge & Center 8 w l . Insert Delete Format Cell Styles Delete Format Conditional Format Formatting as Table Styles Copy paste Format Painter Clipboard A303 clear Sort & Find Filter - Select Editing Alignment Number 281 Contribution D-B-C) 1,658 1. 691 1 .724 1,759 22 283 Total value (AD) 6,215,625 6,339,938 6,466,736 6,596,071 Other Fixed Cost 800000 816000 832320 818966.4 Depreciation 6000000 4200000 2940000 2058000 Total Fixed Cost 6800000 5016000 3772320 2906966,4 Total -584375 1323937.5 2694416.25 3689104.575 Loss on Assets 4302000 Net Income before Tax -584375 1323937.5 2691116.25 612895.425 Tax @30% -175312.5 397181.25 808324.875 -183868.6275 Post Tax -409062.5 926756.25 1886091.375 -129026.7975 Add: Depreciation & Non cash Cost 5000000 4200000 2940000 6360000 Cash Flow from Income Side Sheat Sheet2 5590937.5 Sheer 5126756.25 Sheet1 4826091.375 Sheet13 Sheet14 5930973.203 Sheet15 Sheet16 Sheet

Cut Calibri 11A General Fe Copy Wrap Tent Merge & Center S - Conditional Format Cell Formatting Table Styles Insert Delete Format Clear Paste Format Painter Clipboard A325 Son Filter Editing find Select Alignment Number NWC recovery 1,790, 788.5 305 306 307 Net cash Flow 5590937.5 517b756.25 4826091.375 7721761.703 NOWC 1,687,500.0 1,721,250.0 1,755,675.0 1.790.788.5 Additional WC Required 1,687,500.0 33,750.0 34,425.0 35, 113.5 Cumulative Cash Flow 5590937.5 10717693.75 15543785.13 23765546.83 Preceding Year Balance receovererCash Flow in 3rd Year 3rd Year 6143714.875 7721761.703 Payback Period D=A+B/C 30 Years Calculation of IRR Calculation of NPV Calculation of NPV Cash Outflow Machiney (20,000,000) 20000000 Machinery Working 1,687,500.0 Capital 21687500 working Capital (1,687,500) (21,687,500) 0

Home Insert Page Layout formulas Data Review X Colibri - 1 General Cut fg Copy Paste Format Painter Clipboard Wrap Tent Merge & Center BUH 5 % Conditional Format Formatting as Table Cell Styles - Insert Delete Format Clear Sortid Filter Select Allon men A341 He Calculation of IRR Calculation of NPV Calculation of NPV Cash Outflow Machiney (20,000,000 20000000 Machinery Working 1,687,500.0 Capital 21687500 working Capital 1.687 500) (21.687,500 0 Cash In Flow Year + Income Working Capital Total 1 5590937.5 133,750.0) 2 5126756.25 (34,425.0) 4826091.375 (35,113.5) 4 5930973.203 1,790,788.5 Discounting Factor @9% WACC 5557187.5 0.9174 5092331.25 0.8417 4790977.875 0.7722 7721761.703 0.7084 5,098,337.16 4,286,113.33 3,699,513.97 5,470,290.66 5,557,188 5,092,331 4,790,978 7,721,762 332 18,554,255.12 335 (3,133,244.88) 2.55% NPV of the Project is

Alignment Styles Clipboard A364 - CASH FLOW CALCULATION OF THE PROJECT-25% Increase ales, Sales Price and Variable cost Calculation of Depreciation Machinery Year Investment 20000000 14000000 9800000 6860,000 Depreciation Rate 30% 30% 30% Depreciation 6000000 4200000 2940000 2058000 Value at Year End 14000000 9800000 6860000 4802000 CASH FLOW CALCULATION OF THE PROJECT 359 360 Qty. to be sold (A) 6250 6250 6250 6250 361 362 Sales Price (B) 3,750 3,825 3,902 3,980 363 364 Variable Cost (C) Chat She 988 Sheet3 1,007 Sheet 2 1,027 Sheet13Sheet4 1,048 Sheet 16 Sheet2 Sheets Sheet15

ECODY F A B E Merge & Center Format Painter B Clipboard Conditional Format Condition Formatting as Table Cell Styles - Insert Delete Format Sort Filler Editing F Select Alignment Number A388 Contribution D-B-C) 2,763 2,818 2,874 2,932 366 367 368 Total value (AED) 17,265,625 17,610,938 17,963,156 18,322,419 369 370 Other Fixed Cost 800000 816000 832320 $148956.4 Depreciation 6000000 4200000 2940000 2058000 Total Fixed Cost 6800000 5016000 3772320 2906966.41 Total 10465625 12594937.5 11190836.25 15415452.98 Lass on Assets 4302000 Net Income before Tax 10465625 12594937.5 14190836.25 11113452.98 Tax @30% 3139687.5 3778481.25 |_4257250.875 3 334035.893 Post Tax 7325937.5 8816456.25 9933585.375 7779117.083 Add: Depreciation & Non cash Cost 6000000 4200000 2940000 6360000 387 Cash Flow from Income Side 388 13325937.5 13016456. 25 1 2873585.38 14139417.08

Σ Αισθοδοτο: General te Copy Wrap Text Merge Center Σ Calibri Paste Format Pointer Bu Clipboard - $ - Insert Delete Format Coritonal Format Formatting as Table Cell Styles Clear - Sort Filter Editing Find Select- Mignment Number A410 NWC recovery 4,974 412.5 Net cash Flow 13325937.5 13016156.25 12873585.38 1191113829.58 NOWC 4,687,500.0 4,781,250.0 4,876,875.0 4,974 412.5 Additional WC Required 4,687,500.0 93,750.0 95,625.0 97,537.5 Cumulative Cash Flow 13325937.5 26312393.75 39215979.13 58879808,71 Precedine Year Balance receovered Cash Flow in 3rd Year 1st Year 11361562.5 13016456.25 Payback period D=A+B/C 187 Years Calculation of Calculation of NPV Calculation of NPV IRR Cash Outflow Machiney (20,000,000) 20000000 Machinery Working 4,687,500.0 Capital 24687500 410 working Capital (4,687,500) (24,687,500) 411 0

te copy Merge & Center Paste Format Painter Clipboard Conditional Format Formatting as Tatilde IntentDelete format Cell Styles Cina Sort Foter find & Select Alignment A406 А В 406 Calculation of IRR Calculation of NPV Calculation of NPV Cash Outflow Machiney (20,000,000) 20000000 Machinery Working 4,687,500.0 Capital 24687500 0 working Capital 14,687,500) 24,687,500) Cash In Flow Year Discounting Factor @9% WACC 13232187.5 0.9174 12920831.25 0.8417 12776047.88 0.7722 19113829.58 0.7084 Income Working Capital Total 13325937.5 193,750,0) 13016456.25 (95,625.0) 12873585.38 (97,537.5) 14139417.08 4,974 412.5 2 3 4 + 12,139,621.56 10,875,205.16 9,865,453.11 13,540,718.76 | نیا 13,232,188 12,920,831 12,776,048 19,113,830 46,420,998.58 21,733,498.58 NPV of the Project is 424 425 426