Question

A manufacturer is planning to produce and sell a new product. It would cost $20 million at Year 0 to buy the equipment necessary to manufacture the product. The project would require net working capital at the beginning of each year in an amount equal to 15% of the year's projected sales; for example, NWC₀ = 15%(Sales₁). The product would sell for $30 per unit, and believes that variable costs would amount to $15 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The project's fixed costs would be $500,000/year in Year 1 and would increase with inflation.

The products will be sold for 4 years. If the project is undertaken, it must be continued for the entire 4 years. The firm believes it could sell 500,000 units per year

     The equipment would be depreciated over using straight-line depreciation. The estimated market value of the equipment at the end of the project’s 4-year life is $500,000. The federal-plus-state tax rate is 40%. Its cost of capital is 10%.

Do parts a-e in Excel with separate tabs for each part.

   a.) Develop a spreadsheet model, and use it to find the project’s NPV, IRR, and payback. .

2. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables’ values at least 5%, 10%, and 20% above and below their base-case values. Include a graph in your analysis. To which variable does NPV appear most sensitive?   (Suggestions: Use Excel’s Data Table feature, or re-calculate the NPV of each input level and then copy and paste the results).                                                                                                                       
3. Now conduct a scenario analysis. Assume that there is a 25% probability that best-case conditions, with each of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 25% probability of worst-case conditions, with the variables 20% worse than base, and a 50% probability of base-case conditions.     (Suppose the average CV of this company's projects is 2.0. Is this project more or less risky than the average project for this company?).   
4. Set up your own numerical example of a real option. You can choose either a timing or an abandonment option. Use any probabilities and cash flows that make sense.

Answer 1

	NPV Method
a.	Computation of Cash Inflow per annum
	Year	Sales	Less:Working Capital	Less:Variable Cost	Less: Depreciation	Less:Fixed Costs	Profit	Tax	Profit after Tax	Add:Depreciation	Cash Inflows	Discouning Factor@10%	PV
	1	1,50,00,000.00	22,50,000.00	75,00,000.00	48,75,000.00	5,00,000.00	-1,25,000.00	-	-1,25,000.00	48,75,000.00	47,50,000.00	0.909	43,17,750.00
	2	1,54,50,000.00	23,17,500.00	77,25,000.00	48,75,000.00	5,00,000.00	32,500.00	13,000.00	19,500.00	48,75,000.00	48,94,500.00	0.826	40,42,857.00
	3	1,59,13,500.00	23,87,025.00	79,56,750.00	48,75,000.00	5,00,000.00	1,94,725.00	77,890.00	1,16,835.00	48,75,000.00	49,91,835.00	0.751	37,48,868.09
	4	1,63,90,905.00	24,58,635.75	81,95,452.50	48,75,000.00	5,00,000.00	3,61,816.75	1,44,726.70	2,17,090.05	48,75,000.00	50,92,090.05	0.683	34,77,897.50
	5	1,68,82,632.15	25,32,394.82	84,41,316.08	48,75,000.00	5,00,000.00	5,33,921.25	2,13,568.50	3,20,352.75	48,75,000.00	51,95,352.75	Total PV	1,55,87,372.59
		The fifth year information is for payback period computation only,not for NPV computation							Less:Initial Investment	2,00,00,000.00
												Add:Salvage Value of Initial Investment(Yr4,10%)	3,41,500.00
												NPV(4,10%)	-40,71,127.41
	IRR Method
	For finding IRR,we assume discount rate to be 7%	Since the Discounting rate 7% is much away from 0 we choose the Discounting rate to be 1%	Even at 1% the NPV is negative
	We will start from Cash Inflows of above table again							On subsituiting in formula:-
	Cash Inflows	DF @ 7%	PV		Cash Inflows	DF @ 1%	PV			LR+ (NPV at LR/NPV at LR-NPV at HR)*(HR-LR)
	47,50,000	0.935	44,41,250.00		47,50,000.00	0.990	47,02,500.00
	48,94,500	0.873	42,72,898.50		48,94,500.00	0.980	47,96,610.00			1%+(-279819.68/-279819.68+2945749.43)*(7-1)
	49,91,835	0.816	40,73,337.36		49,91,835.00	0.971	48,47,071.79
	50,92,090	0.763	38,85,264.71		50,92,090.05	0.961	48,93,498.54		IRR	0.37%
		Total PV	1,66,72,750.57			Total PV	1,92,39,680.32
		Less:Initial Investment	2,00,00,000.00			Less: Initial Investment	2,00,00,000.00
		Add:Salvage Value of Initial Investment(Yr4,7%)	3,81,500.00			Add:Salvage Value of Initial Investment(Yr4,1%)	4,80,500.00
		NPV(4,7%)	-29,45,749.43			NPV(4,1%)	-2,79,819.68
	Payback period(Simple,not discounted rate since not mentioned)
	Year	Cash Inflow	Cumulative Cash Inflow
	1	47,50,000.00	47,50,000.00
	2	48,94,500.00	96,44,500.00									ekkarill92 Next > < Previous Related Solutions A manufacturer is planning to produce and sell a new product. It would cost $20 million... A manufacturer is planning to produce and sell a new product. It would cost $20 million at Year 0 to buy the equipment necessary to manufacture the product. The project would require net working capital at the beginning of each year in an amount equal to 15% of the year's projected sales; for example, NWC0 = 15%(Sales1). The product would sell for $30 per unit, and believes that variable costs would amount to $15 per unit. After Year 1, the... You sell chairs; they cost your $20 each to produce and sell. In your world there... You sell chairs; they cost your $20 each to produce and sell. In your world there are 7 potential buyers for your chairs, listed below: (Each person will buy a maximum of 1 chair each.) Buyer                          Reservation Price Craig                           $50 Bill                              $40 John                            $30 Hank                           $25 Sally                            $19.95 Mophead                     $15 Scaredycat                  $10 Calculate the profit-maximizing quantity of chairs and total profits if you use 1st degree price discrimination. (You are a psychic.) b) Calculate the profit-maximizing quantity of chairs and... Talbot Industries is considering launching a new product. The new manufacturing equipment will cost $20 million,... Talbot Industries is considering launching a new product. The new manufacturing equipment will cost $20 million, and production and sales will require an initial $5 million investment in net operating working capital. The company's tax rate is 40%. What is the initial investment outlay? Write out your answer completely. For example, 2 million should be entered as 2,000,000. $___ The company spent and expensed $150,000 on research related to the new project last year. Would this change your answer? -Select-... A manufacturer has a fixed cost of $120 million to produce x million of computer chips... A manufacturer has a fixed cost of $120 million to produce x million of computer chips and estimates its variable cost as $160 for x million compute chips. The manufacturer sells each chip for 165% more than its average cost per chip. a. What is the manufacturer's average cost per x million computer chip? b. What is the manufacturer's price x million computer chips? c. What is the manufacturer's break-even quantity? 1. Winters Corp. is considering a new product that would require an investment of $20 million... 1. Winters Corp. is considering a new product that would require an investment of $20 million now, at t = 0. If the new product is well received, then the project would produce after-tax cash flows of $10 million at the end of each of the next 3 years (t = 1, 2, 3), but if the market did not like the product, then the cash flows would be only $4 million per year. There is a 50% probability that... A cereal manufacturer is planning to develop a new product. The product does not require adding... A cereal manufacturer is planning to develop a new product. The product does not require adding milk because each piece has milk substitute inside of it. All the consumer needs to do is add water. The product is scheduled to be rolled out nationally next year. The marketing manager has to decide how to market the new product. There are three different marketing strategies that the firm is looking at. They want to emphasize all three points, but they also... Cortez Company is planning to introduce a new product that will sell for $106 per unit.... Cortez Company is planning to introduce a new product that will sell for $106 per unit. The following manufacturing cost estimates have been made on 20,000 units to be produced the first year: Direct materials $ 800,000 Direct labor 480,000 (= $16 per hour × 30,000 hours) Manufacturing overhead costs have not yet been estimated for the new product, but monthly data on total production and overhead costs for the past 24 months have been analyzed using simple linear regression.... A toy manufacturer expects to sell 4536 games in a year. The games cost $20 each... A toy manufacturer expects to sell 4536 games in a year. The games cost $20 each plus a fixed delivery cost of $140 per order. If it cost $5 to store a game for a year, find the order size and the number of orders that minimizes inventory costs.   A proposal to build a $187 million factory is being contemplated to produce a new product.... A proposal to build a $187 million factory is being contemplated to produce a new product. The factory is expected to last 20 years (but can be depreciated immediately according to current accounting rules). The factory is expected to produce 8000 units per year that are expected to be sold for a price of $26,000 each. Variable costs (production labor, raw materials, marketing, distribtuion, etc.) are expected to be $17,000 per unit. Fixed costs (administration, maintenance, repairs, utliities, insurance, real... A proposal to build a $187 million factory is being contemplated to produce a new product.... A proposal to build a $187 million factory is being contemplated to produce a new product. The factory is expected to last 20 years (but can be depreciated immediately according to current accounting rules). The factory is expected to produce 8000 units per year that are expected to be sold for a price of $26,000 each. Variable costs (production labor, raw materials, marketing, distribtuion, etc.) are expected to be $17,000 per unit. Fixed costs (administration, maintenance, repairs, utliities, insurance, real... ADVERTISEMENT Subjects Accounting Advanced Math Anatomy and Physiology Biology Chemistry Civil Engineering Computer Science Economics Electrical Engineering Finance History Math Mechanical Engineering Operations Management Physics Psychology Statistics and Probability Nursing Other ADVERTISEMENT Latest Questions Identify two cultures and analyze the nurturing process within each culture and discuss the impact the... Digital security is an increasing concern is the Internet age. In order to protect sensitive information... the challenges experienced by companies considering full application of customer retention strategy in the face of... Respond to the following in a minimum of 175 words: The flow of a program is... Write a version of the binary search algorithm that can be used to search a string... Using the following reduction potentials: I2(s) + 2e- <---> 2I-(aq) E0 = 0.535 V I2 (aq)... Why are learnability and critical thinking crucial skills for the 21stcentury? And, how can you improve... ADVERTISEMENT About @ 2020 WizEdu