Excel help

need help with this, i dont understand it.

i have i putted some answers but they are probably wrong you may need to re do.

Read the instructions step by step and then the case study.

EXCEL HELP

read step by step instruction

and the case study

i have inputted answers but i am sure they are probably wrong so need help on that.

CLICK ON THE PICTURE TO SEE IT BETTER

otherwise it will be blurry.

i sent more pics

This will be helpfal when youranalyce the BeM asd Werst Case grenth rate scenariot. Step 1: Read the Fulli Case. On the "CepihalBadgat Werhukent Step 2: Caleulate the weighar of Fquify and Whights ef Debt for the firm. Uve the sock and bend data provided in the cave Step 3. Calculate the Cost of Equity for the fim. Use the CARM asd the Market data provided es on the Wurkithen "Macketbeta". Siep 6 : Use the resatis toum stepa 2-5 to calculane the WACC (Weighted Aveage Cost of Capital) fot the fim. Simp 7: lopet the appopriale lnitial Catb Oualay? Step 8: linpur the appropriato Cash flows from Operiations Srep 9. laput the appropesate Teminal Caih flow. Step 10 . Compule the Net Cakl flow for Yean 0.4 Step 12. Compute the NPY of the Net canh flow - This can be dose as the sum of the PVVin Seep It or esing Ercels NrV formala. Simp 13: Ladifale the AcctptReject deciale for the mon likely scentarie Step 15: Coerpute the NPV for the Om the "NriProgile" Warkhere Sip 16: Complete the table to genente a NpV prefile for the Mois Laely Scenanie. The gnoph will be autematically generated for yea. Step 17: Optional - Complete the Workaheel "Aarwer Sbeet" as nceded. (hee intenetions en the sheet) Hint Fixplain Clarify aby anrymptienc or nethod wsed. Nore the thit ahoet to convey any coatinents to the inenudec. Scep 1s. Double check your wotk Sip 19: Save with Sofutions for Moat Lhely Can and Upload year Linul Fxcel poufeci by be fue date. theryaa may with te ceedert. year, with a projected (Most-Likely seenario) 25.0% growth nate per year for subsequent years. A total investment of 5 880,000 for new equipment was required. The equipment had fixed maintenance contracts of $325,729 per year with a salvage value of $176,860 and variable costs were 12% of revenues. Balky also needed to consider both the BestCase and Worst-Case scenarios in the analysis with growth rates of 35,00% and 2.50% respectively. The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $175,150 and $22,770 of this increase would be offset with accounts payable. PSUWC currently has 917000 shares of stock outstanding at a current price of $7.00. Even though the company has outstanding stock, it is not publicly traded and therefore there is no publicly avaitable financial in formation. However, after analysis management believes that its equity beta is 1.41. The company also has 95000 bonds outstanding, with a current pnce of 900.00 . The bonds pay interest semiannually at a coupon rate of 6.90%. The bonds have a par value of $1,000 and will mature in 12 years. The average corporate tax rate was 39%. Management believes the S\&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the company's equity beta and the market risk premium based on the SRP S00 annual expected rate of retum - Balky would calculate the monthly expected market retum using 5 years of past monthly price data available in the worksheet Marketdata. This would then be multiplied by 12 to estimate the annual expected rate. Balky remembered that if the expected rate of retum for the market was too low, too high, or negative, a forward looking rate of an historical average of about 9.5% would have to be used, as the calculated value for the carrent 5 -year period may not be representative of the future. Balky would consider a E (Rm) between 812% aceeptable. Balky would calculate the market risk premium: E(Rm) - Rf from the previous calculations using the risk-free rate data available in the workshoet Marketdata. Balky noted that the risk-free rate was on an annual basis. Balky needed to calculate the rate at which the project would have to be discounted to caleulate the Net Present Value (NPV) of the proposed project based on the decision of taising capital and the current capital market environment. This discount rate, the WACC, would obviously influence the NPV and could affect the decision of whether to accept or reject the project. Thank fully, all the information needed to calculate this was available. Balky needed to clearly show all the calculations and sources for all parameter estimates used in the calculation of the WACC (and ultimately the Numerical Inputs Expected frem you are highlighted in yellow and Formulafenetinn Innuts are hiahliahtad ta hlus Note 1: Please use appropriate Cell referencing in Excel so that your numerical values update when you change any input(s). This will be helpful when you analyze the Best and Worst Case growth rate scenarios. Numerical Inputs expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue. Step 1: Read the Full Case. On the "CapitalBudget" Worksheet: Step 2: Calculate the weights of Equity and Weights of Debt for the firm. Use the stock and bond data provided in the case. Step 3: Calculate the Cost of Equity for the firm. Use the CAPM and the Market data provided on on the Worksheet "MarketData". Step 4: Calculate the Cost of Debt for the firm. Use the information provided about the firms bonds to calculate the YTM. Step 5: Calculate the after-tax cost of debt. Use the given tax rate for the firm. Step 6: Use the results from steps 2-5 to calculate the WACC (Weighted Average Cost of Capital) for the firm. Step 7: Input the appropriate Initial Cash Outlays IMPORTANT: AII cash inflows need to be POSITIVE and all cash outflows need to be NEGATIVE. Step 8: Input the appropriate Cash flows from Operations Step 9: Input the appropriate Terminal Cash fws. Step 10: Compute the Net Cash flows for Years 04. Step 11: Compute the PV of Net Cash flows for Years 0-4. (You can either use the EXCEL formula for PVO or use the mathematical formula for PV of a Step 12: Compute the NPV of the Net cash flows - This can be done as the sum of the PV's in Step 11 or using Excels NPV formula. Note 2: Excel's NPV formula needs to be adjusted by a factor of (1+1)-Refer to the module 14 notes on CANVAS for details. Step 13: Indicate the Accept/Rejeet decision for the most likely scenario. Note 3: Copy and paste the NPV values in cells C48 - C50 as you will need to input the NPV's for the 3 scenarios here - DO NOT REFERENCE val Step 14: Compute the NPV for the Best Case scenario by changing to the Best Case growth nate in cell B11 and indicate the Aceept/Reject Decision for See Note 1 above - This is where it will be helpful. Step 15: Compute the NPV for the Worst Case scenario by changing to the Worst Case growth rate in cell B11 and indicate the AcceptReject Decision On the "NPVProfile" Worksheet: Step 16: Complete the table to generate a NPV profile for the Most-Likely Scenario. The graph will be automatically generated for you. Note A: A sample calculation for generating a NPV profile is shown on the NPVProfile worksheet Step 17: Optional - Complete the Worksheet "Answer Sheet" as needed. (see instructions on the sheet) Hint: Explain/Clarify any assumptions or methods used. Note: Use this sheet to convey any comments to the instructor. Step 18: Double check your work Step 19: Save with Solutions for Most Likely Case and Upload your final Excel project by the due date. EXTREMEL Y IMPORTANT: DO NOT add any rows or columns to the existing worksheets. You can ADD worksheers for any calcularions The new Designer Eyewear was expected to sell for $105 per unit and had projected sales of 5000 units in the first year, with a projected (Most-Likely scenario) 25.0 \% growth rate per year for subsequent years. A total investment of S 880,000 for new equipment was required. The equipment had fixed maintenance contracts of $325,729 per year with a salvage value of $176,860 and variable costs were 12% of revenues. Balky also needed to consider both the BestCase and Worst-Case scenarios in the analysis with growth rates of 35.00% and 2.50% respectively. The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $175,150 and $22,770 of this increase would be offset with accounts payable. PSUWC currently has 917000 shares of stock outstanding at a current price of $78.00. Even though the company has outstanding stock, it is not publicly traded and therefore there is no publicly available financial information. However, after analysis management believes that its equity beta is 1.41 . The company also has 95000 bonds outstanding, with a current price of $900.00. The bonds pay interest semiannually at a coupon rate of 6.90%. The bonds have a par value of $1,000 and will mature in 12 years. The average corporate tax rate was 39%. Management believes the S\&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the company's equity beta and the market risk premium based on the S\&P 500 annual expected rate of retum - Balky would calculate the monthly expected market retum using 5 years of past monthly price data available in the worksheet Marketdata. This would then be multiplied by 12 to estimate the annual expected rate. Balky remembered that if the expected rate of retum for the market was too low, too high, or negative, a forward looking rate of an historical average of about 9.5% would have to be used, as the calculated value for the current 5 -year period may not be representative of the future. Balky would consider a E(Rm) between 812% acceptable. Balky would calculate the market risk premium: E(Rm) - Rf from the previous calculations using the risk-free rate data available in the worksheet Marketdata. Balky noted that the risk-free rate was on an annual basis. Balky needed to calculate the rate at which the project would have to be discounted to calculate the Net Present Value (NPV) of the proposed project based on the decision of raising capital and the current capital market environment. This discount rate, the WACC, would obviously influence the NPV and could affect the decision of whether to accept or reject the project. Thankfully, all the information needed to calculate this was available. Balky needed to clearly show all the calculations and sources for all parameter estimates used in the calculation of the WACC (and ultimately the NPV). 1. Given the following data on proposed capital budgeting project. (Numerical Inputs Expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue) 20 II. Net Investment Outhay = Initial CFs Price of Equipmeat Change in NWC \begin{tabular}{|lr|} \hline 5 & (880,000,00) \\ \hline 5 & 175,150,00 \\ \hline \end{tabular} IIT. Cash Flows from Opcrations Revenae Generation \begin{tabular}{|l|r|r|} \hline Unis Sales & & 5000 \\ \hline Unit Sale Price & 5 & 105.00 \\ \hline Revenues & 5 & 525,000.00 \\ \cline { 2 - 3 } & & \\ \hline \end{tabular} Varibble Costs Faded Cons Depreciation Firnings Before Taxes Takes \begin{tabular}{|lr|} \hline 5 & 63,000,00 \\ \hline 5 & 325,729.00 \\ \hline 5 & 348,140.00 \\ \hline 5 & 204,750.00 \\ \hline \end{tabular} Nit locome Depreciation Net eperating CFi Sstvage Value Tax en Salvage Value Retum of NWC Y. Final Casb Flow Cuk Hows Present Value of CFi NPV of Project Summarize Answers for NPV under three cases in area below \begin{tabular}{|l|c|c|c|c|} \hline & Sales Growth Rate & NPV & \multicolumn{2}{|c|}{ Accept? } \\ \hline Best Case & 35.0% & & & \\ \hline Most Likely & 25.00% & & & \\ \hline Werst Case & 2.5% & & & \\ \hline \end{tabular} Most-Likely Case - NPV Profile Most-Likely Case NPV Profile This will be helpfal when youranalyce the BeM asd Werst Case grenth rate scenariot. Step 1: Read the Fulli Case. On the "CepihalBadgat Werhukent Step 2: Caleulate the weighar of Fquify and Whights ef Debt for the firm. Uve the sock and bend data provided in the cave Step 3. Calculate the Cost of Equity for the fim. Use the CARM asd the Market data provided es on the Wurkithen "Macketbeta". Siep 6 : Use the resatis toum stepa 2-5 to calculane the WACC (Weighted Aveage Cost of Capital) fot the fim. Simp 7: lopet the appopriale lnitial Catb Oualay? Step 8: linpur the appropriato Cash flows from Operiations Srep 9. laput the appropesate Teminal Caih flow. Step 10 . Compule the Net Cakl flow for Yean 0.4 Step 12. Compute the NPY of the Net canh flow - This can be dose as the sum of the PVVin Seep It or esing Ercels NrV formala. Simp 13: Ladifale the AcctptReject deciale for the mon likely scentarie Step 15: Coerpute the NPV for the Om the "NriProgile" Warkhere Sip 16: Complete the table to genente a NpV prefile for the Mois Laely Scenanie. The gnoph will be autematically generated for yea. Step 17: Optional - Complete the Workaheel "Aarwer Sbeet" as nceded. (hee intenetions en the sheet) Hint Fixplain Clarify aby anrymptienc or nethod wsed. Nore the thit ahoet to convey any coatinents to the inenudec. Scep 1s. Double check your wotk Sip 19: Save with Sofutions for Moat Lhely Can and Upload year Linul Fxcel poufeci by be fue date. theryaa may with te ceedert. year, with a projected (Most-Likely seenario) 25.0% growth nate per year for subsequent years. A total investment of 5 880,000 for new equipment was required. The equipment had fixed maintenance contracts of $325,729 per year with a salvage value of $176,860 and variable costs were 12% of revenues. Balky also needed to consider both the BestCase and Worst-Case scenarios in the analysis with growth rates of 35,00% and 2.50% respectively. The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $175,150 and $22,770 of this increase would be offset with accounts payable. PSUWC currently has 917000 shares of stock outstanding at a current price of $7.00. Even though the company has outstanding stock, it is not publicly traded and therefore there is no publicly avaitable financial in formation. However, after analysis management believes that its equity beta is 1.41. The company also has 95000 bonds outstanding, with a current pnce of 900.00 . The bonds pay interest semiannually at a coupon rate of 6.90%. The bonds have a par value of $1,000 and will mature in 12 years. The average corporate tax rate was 39%. Management believes the S\&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the company's equity beta and the market risk premium based on the SRP S00 annual expected rate of retum - Balky would calculate the monthly expected market retum using 5 years of past monthly price data available in the worksheet Marketdata. This would then be multiplied by 12 to estimate the annual expected rate. Balky remembered that if the expected rate of retum for the market was too low, too high, or negative, a forward looking rate of an historical average of about 9.5% would have to be used, as the calculated value for the carrent 5 -year period may not be representative of the future. Balky would consider a E (Rm) between 812% aceeptable. Balky would calculate the market risk premium: E(Rm) - Rf from the previous calculations using the risk-free rate data available in the workshoet Marketdata. Balky noted that the risk-free rate was on an annual basis. Balky needed to calculate the rate at which the project would have to be discounted to caleulate the Net Present Value (NPV) of the proposed project based on the decision of taising capital and the current capital market environment. This discount rate, the WACC, would obviously influence the NPV and could affect the decision of whether to accept or reject the project. Thank fully, all the information needed to calculate this was available. Balky needed to clearly show all the calculations and sources for all parameter estimates used in the calculation of the WACC (and ultimately the Numerical Inputs Expected frem you are highlighted in yellow and Formulafenetinn Innuts are hiahliahtad ta hlus Note 1: Please use appropriate Cell referencing in Excel so that your numerical values update when you change any input(s). This will be helpful when you analyze the Best and Worst Case growth rate scenarios. Numerical Inputs expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue. Step 1: Read the Full Case. On the "CapitalBudget" Worksheet: Step 2: Calculate the weights of Equity and Weights of Debt for the firm. Use the stock and bond data provided in the case. Step 3: Calculate the Cost of Equity for the firm. Use the CAPM and the Market data provided on on the Worksheet "MarketData". Step 4: Calculate the Cost of Debt for the firm. Use the information provided about the firms bonds to calculate the YTM. Step 5: Calculate the after-tax cost of debt. Use the given tax rate for the firm. Step 6: Use the results from steps 2-5 to calculate the WACC (Weighted Average Cost of Capital) for the firm. Step 7: Input the appropriate Initial Cash Outlays IMPORTANT: AII cash inflows need to be POSITIVE and all cash outflows need to be NEGATIVE. Step 8: Input the appropriate Cash flows from Operations Step 9: Input the appropriate Terminal Cash fws. Step 10: Compute the Net Cash flows for Years 04. Step 11: Compute the PV of Net Cash flows for Years 0-4. (You can either use the EXCEL formula for PVO or use the mathematical formula for PV of a Step 12: Compute the NPV of the Net cash flows - This can be done as the sum of the PV's in Step 11 or using Excels NPV formula. Note 2: Excel's NPV formula needs to be adjusted by a factor of (1+1)-Refer to the module 14 notes on CANVAS for details. Step 13: Indicate the Accept/Rejeet decision for the most likely scenario. Note 3: Copy and paste the NPV values in cells C48 - C50 as you will need to input the NPV's for the 3 scenarios here - DO NOT REFERENCE val Step 14: Compute the NPV for the Best Case scenario by changing to the Best Case growth nate in cell B11 and indicate the Aceept/Reject Decision for See Note 1 above - This is where it will be helpful. Step 15: Compute the NPV for the Worst Case scenario by changing to the Worst Case growth rate in cell B11 and indicate the AcceptReject Decision On the "NPVProfile" Worksheet: Step 16: Complete the table to generate a NPV profile for the Most-Likely Scenario. The graph will be automatically generated for you. Note A: A sample calculation for generating a NPV profile is shown on the NPVProfile worksheet Step 17: Optional - Complete the Worksheet "Answer Sheet" as needed. (see instructions on the sheet) Hint: Explain/Clarify any assumptions or methods used. Note: Use this sheet to convey any comments to the instructor. Step 18: Double check your work Step 19: Save with Solutions for Most Likely Case and Upload your final Excel project by the due date. EXTREMEL Y IMPORTANT: DO NOT add any rows or columns to the existing worksheets. You can ADD worksheers for any calcularions The new Designer Eyewear was expected to sell for $105 per unit and had projected sales of 5000 units in the first year, with a projected (Most-Likely scenario) 25.0 \% growth rate per year for subsequent years. A total investment of S 880,000 for new equipment was required. The equipment had fixed maintenance contracts of $325,729 per year with a salvage value of $176,860 and variable costs were 12% of revenues. Balky also needed to consider both the BestCase and Worst-Case scenarios in the analysis with growth rates of 35.00% and 2.50% respectively. The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $175,150 and $22,770 of this increase would be offset with accounts payable. PSUWC currently has 917000 shares of stock outstanding at a current price of $78.00. Even though the company has outstanding stock, it is not publicly traded and therefore there is no publicly available financial information. However, after analysis management believes that its equity beta is 1.41 . The company also has 95000 bonds outstanding, with a current price of $900.00. The bonds pay interest semiannually at a coupon rate of 6.90%. The bonds have a par value of $1,000 and will mature in 12 years. The average corporate tax rate was 39%. Management believes the S\&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the company's equity beta and the market risk premium based on the S\&P 500 annual expected rate of retum - Balky would calculate the monthly expected market retum using 5 years of past monthly price data available in the worksheet Marketdata. This would then be multiplied by 12 to estimate the annual expected rate. Balky remembered that if the expected rate of retum for the market was too low, too high, or negative, a forward looking rate of an historical average of about 9.5% would have to be used, as the calculated value for the current 5 -year period may not be representative of the future. Balky would consider a E(Rm) between 812% acceptable. Balky would calculate the market risk premium: E(Rm) - Rf from the previous calculations using the risk-free rate data available in the worksheet Marketdata. Balky noted that the risk-free rate was on an annual basis. Balky needed to calculate the rate at which the project would have to be discounted to calculate the Net Present Value (NPV) of the proposed project based on the decision of raising capital and the current capital market environment. This discount rate, the WACC, would obviously influence the NPV and could affect the decision of whether to accept or reject the project. Thankfully, all the information needed to calculate this was available. Balky needed to clearly show all the calculations and sources for all parameter estimates used in the calculation of the WACC (and ultimately the NPV). 1. Given the following data on proposed capital budgeting project. (Numerical Inputs Expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue) 20 II. Net Investment Outhay = Initial CFs Price of Equipmeat Change in NWC \begin{tabular}{|lr|} \hline 5 & (880,000,00) \\ \hline 5 & 175,150,00 \\ \hline \end{tabular} IIT. Cash Flows from Opcrations Revenae Generation \begin{tabular}{|l|r|r|} \hline Unis Sales & & 5000 \\ \hline Unit Sale Price & 5 & 105.00 \\ \hline Revenues & 5 & 525,000.00 \\ \cline { 2 - 3 } & & \\ \hline \end{tabular} Varibble Costs Faded Cons Depreciation Firnings Before Taxes Takes \begin{tabular}{|lr|} \hline 5 & 63,000,00 \\ \hline 5 & 325,729.00 \\ \hline 5 & 348,140.00 \\ \hline 5 & 204,750.00 \\ \hline \end{tabular} Nit locome Depreciation Net eperating CFi Sstvage Value Tax en Salvage Value Retum of NWC Y. Final Casb Flow Cuk Hows Present Value of CFi NPV of Project Summarize Answers for NPV under three cases in area below \begin{tabular}{|l|c|c|c|c|} \hline & Sales Growth Rate & NPV & \multicolumn{2}{|c|}{ Accept? } \\ \hline Best Case & 35.0% & & & \\ \hline Most Likely & 25.00% & & & \\ \hline Werst Case & 2.5% & & & \\ \hline \end{tabular} Most-Likely Case - NPV Profile Most-Likely Case NPV Profile