Copy of Excel Assignment Oratruction - FA19 - Escal FILE Sign in S. . HOME INSERT PAGE AVOUT Calibri 11 Formet Demir BIU. . lichard font FORMULAS DATA AN == A === Autosum Copy FEW VIEW Wip Text * Merge & Center - ignment General $ % . Huther . Normal Conditional Formats Check Cell *FormangTable Ded Explanatory... Good Neutral Followed Hy... Hyperlink Calculation input File 3 Inzer Odete Format Clear Sort & Fird & Fher Select 4 ABC Marfacturing expects to sell 1,025 units of product in 2019 at an average price of $100,000 each based on cutest demand The Chief Marketing Oficer forecasts growth of 50 mits per year through 2023. So, the demand will be 1,025 units in 2019, 1.075 units e in 2020, etc. and the $100,000 price will remain consistent for all five years of the investment ife. However, ABC cannot prococe more 7 than 1000 n m ally based on current capacity Fxcel Assignment (Check Figures usine Fxcel formulas) Net Present Value of Update Option S 9,654,786 9 In order to meet dead, ABC st cither update the current plus or replace it. If the plant is replaced, an nisa working capital investment 10 of 36,000,000 is required and these fonds will be released at the end of the investment life to be used elsewhere. 1. The following table summarizes the projected data for both options Net Present Value of Replace Option $ 1,308,002 Update $125 000 000 $ 8,000,000 Replace $ 130 000 000 14 Talvement i 2019 15 Ten salvage value in 2025 10 Working capital investment required 17 Useid life 18 Total ancual cash operating costs per unit $ 5 years 61,000 6,000,000 5 years 63,000 S $ 21 The divestment will be made at the begitring of 2019 dal tractions that 22 assumed to occur on the last day of each year. ABC's required rate ofreten is 16% 25 Required: (Please refer to the two Excel video lectures posted on Blackboard for CL. 13 when setting up your spreadsheet for 26 guidance. You may set up your spreadsheet using this file (on separate Excel worksheet) to address requirement #1. 42, and 22 43 and then answer question 44 directly on the spreadsheet. There are multiple way to set up your spreadsheel and part of the exercise za is each student setting up bis her own spreadsheet. 29 1. Using Excel functions and fortukas, calculate the net prezent value (NPV) for both the update and replace alternatives. 30 2. Using Excel functions and foeminas, calculate the internal rate of return (IRR) for both the update and replace alternatives 31 3. Calculate the project profitability index (PPI) for both the update and replace alternatives 39 4 Based on the results which contion chan ABC cheese Specially explain why Sheeti Shock Sheets + REALTY Copy of Excel Assignment Oratruction - FA19 - Escal FILE Sign in S. . HOME INSERT PAGE AVOUT Calibri 11 Formet Demir BIU. . lichard font FORMULAS DATA AN == A === Autosum Copy FEW VIEW Wip Text * Merge & Center - ignment General $ % . Huther . Normal Conditional Formats Check Cell *FormangTable Ded Explanatory... Good Neutral Followed Hy... Hyperlink Calculation input File 3 Inzer Odete Format Clear Sort & Fird & Fher Select 4 ABC Marfacturing expects to sell 1,025 units of product in 2019 at an average price of $100,000 each based on cutest demand The Chief Marketing Oficer forecasts growth of 50 mits per year through 2023. So, the demand will be 1,025 units in 2019, 1.075 units e in 2020, etc. and the $100,000 price will remain consistent for all five years of the investment ife. However, ABC cannot prococe more 7 than 1000 n m ally based on current capacity Fxcel Assignment (Check Figures usine Fxcel formulas) Net Present Value of Update Option S 9,654,786 9 In order to meet dead, ABC st cither update the current plus or replace it. If the plant is replaced, an nisa working capital investment 10 of 36,000,000 is required and these fonds will be released at the end of the investment life to be used elsewhere. 1. The following table summarizes the projected data for both options Net Present Value of Replace Option $ 1,308,002 Update $125 000 000 $ 8,000,000 Replace $ 130 000 000 14 Talvement i 2019 15 Ten salvage value in 2025 10 Working capital investment required 17 Useid life 18 Total ancual cash operating costs per unit $ 5 years 61,000 6,000,000 5 years 63,000 S $ 21 The divestment will be made at the begitring of 2019 dal tractions that 22 assumed to occur on the last day of each year. ABC's required rate ofreten is 16% 25 Required: (Please refer to the two Excel video lectures posted on Blackboard for CL. 13 when setting up your spreadsheet for 26 guidance. You may set up your spreadsheet using this file (on separate Excel worksheet) to address requirement #1. 42, and 22 43 and then answer question 44 directly on the spreadsheet. There are multiple way to set up your spreadsheel and part of the exercise za is each student setting up bis her own spreadsheet. 29 1. Using Excel functions and fortukas, calculate the net prezent value (NPV) for both the update and replace alternatives. 30 2. Using Excel functions and foeminas, calculate the internal rate of return (IRR) for both the update and replace alternatives 31 3. Calculate the project profitability index (PPI) for both the update and replace alternatives 39 4 Based on the results which contion chan ABC cheese Specially explain why Sheeti Shock Sheets + REALTY