This assignment needs to be in Excel, please

Capital Budgeting

The Dew Company is a soft drink company. Until today, the company bought empty cans from an outside supplier that charges Dew $0.20 per can. In addition, the transportation cost from the outside supplier to the factory is $1,000 per truck that transports 10,000 cans ($0.10 per can). The Dew Companys management is considering whether to start manufacturing cans in its plant. The cost of a can machine is $1,000,000 and its life span is 6 years. Historically, Dew Company used straight-line depreciation to zero for all of its equipment. This time they will depreciate $100,000 of the machines value every year and the book value at the end of year 6 would be 400,000. The company expects the equipment to be actually sold for $350,000 after 6 years. Maintenance and repair costs for the machine will be $50,000 per year. The additional space for the operation will cost the company $100,000 annually. The cost of producing a can in the factory will be $0.17. The cost of capital for Dew is 12% and the corporate tax rate is 40%. Also, assume that the Dew Company sells 4,000,000 soft drinks annually.

1. Calculate the initial outlay, annual operating cash flow and terminal cash flows. Complete the table with the timeline and calculate the NPV and IRR of the project. (8 points)

2. Create a Data Table that shows changes in NPV, when required rate changes (11%, 12%, 13%, 14%, 15%) (8 points)

Note: No credit will be given for answers without Data Table.

3. The company expects the equipment to be actually sold for $350,000 or $450,000 after 6 years. Create combo box for the two different market prices. (8 points)

4. The company expects the quantity sold to be 1,000,000, 4000,000, or 7000,000. Create option buttons for the three different quantity sold and group together the option buttons. (8 points)

5. Create a scenario summary table (using the scenario manager tool) to show what happens to the NPV and IRR for the following three scenarios. (8 points)

Three Scenarios for cost of production per unit and additional space costs:

	unit production cost	space costs
Worst	0.23	160,000
Expected	0.17	100,000
Best	0.12	50,000

Insert Page Layout Formulas ta Review View Share ^ AutoSum A Copy , A Merge & Center. $ , % , '00.00 Conditional Format Cel Insert Delete Format Sort & Format BIU Clear Formatting as Table Styles f Dew Company 1 Dew Com 2Data Section 3 Old cost per can (outside supplier) 4 New cost per can (in-house) s Transportation Cost (per can) 6 Machine Cost Machine Useful Life 8 Annual Depreciation (dollar ammount) 9 Book Value of Machine (end of year 6) 0 Sale Price of Machine (Actual Salvage-year 6) 11 Maintenance and Repairs 12 Additional Space Cost 13 Number of Cans Sold 14 Cost of Capital 15 Tax Rate 16 "You might not have to use all numbers from the data secton 17 Calculation Terminal Cash Flow Calculation 21 30 31 32 Cash FlowNPV- IRR 39 41 Sheet 1 Insert Page Layout Formulas ta Review View Share ^ AutoSum A Copy , A Merge & Center. $ , % , '00.00 Conditional Format Cel Insert Delete Format Sort & Format BIU Clear Formatting as Table Styles f Dew Company 1 Dew Com 2Data Section 3 Old cost per can (outside supplier) 4 New cost per can (in-house) s Transportation Cost (per can) 6 Machine Cost Machine Useful Life 8 Annual Depreciation (dollar ammount) 9 Book Value of Machine (end of year 6) 0 Sale Price of Machine (Actual Salvage-year 6) 11 Maintenance and Repairs 12 Additional Space Cost 13 Number of Cans Sold 14 Cost of Capital 15 Tax Rate 16 "You might not have to use all numbers from the data secton 17 Calculation Terminal Cash Flow Calculation 21 30 31 32 Cash FlowNPV- IRR 39 41 Sheet 1