The company is considering manufacturing designer desk lamps and you must estimate the cash flows from the project and apply capital budgeting tools to determine if the project is a value-creating proposition. You are required to build an Excel spreadsheet to analyze the project.

Sunlight Manufacturing, Inc. (SMI) has been successfully producing and selling electrical appliances for the past 20 years and is considering an extension of its existing product line. The company’s VP of Marketing has proposed that SMI enter the relatively high-margin, high-quality designer desk lamp market.

A consulting company was hired to do a preliminary study of the potential market for this type of product. The consulting company charged SMI $25,000 for the study and was paid a month later. Its report indicates that SMI can sell as many as 45,000 lamps the first year of the project, 40,000 the second year, 30,000 the third year, 20,000 the fourth year, and 10,000 the fifth year. The designer desk lamps can be sold for $40 each the first year and the price will increase by 3 percent each year to account for expected inflation. The project will be terminated after five years.

SMI’s sales manager is concerned that the new product will reduce the sales of SMI’s standard desk lamps. She fears that potential customers will switch from buying standard lamps to buying the new designer desk lamps and estimates that SMI’s potential losses from standard desk lamps could reduce the company’s after-tax operating cash flows by $110,000 per year.

If SMI decides to produce the designer desk lamp, it will use a building it already owns that is unoccupied. Recently, SMI received a letter from the vice president of a nearby department store that wanted to know whether SMI would be willing to rent the building to them as a storage area. SMI’s accounting department indicates that, given the current market rates, the building can be rented out for $10,000 per year for five years.

SMI’s engineering department has determined that the equipment needed to produce the designer desk lamps will cost $2 million, including shipping and installation. For tax purposes, the equipment can be depreciated according to the straight-line method over the next five years. That is, the annual depreciation will be $400,000 per year ($2 million divided by 5 years). At the end of the project’s life in year five, the engineering department estimates that the equipment can be resold for $100,000.

After consulting a few suppliers, the purchasing department says that the raw materials required to produce the designer desk lamp will cost $10 per lamp the first year and will most likely increase by the expected annual inflation rate of 3 percent each year. The operations department estimates that the project will have an annual working capital requirement equal to 20 percent of sales. However, to get the project started, an initial investment of $360,000 in working capital will be required. For the first year, the project will incur a labor cost of $5.00 per lamp and $1.00 of energy cost. After the first year, both the labor cost per lamp and energy cost per lamp will increase by 3 percent to account for expected inflation.

To cover the overhead costs, the accounting department charges new projects a standard fee equal to 1 percent of the project’s sales revenue each year. New projects are also charged an additional fee to cover the cost of financing the assets used to support the projects. This financing fee is equal to 12 percent of the book value of the assets employed.

Tax laws allow the $2 million worth of equipment to be fully depreciated on a straight-line basis over a five-year period if the equipment has a terminal book value of zero. A terminal or residual book value greater than zero is considered a capital gain. SMI is subject to a 40 percent tax on both earnings and capital gains.

As the firm’s financial manager, you are tasked with building an Excel spreadsheet to estimate the project’s expected cash flows and determine whether the investment is a value-creating proposition. The cost of capital that SMI uses for projects similar to the designer desk lamp project is 7.6 percent.