Please show excel functions used so I can follow along! Thank you! here is a better picture! Garcia and Martinez manufacture widgets and currently have

Please show excel functions used so I can follow along! Thank you! image text in transcribed

here is a better picture!

Garcia and Martinez manufacture widgets and currently have $8 million in taxable income. The company is considering an expansion, and they've asked you to evaluate the project. The expansion requires the firm to produce 75,000 widgets a year for 6 years, and the company estimates they can sell them for $30 per widget. Garcia and Martinez estimate they will need an additional $4,000,000 worth of machinery. The machinery costs $120,000 a year to operate and maintain. The machinery's depreciable life is 8-years, and the company expects to salvage the machinery for $160,000 at the end of year 6. If the project is accepted the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project's life. Similarly, the company will immediately add $50,000 to their cash balance at start-up and then again in year 1 and maintain that higher cash balance over the project's life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 14%. Calculate the project's NPV by linking to the information/variable values in Column K. ing thir nrohlom in Column las ye rone with the company's 120 0 TRE w 000 Cores Mausoldere opert TO ST De 1 D 500 000 SOLE Marga IT AN LE NO S. RE 1 DOO WE 11 SOLE New HOOF WH A G E HIRD 58.000000 ol 520000 IND 110.000 100 1000 12. 12 GODE 500 0.000 120,00 1000 od 10.00 tor 19.00 So 1.00 00.000 . Mat cap NO NIY an 10.000 57.000 30.000 1.000 100.000 10.000.000 S.O.000 S000 5.000.000 NE TETTE www www Vrsta ETA con Other Table Sold Selemad 000 per unit chinery 1. Machinery mana pepe Store in very Sorin Cash Baca Marginal Comfort HEN Marginal Tas Rate DO Change Net CepS CHER PORTAL IRR NPV Derson Tax NE OS Units sed 20 2000 Tacone S. 300 S10 $5.00 1. .. $335.000 $1.300.000 1.000.000 . SI NOR NE DE RS VE w the the werport Information/Variables $8,000,000.00 Company's Other Taxable Income 75000.00 Units Sold 6.00 Project Life (years) $30.00 Price per unit $4,000,000.00 Machinery $120,000.00 Machinery maintenance costs 8.00 Depreciable Life $160,000.00 Salvage Value $500,000.00 Increase in Inventory $50,000.00 Increase in Cash Balance $6.00 Marginal Cost (per unit) 14.00% Cost of Capital Marginal Tax Rate Periodo Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCF Change NWC Net Capital Spending CFFA PV (CFFA) Pieds Pred Period Period Red leveres Variable Costs Fixed Coupe EBITDA Depreciation EDIT Taxes 00 Change NWC Net Capital Spending OFFA INICIPAL IRR NPV Decision Units Sold 55000 60000 65000 70000 75000 80000 85000 90000 95000 $0 $50,000 $75,000 $100,000 $335,000 $10,000,000 $15,000,000 $18,333,333 Taxable income $50,000 $75,000 $100,000 $335,000 $10,000,000 $15,000,000 $18,333,333 ve $18,333,333 Tax Rate 10% 20% 30% 40% 34% 35% 38% 35% Note: Corporate taxes are now flat at 21%. I have kept a corporate tax table in this example to illustrate the XLOOKUP function. In addition, personal taxes are still based on similar tables, as are state corporate taxes. Garcia and Martinez manufacture widgets and currently have $8 million in taxable income. The company is considering an expansion, and they've asked you to evaluate the project. The expansion requires the firm to produce 75,000 widgets a year for 6 years, and the company estimates they can sell them for $30 per widget. Garcia and Martinez estimate they will need an additional $4,000,000 worth of machinery. The machinery costs $120,000 a year to operate and maintain. The machinery's depreciable life is 8-years, and the company expects to salvage the machinery for $160,000 at the end of year 6. If the project is accepted the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project's life. Similarly, the company will immediately add $50,000 to their cash balance at start-up and then again in year 1 and maintain that higher cash balance over the project's life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 14%. Calculate the project's NPV by linking to the information/variable values in Column K. ing thir nrohlom in Column las ye rone with the company's 120 0 TRE w 000 Cores Mausoldere opert TO ST De 1 D 500 000 SOLE Marga IT AN LE NO S. RE 1 DOO WE 11 SOLE New HOOF WH A G E HIRD 58.000000 ol 520000 IND 110.000 100 1000 12. 12 GODE 500 0.000 120,00 1000 od 10.00 tor 19.00 So 1.00 00.000 . Mat cap NO NIY an 10.000 57.000 30.000 1.000 100.000 10.000.000 S.O.000 S000 5.000.000 NE TETTE www www Vrsta ETA con Other Table Sold Selemad 000 per unit chinery 1. Machinery mana pepe Store in very Sorin Cash Baca Marginal Comfort HEN Marginal Tas Rate DO Change Net CepS CHER PORTAL IRR NPV Derson Tax NE OS Units sed 20 2000 Tacone S. 300 S10 $5.00 1. .. $335.000 $1.300.000 1.000.000 . SI NOR NE DE RS VE w the the werport Information/Variables $8,000,000.00 Company's Other Taxable Income 75000.00 Units Sold 6.00 Project Life (years) $30.00 Price per unit $4,000,000.00 Machinery $120,000.00 Machinery maintenance costs 8.00 Depreciable Life $160,000.00 Salvage Value $500,000.00 Increase in Inventory $50,000.00 Increase in Cash Balance $6.00 Marginal Cost (per unit) 14.00% Cost of Capital Marginal Tax Rate Periodo Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCF Change NWC Net Capital Spending CFFA PV (CFFA) Pieds Pred Period Period Red leveres Variable Costs Fixed Coupe EBITDA Depreciation EDIT Taxes 00 Change NWC Net Capital Spending OFFA INICIPAL IRR NPV Decision Units Sold 55000 60000 65000 70000 75000 80000 85000 90000 95000 $0 $50,000 $75,000 $100,000 $335,000 $10,000,000 $15,000,000 $18,333,333 Taxable income $50,000 $75,000 $100,000 $335,000 $10,000,000 $15,000,000 $18,333,333 ve $18,333,333 Tax Rate 10% 20% 30% 40% 34% 35% 38% 35% Note: Corporate taxes are now flat at 21%. I have kept a corporate tax table in this example to illustrate the XLOOKUP function. In addition, personal taxes are still based on similar tables, as are state corporate taxes