Question

Scenario Analysis (L02] Consider a project to supply Detroit with 35,000 tons of machine screws annually for automobile production. You will need an initial $3.200.000 investment in threading equipment to get the project started the project will last for five years. The accounting department estimates that annual fixed costs will be $450,000 and that variable costs should be $185 per ton, accounting will depreciate the initial fixed asset investment straight-line to zero over the five-year project life. It also estimates a salvage value of $500,000 after dismantling costs. The marketing department estimates that the automakers will let the contract at a selling price of $230 per ton. The engineering department estimates you will need an initial net working capital investment of $360,000. You require a 13 percent return and face a marginal tax rate of 38 percent on this project. a. What is the estimated OCF for this project? The NPV? Should you pursue this project? b. Suppose you believe that the accounting department's initial cost and salvage value projections are accurate only to within 15 percent; the marketing department's price estimate is accurate only to within +10 percent; and the engineering department's net working capital estimate is accurate only to within 5 percent. What is your worst-case scenario for this project? Your best-case scenario? Do you still want to pursue the project?
also please help me with this one and please im begging toanyone to specifically make the base best worst case.. thanks..

Answer 1

Since the file size exceeding the required / approved limit hence, calculation given as below.

Answers.xlsx - Excel Pagri, Dhiraj - ) X Share File Tell me what you want to do - AutoSum - Ay AutoSave on AS A , Home Insert Page Layout Formulas Data Review View Help X Cut Calibri .11 - AA === > Wrap Text Copy Paste Format Painter BIU . . A M erge & Center, Clipboard Font Alignment - X for Variable Costs D General S.% . Conditional Format as Cell Formatting Table Styles Styles Insert Delete Format - Cells Clear - Sort & Find & Filter - Select Editing Number E F G H I J K L M N O P Machine Capacity 35,000 Tonne / Year v v v Initial Investment Project period Annual Fixed Costs Variable Costs Depreciation Period Salvage Value selling Price Working Capital Expected return Marginal Tax Rate 3,200,000 5 Years 450,000 185 per Tonne 5 Years 500,000 230 per Tonne 360,000 13% ) V ( 38% Depreciation calculation Value of Assets after salvage value $ Depreciation / Year 2,700,000 540,000 Answer too Estimated OCF (Operating Cash Flow) Year 1 Machine Capacity Sales price 35,000 230 Year 2 35,000 230 Year 3 35,000 230 Year 4 35,000 230 Year 5 35,000 230 S S S S Total Inflow 8,050,000 S 8,050,000 S 8,050,000 $8,050,000 S 8,050,000 Solution Sheet2 Sheet4 Sheet3 Ready 2a - MOTS e 1 2 0 6e Ae wi - + 80% ... 6:17 PM * 11/3/2019

Answers.xlsx - Excel Pagri, Dhiraj - ) AutoSave on AS Home Insert A Page Layout , Formulas X Share File Data Review View Help Tell me what you want to do - AutoSum - Ay Paste . X Cut Calibri .11 - AA === > Wrap Text Copy at Painter BIU. B A E Format Painter Merge & Center - Clipboard Font Alignment General $ - % Number Clear - ,00 Conditional Format as Cell Formatting Table Styles Styles Insert Delete Format - Cells Sort & Find & Filter - Select Editing C54 H I J K L M N O P . US Expenses Annual Fixed Costs Variable Costs Depreciatiuni (450,000) S (6,475,000) $ (540,000) $ (450,000) S 16,475,000) $ 1540,000) $ (450,000) S (450,000) S (6,475,000) $ 16,475,000) $ (540,000) $ (540,000) $ (450,000) (6,475,000) (540,000) Net Income 585,000 5 585,000 S 585,000 $ 585,000 S 585,000 Tax Amount (222,300) S (222,300) S (222,300) S (222,300) S (222,300) Income After Tax 362,700 S 362,700 S 362,700 S 362,700 S 362,700 Operating cash Flow (Before depreciation) As depreciation is not an outflow OCF 902,700 $ 902,700 $ 902,700 $ 902,700 $ 902,700 NPV Years Amount Inflow/(Outflow) Net Present Value (3,560,000) S (3,560,000) 902,700 $ 798,850 902,700 S 706,947 902,700 S 625,616 902,700 $ 553.613 902,700 $ 489,949 500,000 $ 271,380 Salvage Value S (113,615) NPV We should not pursue the projectos NPV is negative. Which shows that the project is not worth pursuing. Solution Sheet2 Sheet4 Sheet3 + Ready 2 -- 11° 0 0 e d e 9 wi x] 02 A a MOTS e + 80% 6:18 PM * 11/3/2019

Pagri, Dhiraj - ) X Share File AutoSave on AS A , Answers.xlsx - Excel Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do X Cut Calibri .11 - AA === > Wrap Text General Copy Paste Format Painter BIU.E A Merge & Center - $ -% Conditional Format as Cell Formatting Table Styles Clipboard Font Alignment Number Styles - AutoSum - Ay Insert Delete Format - Cells Clear Sort & Find & Filter - Select Editing C82 D E F G H I J K L M N O P - Answer to be Best Case Estimated OCF Assuming Sales Price Working capital Initial Costs & Salvage value reduced by Increase by Decreased by 15% 10% 2,720,000 Initial Costs revised value Salvage Value 425,000 Depreciation Revised Working Capital 459,000 342,000 Year 1 Machine Capacity Sales price Year 2 35,000 253 35,000 253 Year 3 35,000 253 Year 4 35,000 253 Year 5 35,000 S S S S 253 Total Inflow 8,855,000 $ 8,855,000 S 8,855,000 $8,855,000 S 8,855,000 Expenses Annual Fixed Costs Variable Costs Depreciation (450,000) $ (6,475,000) $ (459,000) S (450,000) $ 16,475,000) $ (459,000) S (450,000) $ 1450,000) $ (6,475,000) $16,475,000) $ (459,000) S (459,000) S (450,000) (6,475,000) (459,000) Net Income 1,471,000 S 1,471,000 s 1,471,000 $1,471,000 S 1,471,000 Solution Sheet2 Sheet4 Sheet3 Ready 11° 0 e 6 e 9 w x] 0 a 2 -- Seu H - + 80% ... 6:18 PM 11/3/2019 11

Answers.xlsx - Excel Pagri, Dhiraj - ) AutoSave on AS Home Insert A Page Layout , Formulas X Share File Data Review View Help Tell me what you want to do - X Cut . AutoSum - Ay Wrap Text Accounting Merge & Center - $ -% Calibri -11 -AA === > Copy - Paste Format Painter BIU.E A Clipboard Font C109 for $C$9*(1+D104) D Conditional Format as Cell Formatting Table Styles Styles X Insert Delete Format - Cells Clear - Sort & Find & Filter - Select Editing Alignment Number H I J K L M N O P . F (558,980) S (558,980) S G (558,980) Tax Amount (558,980) S (558,980) S Income After Tax 912,020 S 912,020 S 912,020 S 912,020 S 9 12,020 Operating cash Flow (Before depreciation) OCF 1,371,020 $ 1,371,020 $ 1,371,020 $ 1,371,020 $ 1,371,020 NPV Years Amount Inflow/(Outflow) NPY (3,062,000) $ 1,371,020 $ 1,371,020 S 1,371,020 $ 1371.020 $ 1,371,020 $ 425,000 $ (3,062,000) 1 213,292 1,073,710 950,186 840,872 744,135 230,673 Salvage Value 1,990,867 NPV Worst Case 15% Assuming Sales Price Working capital Initial Costs & Salvage value increase by Decrease by Increased by -10% 5% 100 109 3,680,000 575,000 Initial Costs revised value Salvage Value Solution Sheet2 Sheet4 Sheet3 → Ready 2 -- a MOTS e 110 0e e gwi + 80% ... 6:21 PM * 11/3/2019

Pagri, Dhiraj - ) X Share File - Σ AutoSum * Α cum 2 Ο O AutoSave on AS A , Answers.xlsx - Excel Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do X Cut Calibri -11 -AA === > Wrap Text Accounting . Copy - Paste Format Painter BIU.E A Merge & Center - $ -% Conditional Format as Cell Formatting Table Styles Clipboard Font Alignment Number Styles C137 - X f x --C108-112 D E F G H Depreciation 621,000 Revised Working Capital S 378,000 - X Insert Delete Format - - Cells Clear - Sort & Find & Filter - Select Editing I J K L M N O PA Year 1 Machine Capacity Sales price 35,000 207 Year 2 35,000 207 Year 3 35,000 207 Year 4 35,000 207 $ Year 5 35,000 207 S $ $ Total Inflow 7,245,000 S 7,245,000 $ 7,245,000 $ 7,245,000 S 7,245,000 Expenses Annual Fixed Costs Variable Costs Depreciation (450,000) $ (6,475,000) $ (621,000) S (450,000) $ 16,475,000) $ (621,000) S (450,000) $ 1450,000) $ (6,475,000) $16,475,000) $ (621,000) S (621,000) S (450,000) (6,475,000) (621,000) Net Income (301,000) S (301,000) S (301,000) S (301,000) S (301,000) Tax Amount - $ . $ . $ $ Income After Tox (301,000) S (301,000) S (301,000) S (301,000) S (301,000) Operating cash flow (Before depreciation) OCF 320,000 $ 320,000 $ 320,000 $ 320,000 $ 320,000 NPV 136 Years Amount Inflow/(Outflow) 137 (4,058,000 NPV $ (4,058,000) Solution Sheet2 Sheet4 Sheet3 © Ready 2 -- a MOTS e 110 0e e gwi - + 80% ... 6:21 PM * 11/3/2019 S

Pagri, Dhiraj - ) X Share File - AutoSum - Ay AutoSave on AS A , Answers.xlsx - Excel Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do X Cut Calibri .11 - AA === > Wrap Text General Copy - Paste Format Painter BIU.E A Merge & Center - $ -% Conditional Format as Cell Formatting Table Styles Clipboard Font Alignment Number Styles C160 - X for D E F G H Insert Delete Format - Cells Clear - Sort & Find & Filter - Select Editing I J K L M N O PA NPV Years Amount Inflow/(Outflow) NPV (4,058,000) $ 320,000 $ 320,000 $ 320,000 $ 320,000 $ 320,000 S 575,000 $ S (4,058,000) 283,186 250,607 221,776 196,262 173,683 312,087 Salvape Value (2,620,399) NPV If the Best case scenario prevails then we con pursue the project otherwise not. As the NPV under best case scenario is positive. 159 160 Solution Sheet2 Sheet4 Sheet3 © Ready 100 6e Ae gwi -- + 80% ... 6:21 PM MB Seu . * War 11/3/2019 11