Question

I need you to work this assignment out using excel. It will be a huge help to me. Thanks.

The ABC Project 1 3 The ABC Company currently markets its chocalate and other related products. 4While seeking expansion ideas, management of the company decided to look into the possibility of a line of the new product. 5 Entry into this business would require the purchase of an existing 50-acre land in Atalanta GA at a cost of $250,000 for the land and S40,000 for the buildings and equipment. 6 At the end of the five years, management anticipates that the farm will be sold for S350,000 for the land and $20,000 for the buildings and equipment. 8 The marketing department estimates that the firm will be able to sell 25,000 pounds of the product at an average wholesale price of $7.50 per pound during the first year 9 Unit demand is expected to grow at a rate of 8% annually thereafter 10 Variable operating expenses are expected to average 55% of gross sales, and fixed costs (notincluding depreciation) will be $60,000 per year 11 The company's marginal tax rate is 35% and its WACC is 10%. 12 13 We are hired to analyze the profitability and riskiness of the project. 14 1. Please use the formated spreadsheet to compute, Net Present Value, Profitability Index and Internal Rate of Return. 15 2. We shall investigate the following senerios of Worst, Medium and Best Cases, in terms of First Year Sales, Price per Pond, Unit Sales Growth Rate and Variable Cost, as below 16 Worst Medium Best First Year Sales (Ibs) Price per Pound Unit Sales Growth Rate Variable Costs as % of Sales 18 19 20 21 12500 25000 35000 $5.50 $7.50$9.70 8% 10% 55% 5% 65% 60% 23 24 25 3. we estimate the probabilities for the worst, Medium and Best Scenarios as 30% 50% and 20%, respectively. What is the Expected NPV ofthe project? 26 Please use Scenario Manager of Excel to generate the report for the three Scenarios. Please use Sumproduct formula for the compuation. ABC Project Inputs 2 Cost of Land 3 Cost of Buildings & Equipment 4 Life of Project (Years) 5 Terminal Value of Land 6 Teminal Value of Buildings & Equipment 7 First Year Sales (Ibs) 8 Price per Pound 9 Unit Sales Growth Rate 10 Variable Costs as % of Sales 11 Fixed Costs per Year 12 Tax Rate 13 WACOC 14 15 16 17 Initial Outlay 18 Sales 19 Variable Costs 20 Fixed Costs 21 Taxable Cash Flows 22 Taxes 23 Annual After-Tax Cash Flow 24 Terminal Cash Flow 25 Total Annual Cash Flows 26 27 Net Present Value 28 Profitability Index 29 Intenal Rate of Retum Scenario Manager Worst Medium Best Annual Cash Flows for ABC Project Year 0 Year l Year 3 Year 4 Year 2 lear

Answer 1

1)

ABC project inputs Cost of Land Cost of Buildings andequipment Life of Project Terminal Value of Land I erminal Value 0t Building and Equipment First Year sales Price per Pound Unit Sales Growth Rate Variable Costs as % of Sales Fiked Costs per year Tax Rate WACC 250000 40000 350000 20000 25000 60000 10% Annual Cash Flows Initial Outlay Sales Variable Costs Fied Costs Depreciation Taxable cash Flows Taxes Annual After Ta Cash Flow Depreciation Terminal Cash Flow Total Annual Cash Flow -290000 187500 202500 218700 236196 255092 103125 111375120285 129908 140300 60000 60000 60000 60000 60000 4000 4000 4000 4000 4000 20375 27125 34415 42288 50791 7131.3 9493.812045 8 17777 13244176312237027487 33014 4000 4000 4000 4000 4000 370000 29000017244 21631 26370 31487 407014 3,27,595.32 2.129639048 Net Present Value Profitability Indew Internal Rate of Return

2)

Scenario Summary Current Values Worst Best Changing Cells: SB$7 SB$8 SB$9 ŞB$10 25000 7.5 0.08 0.6 12500 5.5 0.05 0.65 35000 9.7 0.55 Result Cells: $B$28 3,00,885.54 1,52,125.15 5,38,587.99 ŞB$30 30% 11% Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in grav Probability Expected 274045.8353