you are to create a spreadsheet for the Lazy Mower in EXCEL. No hard$-$wiring numbers i$.$e$.$ anything that can be calculated in EXCEL should be$.$ Please make your spreadsheets readable.

Once you have laid out the $10$ years of cash flows, please calculate the IRR and NPV using EXCEL functions as shown in class.

Full points for design, readability and of course, accuracy.

The Lazy Mower: Is It Really Worth It$?$

If there was one thing the folks at Creative Products Company $($CPC$)$knew$,$it was how to come up with useful and unique products in the midst of economic adversity. With current year revenues considerably lower and profit margins shrinking due to severe price competition, the firms' engineers had ben pushed really hard to develop a proto$-$type of a useful, and hopefully, highly profitable "unique $\text{'}\text{'}$ product. Then, last month, the design team unveiled a fully$-$ edst, proto$-$type of their latest innovation, het remote$-$controlled" lawn mowernick$-$named t h e Teh" Lazy Mower."

Surveys of retailers and customers, conducted by the marketing department, indicated that demand would be excellent, provided the prices lower than attractor. ehT testing and development took almost $3$ year and het final product passed al safety hazard tests with flying colors. After the unveiling, the product was exhibited at various home

shows nationwide and received raving reviews. Ful production had not

yet started, however, because there had been a change ni CEOs and the

new CEO was highly conservative.

Before being given the $"$go ahead" ot go into full$-$scale production of the Lazy Mower, the design team had to present a detailed feasibility study to the Capital Investment Committee, which

was chaired by the Vice President of Finance, Gary Lester. As was

typical ni a major undertaking of this type, the proposal had ot include detailed cost and revenue estimates with sufficient documentation to substantiate the numbers.

Having been involved with more than a few of these kinds of proposals before, the head of the Design team, Dave Crotte, knew that he had better take every possible factor into consideration and eb prepared

for a tough and demanding question and answer session at the next committee meeting. Luckily for Dave, his assistant, Richard Snow, who had recently earned his Chartered Financial Analyst $($CA$)$ designation, was an experienced and dependable employee. Prior to being hired by CPC three years ago, Rick had worked for another large engineering company for over $10$ years. "Richard, we have to dot al the $"$i$\text{'}$s$"$ and cross all the $"\text{'}$s$"$ on this one!" said Dave. $"$Or else, the big guys are going to tear us apart, coz we're talking major dollars here. Their main question

si going tobe, SI TI REALLY WORTH IT$?"$

So Dave and Rick began collecting the necessary information. They knew that to have a comprehensive feasibility study they would have to include the following:

$1.$ P r o F o r m a statements showing expected annual revenues, variable costs, fixed costs, and net cash flows over the economic life of the project with appropriate supporting documentation;

Break$-$Even Analysis;

$\mathrm{.}3$ Sensitivity of the cash flows to alternative scenarios of

sales growth and profit margins;

Based on the data provided by the Marketing department, they prepared Table $1,$ showing the expected unit sales of the Lazy Mower over its $10-$year economic life and the expected selling price per unit. Note that the price of $$700$ per unit was estimated ot gradually drop ot $$600$ per unit over the $10-$year period reflecting

competitive pressures. Depreciation for this project was based on the $7-$year MACRS rates as

shown in Table $2.$ The cost of equipment, including shipping, handling, and installation, was estimated at $$30$ million. It was estimated that after $10$ years, the equipment and tools could be sold for $$3$ million.

The manufacturing would be done in an unused plant of the firm. Similar plant locations could be leased for $$10,000$ per month. Fixed costs were estimated to be $$2,000,000$ per year while variable production costs per unit were expected to be $$400.$ To get the project underway, additional inventory of $$500,000$ would be required.The company would increase its accounts payable by$.$ $$700,000$ and its accounts receivable by $$1,000,000..$The weighted average cost of capital was calculated to be $18\%.$ The company's tax rate was $34\%.$

Table $1$

Projected Unit Sales and Price for Lazy Mower

Year

Unit Sales

$1$

$200,000$

$700$

$2$

$240,000$

$700$

$3$

$288,000$

$700$

$4$

$280,000$

$650$

$5$

$260,000$

$650$

$6$

$260,000$

$650$

$7$

$255,000$

$650$

$8$

$250,000$

$600$

$9$

$245,000$

$600$

$10$

$240,000$

$600$

Table $2$

Modified ACRS Depreciation Allowances

Year

$3$ year

$5$ year

$7$ year

$1$

$33\mathrm{.}33\%$

$20\mathrm{.}00\%$

$14\mathrm{.}29\%$

$2$

$44\mathrm{.}44$

$32$

$24\mathrm{.}49$

$3$

$14\mathrm{.}82$

$19\mathrm{.}2$

$17\mathrm{.}49$

$4$

$7\mathrm{.}41$

$11\mathrm{.}52$

$12\mathrm{.}49$

$5$

$11\mathrm{.}52$

$8\mathrm{.}93$

$6$

$5\mathrm{.}76$

$8\mathrm{.}93$

$7$

$8\mathrm{.}93$

$8$

$4\mathrm{.}45$