PLEASE SOLVE IN EXCEL THE SAME WAY IT IS PRESENTED HERE

PLEASE SOLVE IN EXCEL THE SAME WAY IT IS PRESENTED HERE

PLEASE SOLVE IN EXCEL THE SAME WAY IT IS PRESENTED HERE

PLEASE SOLVE IN EXCEL THE SAME WAY IT IS PRESENTED HERE

PrintMax is a midcap printer manufacturer located in Austin, Texas. The company president is Sheldon Smith. Sheldon started the company in 1990. The company's original focus was the manufacturer of specialized color laser printers for high-volume, high-quality printing. Over the years, the company became a reliable manufacture of printers that tended to satisfy midsize publishers and the like. As a recent UT graduate, you were hired by the company's finance department to assist in the valuation of various projects under consideration for investment by the company. One of the major revenue-producing items manufactured by PrintMax is a 3D printer. PrintMax currently has one 3D printer model on the market, and sales have been excellent. The 3D printer is unique in that it serves a budget conscious market despite the typical cost associated with such a product. However, 3D printers have been on the market for some time now and the technology and overall price points are improving rapidly, and the current 3D printer has limited features in comparison with newer models in its price point. PrintMax spent $900,000 to develop a prototype for a new 3D printer that has all the features of the existing 3D printer but adds new features such as a laser cutter. The company has spent an additional $280,000 for a marketing study to estimate the expected sales figures for the new printer. PrintMax can manufacture the new printers for $220 each in variable costs. Fixed costs for the operation are estimated to run $6.4 million per year. The estimated sales volume is 159,000, 168,000, 125,000, 95,000, and 70,000 per year for the next five years, respectively. The unit price of the new printer will be $499. The necessary manufacturing equipment can be purchased for $42 million. Depreciation will occur on a seven-year MACRS schedule. The value of the equipment in five years is estimated to be $6 million. As Previously stated, PrintMax currently manufactures a 3D printer. Production of the existing model is expected to be terminated in two years. If PrintMax does not introduce the new 3D printer, sales will be 95,000 units and 65,000 units for the next two years, respectively. The price of the existing 3D printer is $399 per unit, with variable costs of $150 each and fixed costs of $4.3 million per year. If Print Max does introduce the new 3D printer, sales of the existing 3D printer will fall by 30,000 units per year, and the price of the existing units will have to be lowered to $199 each. Net working capital for the 3D printers will be 20% of sales and will occur with the timing of the cash flows for the year, for example, there is no initial outlay for NWC, but changes in NWC will first occur in Year 1 with the first year's sales. PrintMax has a 21% corporate tax rate and a required return of 15%. Sheldon has asked you to prepare a report that answers the following questions: 1. What is the payback period of the project? 2. What is the profitability index of the project? 3. What is the IRR of the project? 4. What is the NPV of the project? Print Max New 3D Printer Project Analysis Input Data Equipment Salvage value R&D Marketing study Instructions Input the various requested values, which can be found in the case description posted on Canvas. This page is organized to assist your efforts and will be referenced to calculate the output values found on the next worksheet. Each cell shaded in blue requires entry. sunk cost sunk cost Year 1 Year 2 Year 3 Year 4 Year 5 14.29% 24.49% 17.49% 12.49% 8.93% Sales(units) Depreciation rate Sales of old printer Lost sales Price of new printer VC FC Price of old printer Revised price of old printer VC of old printer Tax rate NWC percentage Required return PrintMax New 3D Printer Project Analysis Valuation calculations and Results Instructions This sheet requires you to reference and use formulas to solve cach cell. No raw Input data should be typed on this page that is only cell references and formulas. The only srception is values for zeros where appropriate. Do not leave acell value blank (null). Each cel shaded in blue requires entry. Year 1 Year 2 Year 2 Yaart Old printer price reduction Sales New Lost sales Last rev. Net sales Yaar 5 VC New Lost sales Net Sales VC Fixed costs Dep EBT Tax NI +Da OCF NWC Beg End NWC CF Net CF Salvage BV of equipment Taxes Salvage CF Net CF Net CF Time 0 1 2 3 5 Value Valuation Method Payback period PI IRR NPV PrintMax is a midcap printer manufacturer located in Austin, Texas. The company president is Sheldon Smith. Sheldon started the company in 1990. The company's original focus was the manufacturer of specialized color laser printers for high-volume, high-quality printing. Over the years, the company became a reliable manufacture of printers that tended to satisfy midsize publishers and the like. As a recent UT graduate, you were hired by the company's finance department to assist in the valuation of various projects under consideration for investment by the company. One of the major revenue-producing items manufactured by PrintMax is a 3D printer. PrintMax currently has one 3D printer model on the market, and sales have been excellent. The 3D printer is unique in that it serves a budget conscious market despite the typical cost associated with such a product. However, 3D printers have been on the market for some time now and the technology and overall price points are improving rapidly, and the current 3D printer has limited features in comparison with newer models in its price point. PrintMax spent $900,000 to develop a prototype for a new 3D printer that has all the features of the existing 3D printer but adds new features such as a laser cutter. The company has spent an additional $280,000 for a marketing study to estimate the expected sales figures for the new printer. PrintMax can manufacture the new printers for $220 each in variable costs. Fixed costs for the operation are estimated to run $6.4 million per year. The estimated sales volume is 159,000, 168,000, 125,000, 95,000, and 70,000 per year for the next five years, respectively. The unit price of the new printer will be $499. The necessary manufacturing equipment can be purchased for $42 million. Depreciation will occur on a seven-year MACRS schedule. The value of the equipment in five years is estimated to be $6 million. As Previously stated, PrintMax currently manufactures a 3D printer. Production of the existing model is expected to be terminated in two years. If PrintMax does not introduce the new 3D printer, sales will be 95,000 units and 65,000 units for the next two years, respectively. The price of the existing 3D printer is $399 per unit, with variable costs of $150 each and fixed costs of $4.3 million per year. If Print Max does introduce the new 3D printer, sales of the existing 3D printer will fall by 30,000 units per year, and the price of the existing units will have to be lowered to $199 each. Net working capital for the 3D printers will be 20% of sales and will occur with the timing of the cash flows for the year, for example, there is no initial outlay for NWC, but changes in NWC will first occur in Year 1 with the first year's sales. PrintMax has a 21% corporate tax rate and a required return of 15%. Sheldon has asked you to prepare a report that answers the following questions: 1. What is the payback period of the project? 2. What is the profitability index of the project? 3. What is the IRR of the project? 4. What is the NPV of the project? Print Max New 3D Printer Project Analysis Input Data Equipment Salvage value R&D Marketing study Instructions Input the various requested values, which can be found in the case description posted on Canvas. This page is organized to assist your efforts and will be referenced to calculate the output values found on the next worksheet. Each cell shaded in blue requires entry. sunk cost sunk cost Year 1 Year 2 Year 3 Year 4 Year 5 14.29% 24.49% 17.49% 12.49% 8.93% Sales(units) Depreciation rate Sales of old printer Lost sales Price of new printer VC FC Price of old printer Revised price of old printer VC of old printer Tax rate NWC percentage Required return PrintMax New 3D Printer Project Analysis Valuation calculations and Results Instructions This sheet requires you to reference and use formulas to solve cach cell. No raw Input data should be typed on this page that is only cell references and formulas. The only srception is values for zeros where appropriate. Do not leave acell value blank (null). Each cel shaded in blue requires entry. Year 1 Year 2 Year 2 Yaart Old printer price reduction Sales New Lost sales Last rev. Net sales Yaar 5 VC New Lost sales Net Sales VC Fixed costs Dep EBT Tax NI +Da OCF NWC Beg End NWC CF Net CF Salvage BV of equipment Taxes Salvage CF Net CF Net CF Time 0 1 2 3 5 Value Valuation Method Payback period PI IRR NPV