Question

The Tuff Wheels was getting ready to start its development project for a new product to be added to their small motorized vehicle line for children. The new product is called the Kiddy Dozer. It will look like a miniature bulldozer, complete with caterpillar tracks and a blade. Tuff Wheels has forecasted the demand and the cost to develop and produce the new Kiddy Dozer. The table below contains the relevant information for this project.

Development cost	$	1,100,000
Estimated development time		9	months
Pilot testing	$	200,000
Ramp-up cost	$	400,000
Marketing and support cost	$	150,000	per year
Sales and production volume		60,000	per year
Unit production cost	$	100
Unit price	$	190
Interest rate		8	%

Tuff Wheels also has provided the project plan shown below. As can be seen in the project plan, the company thinks that the product life will be three years until a new product must be created.

YEAR 1 02 03 YEAR 2 0 0 YEAR 3 0 0 YEAR 4 02 03 04 Q 04 0 0 0 04 Q PROJECT SCHEDULE KIDDY DOZER Development Pilot Testing Ramp-up Marketing and Support Production and Sales

Assume all cash flows occur at the end of each period.

a. What is the net present value (discounted at 8%) of this project? Consider all costs and expected revenues. (Enter your answer in thousands of dollars. Perform all calculations using Excel. Do not round any intermediate calculations. Round your answer to the nearest thousand.)

b. What is the impact on NPV for the Kiddy Dozer if the actual sales are 50,000 per year? 70,000 per year? (Enter your answer in thousands of dollars. Perform all calculations using Excel. Do not round any intermediate calculations. Round your answer to the nearest thousand.)

c. Based on the original sales level of 60,000, what is the effect on NPV caused by changing the discount rate to 9%, 10%, or 11%? (Enter your answer in thousands of dollars. Perform all calculations using Excel. Do not round any intermediate calculations. Round your answer to the nearest thousand.)

Answer 1

For reference of formulas i have placed the cursor at different cells so that you can refer the formula from the description box in the excel sheet.

Clipboard Font Alignment Number Styles Cells 016 =D14-D8-D9-010-011-012 TUFF WHEELS NPV 4 Part A Formula for calculating PVIE=1/1 +/100 Formula for calculating PVIFA = 1/1 + (c/100)^n Amount 6 NPV @ 8% 7 Particulars 8 Development cost 9 Pilot Testing 10 Ramp up cost 11 Marketing and support cost 12 Production PVIF/PVIFA@8% PV Remarks 1,100,000 0.943396226 1,037,736 (PVIF has been calculated for first 9 months) 200,000 0.925925926 185,185 (PVIF has been calculated for last only 1st year) 400,000 0.925925926 370,370 (PVIF has been calculated for last only 1st year) 150,000 2.577096987 386,565 (PVIFA has been calculated for three years) 6,000,000 2.577096987 15,462,582 (PVIFA has been calculated for three years) 14 Sales 11,400,000 2.57709 29,378,826 (PVIFA has been calculated for three years) 16 NPV 11,936,388 Formula for calculating PVIF = 1/1 +r/100 Formula for calculating PVIFA = 1/1 + (r/100)^n 18 Part B 19 If the sales is 50,000 20 21 Particulars 22 Development cost Amount PVIF/PVIFA @ 8% PV 1,100,000 0.943396226 200.000 0.925925926|| Remarks 1,037,736 (PVIF has been calculated for first 9 months) 185.185 liPVIF has been calculated for last only ist vear)

Paste BI U Format Painter = E E E Merge & Center % 4898 Insert Delete Format Clipboard Conditional Format as Cell Formatting Table Styles Styles Font Alignment Number C23 =1/1.08 13 11,400,000 2.57709 29,378,826 (PVIFA has been calculated for three years) 14 Sales 15 16 NPV 17 18 Part 8 19 If the sales is 50,000 11,936,388 Formula for calculating PVIF = 1/1+T/100 Formula for calculating PVIFA = 1/1+ (1/100)^n 20 21 Particulars 22 Development cost 23 Pilot Testing 24 Ramp up cost 25 Marketing and support cost 26 Production Amount PVIF/PVIFA @ 8% PV Remarks 1,100,000 0.943396226 1,037.736 (PVIF has been calculated for first 9 months) 200,000 0.925925926 185,185 (PVIF has been calculated for last only 1st year) 400,000 0.925925926 370,370 (PVIF has been calculated for last only 1st year) 150,000 2.577096987 386,565 (PVIFA has been calculated for three years) 5,000,000 2.577096987 12,885,485 (PVIFA has been calculated for three years) 28 Sales 9,500,000 2.577096987 24,482,421 (PVIFA has been calculated for three years) 30 NPV 9,617,080 NPV reduces as the sale is reduced 32 If the sales is 70,000 Formula for calculating PVIE = 1/1 +r/100 Formula for calculating PVIFA = 1/1 + (1/100) An

Alignment Number Styles Cells 639 f =1/1.08 + 1/(1.08)^2 +1/(1.08)^3 F G H I Formula for calculating PVIF = 1/1 + 7/100 Formula for calculating PVIFA = 1/1 + (1/100)n 32 If the sales is 70,000 33 34 35 Particulars Amount PVIF/PVIFA @ 8% 36 Development cost 1,100,000 0.943396226 37 Pilot Testing 200,000 0.925925926 H 38 Ramp up cost 400,000 0.925925926 39 Marketing and support cost 150,000 2.5770969871 40 Production 7,000,000 2.577096987 Remarks 1,037,736 (PVIF has been calculated for first 9 months) 185,185 (PVIF has been calculated for last only 1st year) 370,370 (PVIF has been calculated for last only 1st year) 386,565 (PVIFA has been calculated for three years) 18,039,679 (PVIFA has been calculated for three years) 42 Sales 13,300,000 2.577096987 34,275,390 (PVIFA has been calculated for three years) 43 14,255,855 NPV increased as the sale is increased 44 NPV 45 46 Part 47 Formula for calculating PVIF =1/1 +r/100 Formula for calculating PVIFA = 1/1 + (1/100)^n 48 If the sales is 60,000 49 Rate 9 % 50 Particulars 51 Development cost 52 Pilot Testing 53_Ramo uo cost Sheet1 Amount PVIF/PVIFA @ 9% PV 1,100,000 0.93676815 200,000 0.917431193 400.000 0.9174311931 Remarks 1,030,445 (PVIF has been calculated for first 9 months) 183,486 (PVIF has been calculated for last only 1st year) 366.972 (PVIE has been calculated for last only 1st vearl

=B51*051 F G H I 42 Sales 13,300,000 2.577096987 34,275,390 (PVIFA has been calculated for three years) 44 NPV 14,255,855 NPV increased as the sale is increased 46 Part 47 Formula for calculating PVIF=1/1 +T/100 Formula for calculating PVIFA = 1/1 + (1/100)^n 48 If the sales is 60,000 49 Rate 9% 50 Particulars 51 Development cost 52 Pilot Testing 53. Ramp up cost 54 Marketing and support cost 55 Production 56 57 Sales Amount PVIF/PVIFA @ 9% PV in the Remarks 1,100,000 0.93676815 1,030,445 (PVIF has been calculated for first 9 months) 200,000 0.917431193 183,486 (PVIF has been calculated for last only 1st year) 400,000 0.917431193) 366,972 (PVIF has been calculated for last only 1st year) 150,000 2.531294666 379,694 (PVIFA has been calculated for three years) 6,000,000 2.531294666 15,187,768 (PVIFA has been calculated for three years) 11,400,000 2.531294666 28,856,759 (PVIFA has been calculated for three years) 58 59 NPV 11,708,393 NPV reduces as the interest rate is increased 60 Formula for calculating PVIF=1/1 +r/100 Formula for calculating PVIFA = 1/1+(/100)^n 63 Rate 10% Sheet1

Alignment Number Cells 59 NPV 11,708,393 NPV reduces as the interest rate is increased 8 9 Formula for calculating PVIF = 1/1 +1/100 Formula for calculating PVIFA = 1/1 + (c/100)^n 63 Rate 10% 64 Particulars 65 Development cost 66 Pilot Testing 67 Ramp up cost 68 Marketing and support cost 69 Production Amount PVIF/PVIFA @ 10% PV Remarks 1,100,000 0.930232558 1,023,256 (PVIF has been calculated for first 9 months) 200,000 0.909090909 181,818 (PVIF has been calculated for last only 1st year) 400,000 0.909090909 363,636 (PVIF has been calculated for last only 1st year) 150,000 2.486851991 373,028 (PVIFA has been calculated for three years) 16,000,000 248685199114,921,112 (PVIFA has been calculated for three years) 71 Sales 11,400,000 2.486851 28,350,113 (PVIFA has been calculated for three years) 73 NPV 11,487,263 NPV reduces as the interest rate is increased Formula for calculating PVIF = 1/1 +r/100 Formula for calculating PVIFA = 1/1 +(1/100)^n 77 Rate 11% 78 Particulars 20 noualnnment cost Amount PVIF/PVIFA @ 11% PV 1,100,000 0.923787529 Remarks 1,016,166 (PVIF has been calculated for first 9 months) chirulated for last only 1st year) no

73 NPV 11,487,263 NPV reduces as the interest rate is increased Formula for calculating PVIF = 1/1 +T/100 Formula for calculating PVIFA = 1/1 + (1/100)^n 75 76 77 Rate 11% 78 Particulars 79 Development cost 80 Pilot Testing 81 Ramp up cost 82 Marketing and support cost 83 Production Amount PVIF/PVIFA @ 11% PV Remarks 1,100,000 0.923787529 1,016,166 (PVIF has been calculated for first 9 months) 200,000 0.900900901 180,180 (PVIF has been calculated for last only 1st year) 400,000 0.900900901 360,360 (PVIF has been calculated for last only 1st year) 150,000 2.443714715 366,557 |(PVIFA has been calculated for three years) 6,000,000 2.44371471514,662,288 (PVIFA has been calculated for three years) 11,400,000 2.443714715 27,858,348 (PVIFA has been calculated for three years) 85 Sales 86 87 NPV 11,272,795 NPV reduces as the interest rate is increased