Question

Dell is considering offering a new light-weight design laptop that would automatically be backed up to a cloud service. If a customer purchases the new product, they will transfer all files from the customers old machine to the cloud and have it synced to the new light-weight design laptop. The syncing station is estimated to have an initial start-up cost $9 mil along with an additional operating cost of $300,000 a year during the time they are selling the new laptop. Dell does not anticipate that they will be able to recoup any of these expenses at the end of the project, nor will any of these expenses be depreciated over the project life. Dell anticipates that they will be able to sell these new light-weight laptops for 7 years before it will become obsolete. The projected selling price for each laptop is ($1,800 for year 1 and decrease by 10% each year). The estimated demand for the laptop is 15,000 units in year 1, and is projected to increase by 11% each year for the life of the project. Each laptop will cost $1,000 to make. The project will require $2,700,000 in annual operating costs, which will increase 3% each year. Dell conservatively estimates that this new product will result in a $600,000 a year drop in sales from their existing laptop line. However, Dell anticipates that 35% of purchases will result in the purchase of an upgraded cloud service that sells for a one-time flat fee of $100. Dell will have to purchase an additional machine to manufacture the new light-weight laptops which will cost $400,000, and be depreciated using a 10-year straight line to zero schedule. At the end of the 7th year, Dell anticipates that it will be able to sell the machine for $25,000. Dell anticipates that it will need to have 10% worth of the current year’s laptop sales set aside in inventory to help sell the product, and will initially set aside 1 million in year 0. In the 7th year, all inventory will be liquidated and recouped. Dell currently has a tax rate of 21% and believes that a 15% discount rate would be appropriate given the amount of risk associated with the project. Use excel to complete the following questions:

A. What are the FCF for this project (years 0 to 7)?

B. What is the NPV and IRR of this project? Should Dell accept this project?

C. Graph the NPV of this project.

D. What is the accounting break-even and cash break-even for year 1 (ignore taxes)?

E. Conduct a Breakeven Analysis of the following assumptions: Sales growth, tax rate, Net-working capital-to-Sales, and Selling price discount (10% decrease in selling price each year). Report the expected value, the critical value (break-even), and the percentage change. Which input is the project’s success most sensitive to?

Answer 1

FCFF	EBIT*(1-Tax)+Depreciation+Changes in Working Capital-Capex

Sales = Selling Price * Demand

Depreciation

Machine COst = 400000

Life of Asset = 10

Straightline Method Depreciation

400000/10 = 40000

Gross profit = Sales - Operating Cost

EBIT = Gross Profit - Depreciation

Selling Price	0	1800	1620	1458	1312.2	1180.98	1062.882	956.5938
Demand	0	15000	16650	18481.5	20514.465	22771.05615	25275.87233	28056.21828
Inventory		142857.1429
Income Statement	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7
Sales	0	27000000	26973000	26946027	26919080.97	26892161.89	26865269.73	26838404.46
Cogs	0	2700000	2781000	2864430	2950362.9	3038873.787	3130040.001	3223941.201
Gross Profit	0	24300000	24192000	24081597	23968718.07	23853288.11	23735229.73	23614463.26
Depreciation	0	40000	40000	40000	40000	40000	40000	40000
EBIT	0	24260000	24152000	24041597	23928718.07	23813288.11	23695229.73	23574463.26
FCFF Calculation	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7
EBIT	0	24260000	24152000	24041597	23928718.07	23813288.11	23695229.73	23574463.26
EBIT *(1-(21/100))	0	19165400	19080080	18992861.63	18903687.28	18812497.6	18719231.49	18623825.98
Depreciation	0	40000	40000	40000	40000	40000	40000	40000
Changes in Working Capital	-1000000	-857142.86	-714285.71	-571428.57	-428571.43	-285714.29	-142857.14	0.00
Capex	-400000	0	0	0	0	0	0	0
FCFF	-1400000	18348257.14	18405794.29	18461433.06	18515115.85	18566783.32	18616374.34	18663825.98

NPV

CF = Cashflow

i = Discounted rate of Interest

n = no.of Years

No.of Years	0	1	2	3	4	5	6	7
Discount Rate	15%
NPV		15955006.21	18405794.29	18461433.06	18515115.85	18566783.32	18616374.34	18663825.98

IRR

CF = Current Period Cash Flow

i = Discounted rate of Interest

CF0 = Initial investment

NPV Graph

Accounting / Cost Break Even Point

Fixed Cost - Depreciation is Considered as Fixed cost

Fixed Cost	Depreciation is a fixed cost	40000	40000	40000	40000	40000	40000	40000
Gross Margin	Gross Profit /Sales	0.9	0.896896897	0.893697501	0.890398825	0.886997788	0.883491213	0.879875825
Account Breakeven Analysis & Cost Break even	44444.44444	44598.21429	44757.87382	44923.6891	45095.94111	45274.92683	45460.96037

Break Even Analysis & Sensitive Percentage

Fixed Cost = Depreciation Cost - 40000

Break even Analysis
Fixed cost		40000	40000	40000	40000	40000	40000	40000
Sales Prices		1800	1620	1458	1312.2	1180.98	1062.882	956.5938
Variable cost		180	185.4	190.962	196.69086	202.5915858	208.6693334	214.9294134
Contribution Margin		1620	1434.6	1267.038	1115.50914	978.3884142	854.2126666	741.6643866
Sensitive Percentage		24.69135802	27.88233654	31.56969246	35.85806567	40.88355853	46.8267465	53.93275007