please answer last pages questions

FIN 301: Penn State World Campus Final Excel project It is recommended that you copy and paste the Full Case into a Word Document for easy viewing. Ctrl-A followed by Ctrl-C will Copy the text of the case. 2 Scenario: Veronica Sarkozy, the CFO for the firm PSUWC Designer Jeans Company, LLC, woke up with a start at 4:00 am on 12/14/21, due to the phone ringing. It was the firms senior financial analyst, vacationing in Europe, calling with bad news. Veronica was supposed to present the project evaluation, at the end of the week, for the Board's proposal that they invest in new equipment which would enable them to add a new product line. Currently PSUWC has four successful products and they are considering selling a new Designer Jeans line. The staff of financial analysts had been working hard over the last few weeks collecting data and had prepared a model creating a financial forecast about the proposed project's viability Disaster had struck on the night of 12/13/21 wherein malware all but wiped out the work of the analysts. Veronica needed to prepare a financial analysis of the project to present the Board with recommendations. All the staff had already left for their annual vacation and Veronica was working alone. Veronica quickly reached the office and managed to salvage what was left of the excel spreadsheet prepared for the presentation. What follows is some basic information that Veronica know and was able to retrieve about the project. PSUWC's existing plant has excess capacity, in a fully depreciated building, to install and run the new equipment to produce the new Designer Jeans line. Due to relatively rapid advances in technology, the project was expected to be discontinued in four years The new Designer Jeuns was expected to sell for S 105 per unit and had projected sales of 4900 units in the first yetur, with a projected (Most-Likely scenario) 19.0 % growth rate per year for subsequent years. A total investment of $ 833,000 for new equipment was required. The equipment had fixed maintenance contracts of $ 317,070 per year with a salvage value of 176,693 and variable costs Were 8 % of revenues. Veronica also needed to consider both the Best-Case and Worst-Case scenarios in the analysis with growth rates of 29.00% and 1.90 % respectively. 3 The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in I working capital of 146,780 and $ 22,017 of this increase would be offset with accounts payable. PSUWC currently has 937000 shares of stock outstanding at a current price of $ 67.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 manngement believes that its equity beta is 1.64. 4 B TL years The new Designer Jeans was expected to sell for $ 105 per unit and had projected sales of 4900 units in the first year, with a projected (Most-Likely scenario) 19.0% growth rate per year for subsequent years. A total investment of S 833,000 for new equipment was required. The equipment had fixed maintenance contracts of S 317,070 per year with a salvage value of $ 176,693 and variable costs were 8 % of revenues. Veronica also needed to consider both the Best-Case and Worst-Case scenarios in the analysis with growth rates of 29.00 % and 1.90 % respectively. 3 The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of S 146,780 and $ 22,017 of this increuse would be offset with accounts payable. PSUWC currently has 937000 shares of stock outstanding at a current price of $ 67.00. Even though the company has outstanding stock, it is not publicly traded and therefore there is no publicly available financinl information. However, after analysis management believes that its equity beta is 1.64. 36% The company also has 115000 bonds outstanding, with a current price of $ 1,051.00. The bonds pay interest semi-annually at a coupon rate of 6,30 %. The bonds have a par value of $1,000 and will mature in 14 years. The average corporate tax rate was 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 return - Veronica would calculate the monthly expected market return using 5 years of past monthly price data available in the worksheet Marketdata. This would then be multiplied by 12 to estimato the annual expected rate. Veronica remembered that if the expected rate of return 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. Veronica would consider a E(Rm) between 8-12% acceptable. Veronica would calculate the market risk premium: E(Rm) - Rf from the previous calculations using the risk-free rate data available in the worksheet Marketdata, Veronica noted that the risk-free rute was on an annual basis. Veronica 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 tho project. Thankfully, all the information needed to calculate this was available. Veronica needed to clearly show all the calculations and sources for all paraneter estimates used in the calculation of the WACC (and ultimately the NPV). Gathering all the available information, Veronica got a large cup of extra strong coffee and sat down to work on the development of the Capital Budgeting project model. The correct recommendation to the board was critical to the future growth of the firm! Veronicn appreciated the detailed step by step instructions on the Worksheet 0.Case Instructions - Luckily they were still 5 available!! Company B Risk Free Rate (R 1.64 2.30% Returns 1 S&P 500 Closing Data 2 Date Adjusted Close 3 12/1/2021 7101.80 4 11/1/2021 7054.31 5 10/1/2021 7032.17 6 9/1/2021 7067.68 7 8/1/2021 7051.78 8 7/1/2021 7063.94 9 6/1/2021 6984.32 10 5/1/2021 7028.14 11 4/1/2021 6970.95 12 3/1/2021 6930.53 13 2/1/2021 6848,51 14 1/1/2021 6754.79 15 12/1/2020 6753.04 16 11/1/2020 6696.64 17 10/1/2020 6544.91 18 9/1/2020 6412.52 19 8/1/2020 6410,65 20 7/1/2020 6320.31 21 6/1/2020 6338.06 22 5/1/2020 6203.50 23 4/1/2020 6143.76 24 3/1/2020 5962.61 25 2/1/2020 5828.44 25 1/1/2020 5665.45 27 12/1/2019 5619.74 28 11/1/2019 5520.17 29 10/1/2019 5461.98 30 9/1/2019 5448.93 31 8/1/2019 S412.71 32 7/1/2019 5254.98 33 6/1/2019 5216,46 34 5/1/2019 5144.32 35 4/1/2019 5122.67 36 3/1/2019 5068.13 37 2/1/2019 5070.69 38 1/1/2019 4997.44 39 12/1/2018 4946,82 40 11/1/2018 4969.42 41 10/1/2018 5014, 42 9/1/2018 4910.26 43 8/1/2018 4908.89 0.Case Instructions FullCase MarketData Grading Rubric Answer Sheet CapitalBudant NE A D 20 27 28 29 30 33 32 33 34 35 36 37 38 39 40 41 42 63 54 45 66 07 12/1/2019 11/1/2019 10/1/2019 9/1/2019 8/1/2019 7/1/2019 6/1/2019 5/1/2019 4/1/2019 3/1/2019 2/1/2019 1/1/2019 12/1/2018 11/1/2018 10/1/2018 9/1/2018 8/1/2018 7/1/2018 6/1/2018 5/1/2018 4/1/2018 3/1/2018 2/1/2018 1/1/2018 12/1/2012 11/1/2017 10/1/2017 9/1/2017 8/1/2017 7/11/2017 6/1/2017 3/1/2017 4/1/2017 3/11/2017 2/1/2017 1/1/2017 12/1/2016 30033 5619.74 5520.17 5461.98 5448.93 5412.21 5254.98 $216.46 5144.32 $122.67 5068.13 5070.69 4997.44 4946.82 4969.42 5014.81 4910.26 4908.89 4833.89 4719.92 4651.79 4654.79 4581.17 4574.09 4461.78 4354.05 4324.18 4315.29 4346.83 425483 4184.48 4135.72 1 4048.20 4043.17 4005.01 3922:41 3904.16 3857.00 49 50 51 52 52 54 55 56 57 58 59 60 61 62 63 64 65 56 57 Expected Return Sid. Dav B Weights of Debt and Equity, Wd & We Cost of Debt Calculation 6 Beta for the Firm 7 Expected Market Return 8 Risk-Free Rate 9 WACC 10 WACC Calculation 11 Initial Cash Flows 12 Operating CFS 13 Terminal CFS 14 Final Decision: Accept Reject 15 Comments 16 FIN 301: Penn State World Campus Final Excel project It is recommended that you copy and paste the Full Case into a Word Document for easy viewing. Ctrl-A followed by Ctrl-C will Copy the text of the case. 2 Scenario: Veronica Sarkozy, the CFO for the firm PSUWC Designer Jeans Company, LLC, woke up with a start at 4:00 am on 12/14/21, due to the phone ringing. It was the firms senior financial analyst, vacationing in Europe, calling with bad news. Veronica was supposed to present the project evaluation, at the end of the week, for the Board's proposal that they invest in new equipment which would enable them to add a new product line. Currently PSUWC has four successful products and they are considering selling a new Designer Jeans line. The staff of financial analysts had been working hard over the last few weeks collecting data and had prepared a model creating a financial forecast about the proposed project's viability Disaster had struck on the night of 12/13/21 wherein malware all but wiped out the work of the analysts. Veronica needed to prepare a financial analysis of the project to present the Board with recommendations. All the staff had already left for their annual vacation and Veronica was working alone. Veronica quickly reached the office and managed to salvage what was left of the excel spreadsheet prepared for the presentation. What follows is some basic information that Veronica know and was able to retrieve about the project. PSUWC's existing plant has excess capacity, in a fully depreciated building, to install and run the new equipment to produce the new Designer Jeans line. Due to relatively rapid advances in technology, the project was expected to be discontinued in four years The new Designer Jeuns was expected to sell for S 105 per unit and had projected sales of 4900 units in the first yetur, with a projected (Most-Likely scenario) 19.0 % growth rate per year for subsequent years. A total investment of $ 833,000 for new equipment was required. The equipment had fixed maintenance contracts of $ 317,070 per year with a salvage value of 176,693 and variable costs Were 8 % of revenues. Veronica also needed to consider both the Best-Case and Worst-Case scenarios in the analysis with growth rates of 29.00% and 1.90 % respectively. 3 The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in I working capital of 146,780 and $ 22,017 of this increase would be offset with accounts payable. PSUWC currently has 937000 shares of stock outstanding at a current price of $ 67.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 manngement believes that its equity beta is 1.64. 4 B TL years The new Designer Jeans was expected to sell for $ 105 per unit and had projected sales of 4900 units in the first year, with a projected (Most-Likely scenario) 19.0% growth rate per year for subsequent years. A total investment of S 833,000 for new equipment was required. The equipment had fixed maintenance contracts of S 317,070 per year with a salvage value of $ 176,693 and variable costs were 8 % of revenues. Veronica also needed to consider both the Best-Case and Worst-Case scenarios in the analysis with growth rates of 29.00 % and 1.90 % respectively. 3 The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of S 146,780 and $ 22,017 of this increuse would be offset with accounts payable. PSUWC currently has 937000 shares of stock outstanding at a current price of $ 67.00. Even though the company has outstanding stock, it is not publicly traded and therefore there is no publicly available financinl information. However, after analysis management believes that its equity beta is 1.64. 36% The company also has 115000 bonds outstanding, with a current price of $ 1,051.00. The bonds pay interest semi-annually at a coupon rate of 6,30 %. The bonds have a par value of $1,000 and will mature in 14 years. The average corporate tax rate was 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 return - Veronica would calculate the monthly expected market return using 5 years of past monthly price data available in the worksheet Marketdata. This would then be multiplied by 12 to estimato the annual expected rate. Veronica remembered that if the expected rate of return 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. Veronica would consider a E(Rm) between 8-12% acceptable. Veronica would calculate the market risk premium: E(Rm) - Rf from the previous calculations using the risk-free rate data available in the worksheet Marketdata, Veronica noted that the risk-free rute was on an annual basis. Veronica 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 tho project. Thankfully, all the information needed to calculate this was available. Veronica needed to clearly show all the calculations and sources for all paraneter estimates used in the calculation of the WACC (and ultimately the NPV). Gathering all the available information, Veronica got a large cup of extra strong coffee and sat down to work on the development of the Capital Budgeting project model. The correct recommendation to the board was critical to the future growth of the firm! Veronicn appreciated the detailed step by step instructions on the Worksheet 0.Case Instructions - Luckily they were still 5 available!! Company B Risk Free Rate (R 1.64 2.30% Returns 1 S&P 500 Closing Data 2 Date Adjusted Close 3 12/1/2021 7101.80 4 11/1/2021 7054.31 5 10/1/2021 7032.17 6 9/1/2021 7067.68 7 8/1/2021 7051.78 8 7/1/2021 7063.94 9 6/1/2021 6984.32 10 5/1/2021 7028.14 11 4/1/2021 6970.95 12 3/1/2021 6930.53 13 2/1/2021 6848,51 14 1/1/2021 6754.79 15 12/1/2020 6753.04 16 11/1/2020 6696.64 17 10/1/2020 6544.91 18 9/1/2020 6412.52 19 8/1/2020 6410,65 20 7/1/2020 6320.31 21 6/1/2020 6338.06 22 5/1/2020 6203.50 23 4/1/2020 6143.76 24 3/1/2020 5962.61 25 2/1/2020 5828.44 25 1/1/2020 5665.45 27 12/1/2019 5619.74 28 11/1/2019 5520.17 29 10/1/2019 5461.98 30 9/1/2019 5448.93 31 8/1/2019 S412.71 32 7/1/2019 5254.98 33 6/1/2019 5216,46 34 5/1/2019 5144.32 35 4/1/2019 5122.67 36 3/1/2019 5068.13 37 2/1/2019 5070.69 38 1/1/2019 4997.44 39 12/1/2018 4946,82 40 11/1/2018 4969.42 41 10/1/2018 5014, 42 9/1/2018 4910.26 43 8/1/2018 4908.89 0.Case Instructions FullCase MarketData Grading Rubric Answer Sheet CapitalBudant NE A D 20 27 28 29 30 33 32 33 34 35 36 37 38 39 40 41 42 63 54 45 66 07 12/1/2019 11/1/2019 10/1/2019 9/1/2019 8/1/2019 7/1/2019 6/1/2019 5/1/2019 4/1/2019 3/1/2019 2/1/2019 1/1/2019 12/1/2018 11/1/2018 10/1/2018 9/1/2018 8/1/2018 7/1/2018 6/1/2018 5/1/2018 4/1/2018 3/1/2018 2/1/2018 1/1/2018 12/1/2012 11/1/2017 10/1/2017 9/1/2017 8/1/2017 7/11/2017 6/1/2017 3/1/2017 4/1/2017 3/11/2017 2/1/2017 1/1/2017 12/1/2016 30033 5619.74 5520.17 5461.98 5448.93 5412.21 5254.98 $216.46 5144.32 $122.67 5068.13 5070.69 4997.44 4946.82 4969.42 5014.81 4910.26 4908.89 4833.89 4719.92 4651.79 4654.79 4581.17 4574.09 4461.78 4354.05 4324.18 4315.29 4346.83 425483 4184.48 4135.72 1 4048.20 4043.17 4005.01 3922:41 3904.16 3857.00 49 50 51 52 52 54 55 56 57 58 59 60 61 62 63 64 65 56 57 Expected Return Sid. Dav B Weights of Debt and Equity, Wd & We Cost of Debt Calculation 6 Beta for the Firm 7 Expected Market Return 8 Risk-Free Rate 9 WACC 10 WACC Calculation 11 Initial Cash Flows 12 Operating CFS 13 Terminal CFS 14 Final Decision: Accept Reject 15 Comments 16