Question

Create a spreadsheet to calculate the expected 5-year net present value.

1. • The sales price to consumers is $150

   • The retailers' markup is 50%

   • Whirlpool's after tax profit margin is 4%

   • The hurdle rate is 20%

   • Populations are as follows: Brazil 182 million, China 1.286 billion, and India 1.049 billion.

   • Family size are as follows: Brazil 4.01, China 3.70, and India 4.91.

   • The two-year increase in market penetration is expected to be 20%. To simply your analysis, you decide to calculate the overall two-year increase in sales and allocate 50% to each years 1 and 2. For years 3, 4, and 5 you expect sales to increase by 10% per year.

Answer 1

The sale price of whirlpool to retailer = 150/1.5= $100

the expected 5 year net present value .

	1	2	3	$4	$5
units	20000	20000	22000	24200	26620
unit price	100	100	100	100	100
sales revenue	$2000000	$2000000	$2200000	$2420000	$2662000
net profit margin	$80000	$80000	$88000	$96800	$106480
hurdle rate	$0.833	$0.694	$0.579	$0.482	$0.402
PV of cash flows	$66666.7	$55555.6	$50925.9	$46682.1	$42791.9
NPV	$2,62,622.2

-----------------------------------------x--------------------------------------

please give a positive vote