Dee-Growth Company is specialized in recycling existing old house appliances into new
ones, using parts in good condition and adding innovative features to manufacture a
modern appliance. It is a Business-to-Business (B2B) company that sells the house
appliances to the leading wholesalers but not directly to consumers.
The CEO is convinced that the company should develop a new product called Dee-Wash,
which is an innovative washing machine made of parts of old washing machines and
recent electronic innovations, especially about special programs that save water and
electricity and take care of delicate materials.
You have been chosen to realize the capital budgeting work
on this project.
To help you in this task, Dee-Growth has just completed a $70,000 feasibility study in
October 2016, to figure out the costs, revenues, investments and all the consequences
that could be linked to such a project.
– General information:
The production will last 3 years, from 2017 to 2019.
The cost of the new production line is $20,000,000 and will be depreciated on a
Straight-line basis to a zero book value over 5 years.
The production line has been paid in 2016 (year 0) but the production is expected
to start in 2017 only (year 1).
The machine will be sold $12,000,000 (before taxes). Assume that the after-tax
selling price of the investment will be made at the end of the project (in 2019).
The project’s opportunity cost of capital is 12%. The corporate tax rate is 20%.
The company is globally profitable.
– Marketing:
Sales are expected to be 180,000 units in 2017, 320,000 units in 2018 and
120,000 units in 2019. The forecasted selling price per unit is $220.
The sales of Dee-Wash should decrease the sales of the old washing
machine model by 80,000 units in 2017, 120,000 units in 2018 and
120,000 units in 2019 (sale price of the old model is $170 per unit).
– Operations:
The cost of goods sold is expected to be 125 $ per unit.
Previous analysis showed that cost of sales of the old model is equal to
$100 per unit.
– Other Operating Expenses:
The new production line will require additional administrative, marketing
and overhead cost of $5,000,000 per year.
Working Capital linked to the project:
The account receivables are equal to 60 days of sales.
Inventories are equal to 75 days of cost of sales.
The accounts payable are equal to 45 days of cost of sales.
You are considering using 360 days per year for the calculations.
Assume that the working capital will be recovered one year after the end of
the project (in 2020).
Work to do:
You are asked to prepare a report in a word format to be sent to the management of
your company giving your opinion about the opportunity to invest in this project,
answering to the following questions:
Calculate the After tax Selling price of the production line at the end of
2018.
Determine the free cash flows of the project.
Calculate the net present value (NPV) of the project.
Given your calculation of the NPV, you will write a note giving your
suggestion regarding the project: Should the managers undertake this
investment?
What other criteria and methods could you use to take your
decision?
How a sensitivity analysis would be helpful? And regardless
financial aspects, do you think this project is a good opportunity? What
do you think about the risk of this project? Does the Opportunity cost of
capital that you used in your calculations seem reasonable?
INSTRUCTIONSThis workbook contains tables and examples from Chapter 8. Input formulas in the shaded areas to recreate the tables in the book, and substitute your own values for the bold blue numbers to see how the tables change. HOMENET
KEY ASSUMPTIONS Year012345 Key Assumptions HomeNet Units Sold (000s) 100 100 100 100 Sale Price ($/unit) 260 260 260 260 Cost of Goods ($/unit) 110 110 110 110 Existing Router Units Sold (000s)25% (25) (25) (25) (25) Sale Price ($/unit) 100 100 100 100 Cost of Goods ($/unit) 60 60 60 60 Operating Expenses ($000s) Hardware and Software Development (15,000) Marketing and Support (2,800) (2,800) (2,800) (2,800) Lost Rent (200) (200) (200) (200) Capital Expenditures Lab Equipment (7,500) Depreciation 20%20%20%20%20% Marginal Corporate Tax Rate40%40%40%40%40%40% Working Capital Receivables (% of Sales) 15%15%15%15% Payables (% of COGS) 15%15%15%15% TABLE 8.1
SPREADSHEETHomeNet’s Incremental Earnings Forecast Year012345 Data from lines Incremental Earnings Forecast ($000s) 1Sales 9 & 10 2Cost of Goods Sold 9 & 11 3Gross Profit 4Selling, General, and Administrative 18 5Research and Development 17 6Depreciation 21 & 22 7EBIT 8Income Tax at 40% 23 & 38 9Unlevered Net Income Table 8.2
SPREADSHEETHomeNet’s Incremental Earnings Forecast Including Cannibalization and Lost Rent Year012345 Data from lines Incremental Earnings Forecast ($000s) 1Sales 9, 10, 13 & 14 2Cost of Goods Sold 9, 11, 13 & 15 3Gross Profit 4Selling, General, and Administrative 18 & 19 5Research and Development 17 6Depreciation 21 & 22 7EBIT 8Income Tax at 40% 23 & 52 9Unlevered Net Income Table 8.4
SPREADSHEETHomeNet’s Net Working Capital Requirements Year012345 Data from lines Net Working Capital Forecast ($000s) 1Cash Requirements 2Inventory 3Receivables (15% of Sales) 25 & 46 4Payables (15% of COGS) 26 & 47 5Net Working Capital Table 8.3
SPREADSHEETCalculation of HomeNet’s Free Cash Flow (Including Cannibalization and Lost Rent) Year012345 Data from lines Incremental Earnings Forecast ($000s) 1Sales 9, 10, 13 & 14 2Cost of Goods Sold 9, 11, 13 & 15 3Gross Profit 4Selling, General, and Administrative 18 & 19 5Research and Development 17 6Depreciation 21 & 22 7EBIT 8Income Tax at 40% 23 & 76 9Unlevered Net Income Free Cash Flow ($000s) 10Plus: Depreciation 75 11Less: Capital Expenditures 21 12Less: Increases in NWC 64 (change from previous period) 13Free Cash Flow Table 8.5
SPREADSHEETComputing HomeNet’s NPV Year012345 Data from lines Net Present Value ($000s) 1Free Cash Flow 83 2Project Cost of Capital12% 3Discount Factor 90 4PV of Free Cash Flow NPV without rounding 5NPV 0.00 SUMMARYSUMMARY SPREADSHEET: COMBINED EARNINGS/FCF/NPV Year012345 Incremental Earnings Forecast ($000s) 1Sales 2Cost of Goods Sold 3Gross Profit 4Selling, General, and Administrative 5Research and Development 6Depreciation 7EBIT 8Income Tax at 40% 9Unlevered Net Income Free Cash Flow ($000s) 10Plus: Depreciation 11Less: Capital Expenditures 12Less: Increases in NWC 13Free Cash Flow Net Present Value ($000s) 14Project Cost of Capital12% 15Discount Factor 16PV of Free Cash Flow 17NPV Table 8.6
SPREADSHEETNPV Cost of Outsourced Versus In-House Assembly of HomeNet Year012345 Outsourced Assembly ($000s) 1EBIT – (11,000) (11,000) (11,000) (11,000) – 2Income Tax at 40% – 4,400 4,400 4,400 4,400 – 3Unlevered Net Income – (6,600) (6,600) (6,600) (6,600) – 4Less: Increases in NWC – 1,650 – – – (1,650) 5Free Cash Flow – (4,950) (6,600) (6,600) (6,600) (1,650) 6NPV at 12% (19,510) Year012345 In-House Assembly ($000s) 1EBIT (5,000) (9,500) (9,500) (9,500) (9,500) – 2Income Tax at 40% 2,000 3,800 3,800 3,800 3,800 – 3Unlevered Net Income (3,000) (5,700) (5,700) (5,700) (5,700) – 4Less: Increases in NWC – 633 – – – (633) 5Free Cash Flow (3,000) (5,067) (5,700) (5,700) (5,700) (633) 6NPV at 12% (20,107) Table 8.7
SPREADSHEETHomeNet IRR Calculation Year012345 NPV ($000s) and IRR 1Free Cash Flow – – – – – – 2NPV at 12% – 3IRR #NUM! Table 8.9
SENSITIVITY ANALYSISBest- and Worst-Case Parameter Assumptions for HomeNet CurrentWorstBaseBestBreakeven Units Sold:$f$9 7010013079.8 NPV 0 – – – – Sale Price:$f$10260240260280232 NPV 0 – – – – Cost of Goods$f$11110120110100138 NPV 0 – – – – Net Working Capital 030002100160017545 change relative to current$f$61 -30002100160017545 NPV 0 – – – – Cannibalization$d$1325%40%25%10%95.9% NPV 0 – – – – Cost of Capital $d$9012%15%12%10%24.11% NPV 0 – – – – Table 8.10
SCENARIO ANALYSISScenario Analysis of Alternative Pricing Strategies Two-Way Data Table (row $e$6, column $e$7) Units Sold (000s) -90100110120 Price per Unit ($) 245 – – – – 2600000 2750000 2800000 Sale PriceUnits NPV Current Strategy2601000 Price Reduction2451100 Price Increase275900