Net Present Value Analysis

I need help analyzing the NPV by creating an Excel spreadsheet, using the details below

Thanks for your effort.

------------
PROJECT 3 - Net Present Value Analysis - Rev 1

Leathersmells.com is a (fictitious) profitable Internet-based company selling high-end fine leather furniture. The company is considering expanding its product selection to include large flatscreen HDTVs. Three months ago, Leathersmells.com completed a $250,000 marketing research study, which revealed that many fine leather furniture buyers would also be interested in buying large flatscreen HDTVs online. Therefore, the company has a built-in clientele for the proposed new offerings.

Up-front capital expenditures associated with the investment consist of $50,000 for a forklift with special shock absorbers and padded forks to handle the televisions, which are somewhat more delicate than the company's traditional sofas and loveseats. Moreover, the new product line will require the firm to update its computer equipment in year 2. This update will require an additional $3,000 in capital expenditures. 1The firm is planning to exit the HDTV business and sell its special forklift at the end of year 6, when managers expect several Chinese HDTV manufacturing plants to come on line, releasing a flood of HDTVs onto the U.S. market and depressing margins for the product. The market value of the forklift at year 6 is expected to be $20,000.

Currently, the average selling price of a large flatscreen HDTV is $2,500, a price which is expected to decline at a 5% annual real rate. 2The market study predicts the following HDTV sales volume per year over the next six years (see Exhibit 1).
Exhibit 1
Year

0
1

2
3
4
5
6
HDTVs 0 800 1,600 2,400 3,600 3,600 3,000

1 Assume all capital expenditures in this project can be depreciated using the schedule for the 5-year MACRS Recovery Period Class.
2 This rate is the only measure expressed in real terms (all other values and rates reported in the case are expressed in nominal terms).

 
The additional operating costs from the HDTV operation include a fixed cost of $25,000 each year starting in year 1 and variable costs that depend on the number of HDTVs sold. The average cost per unit is $2,000 and company executives believe that the cost of large flatscreen HDTVs supplied to the firm will decrease over time at a 7% annual (nominal) rate.
The forecasted working capital requirements for this project are reported in Exhibit 23.

Exhibit 2
Year

0
1
2
3
4
5
6
Cash 2,000 3,000 2,500 3,500 3,200 3,300 2,500
Accounts Receivable 0 4,500 11,800 19,100 26,700 29,800 4,500
Inventory 6,000 10,300 21,900 25,500 42,800 31,400 0
Accounts Payable 3,500 4,300 11,200 17,300 25,200 22,000 7,000

The company projects that some of the existing clients who are going to buy large flatscreen HDTVs will also purchase more fine leather sofas, since leather sofas and large flatscreen HDTVs tend to be complementary goods. As a consequence, the company's management estimates that the sale of fine leather sofas will increase by 80 units in year 1, and by 110 units in each of the following years over current levels.4 Each fine leather sofa currently provides a before-tax profit of $125, which the firm projects to increase at a (nominal) 2% annual rate.
Leathersmells.com pays taxes at a 35% rate. Inflation is expected to remain at 2.2% over the next six years. The average (equity) beta for the Internet selling industry is 2.10. The cost of debt for Leathersmells.com is 6.5%. Moreover, the firm's stock is currently trading at $15 and the firm has 10 million common shares outstanding. The market value of the firm's outstanding debt is $50 million. A good estimate of the market's risk premium is 7.2%, and the risk-free rate is 2.9%.
Calculate the Net Present Value of the project using the appropriate weighted average cost of capital as the discount rate. Should Leathersmells.com undertake the HDTV project?

3 Working capital equals current assets minus current liabilities. Cash, accounts receivable, and inventory are current assets while accounts payable are current liabilities.
4 Note that, if current sales are represented by X, then sales in years 0 through 6 are represented by X, X + 80, X + 110, X + 110, X + 110, X + 110, and X + 110.