Start with a new Excel file and answer each question on a separate sheet within your file (2 sheets for question #2). You will have 6 sheets in your file. Please make sure you use cell references wherever appropriate (everywhere possible). As in the example Excel file and Excel video files on Moodle, list the model inputs (the information provided in the question) at the top. Once you start working on developing your model below the inputs, you should NOT be typing any numbers or answers in your cells; instead all cells should include cell references and Excel functions wherever possible. DO NOT use discount factors within your Excel file; use Excel functions whenever you need to compute a future value or present value. Perform all of your calculations directly in the Excel cells; do not calculate anything outside of Excel; Excel functions you may need to use: PV, FV, PMT, RATE, NPER, NPV, and IRR. Some questions ask for a written answer, in addition to calculating an answer in Excel; please make sure you answer all questions asked; create a box below your calculations where you type in your written answer whenever required.

Note: Always assume cash flows occur at the end of the period.

Question 1 (based on Week 5 Excel Examples Chapter 7)

Consider the two mutually exclusive projects described in the table below. (Note: Each part of the question requires a written answer.)

Assuming 9% minimum attractive rate of return (MARR), should either of the two projects be accepted? Why?

Assuming 16% MARR, should either of the two projects be accepted? Why?

For any positive value of the MARR, divide the possible MARR values into ranges with different decisions; describe and discuss what decision would be made in each range and why. You will need to calculate the crossover rate to determine the precise MARR where the decision changes. Include an NPV profile table and chart to illustrate your answer.

Year	Cash Flow Project A	Cash Flow Project B
0	-450,000	-700,000
1	200,000	200,000
2	150,000	200,000
3	100,000	200,000
4	100,000	200,000
5	75,000	200,000

Question 2 (based on Week 5 Excel Examples Chapter 10)

Estimate the weighted average cost of capital for Procter & Gamble Co. (ticker PG), using the income statement and balance sheet data for year 2021 for PG from www.morningstar.com, and using the historical stock price data for PG and VFINX from www.finance.yahoo.com. You will also need to look up the market cap for PG. (Do not use balance sheet and income statement from finance.yahoo.com, it has errors in it. Make sure you use year 2021 data from the balance sheet and income statement on Morningstar do not use the TTM column.) Note: Follow the video example on Moodle! (This question doesnt require a written answer)

Create a sheet in your Excel file (Q2a) that estimates the weighted average cost of capital, listing the

necessary inputs and calculations. Obtain any necessary data from the sources listed above. To estimate PGs cost of equity, use the Capital Asset Pricing Model, assuming 2.5% risk-free rate and 5.5% market risk premium. Calculate your own beta (see below). Link to the beta, calculated on sheet b, using a cell reference.

Create a second sheet (Q2b), where you estimate PGs beta, using historical prices with DAILY frequency for the following dates: starting date 07/01/2020, and ending date 07/01/2021 (note: input the dates 7/1 using the calendar icon, when you click done the dates showing may be 6/30, thats okay). Use finance.yahoo.com to download the prices for PG and VFINX (make sure you use the ADJUSTED CLOSE price). Use the SLOPE function to estimate beta. Insert a scatter chart that shows the trendline from regressing returns of PG on the VFINX returns, and displays the estimated equation.

Note: The best way to access the balance sheet and income statement data is the following. Go to morningstar.com, pull the company (make sure you pull the PG ticker symbol), click on Key Ratios -> Full Key Ratios Data -> Financials, where you can access the balance sheet and income statement. Note that you can collapse and un-collapse some rows by clicking on the arrows on the left side.

Question 3 (based on Week 6 Excel Examples Chapter 11)

A new machine will cost $200,000. Its maximum useful life is 8 years. The expected market value (MV) of the machine at the end of year 1 is $100,000, and it is expected to decline by $15,000 each year afterwards. The annual operating cost (AOC) is projected to be $75,000 during the first year of operation, and it is expected to rise by 15% every year afterwards. Assuming 11% minimum attractive rate of return, calculate the economic service life of the machine. Your calculations need to include a table with the following columns: Year, MV, AOC, Capital Recovery, Annual Worth (AW) of AOC, and Total AW. Illustrate your analysis with a properly labeled chart, featuring the number of years of service on the horizontal axis, and capital recovery, annual worth of the AOC, and total annual worth on the vertical axis (click to format the vertical axis and check the box Values in reverse order so that the axis displays rising cost as movement up). Please provide a written statement clearly stating the length of the economic service life of the machine.

Question 4 (based on Week 6 Excel Examples Chapter 13)

RC Sport assembles and sells low-end hockey goals, designed for youth and recreational hockey. At the moment, the company is buying all of the components required to assemble each hockey goal from an external supplier: the goal frame for $50, padding for $15, and netting for $10. The company employs part-time labor, paid $5 to assemble one goal. The annual fixed cost, consisting of insurance, property taxes, and rent is $20,000. The selling price of one goal is $125. Ignore taxes, and other possibly relevant information not given. (Note: Please include a written answer with part d.)

Calculate the contribution margin per unit.

Calculate the annual number of soccer goals that RC Sport needs to sell to break even.

Construct a table with Number of Units sold, Fixed Cost, Variable Cost, Total Cost, Total Revenue, and Profit in the columns. Fill the units sold column with the following values: 0, 100, 200, 300, 400, 500, 600, 700, 800, 900, and 1000. Fill in the remaining columns with formulas and cell references, as necessary. Insert two Excel Charts, one showing Number of Units (x) and Profit (y), the other showing Number of Units (x), Total Revenue (y1), and Total Cost (y2). Use labels in your charts and make them look good for full credit!

RC Sport is considering producing the goal frame in its existing facility, instead of buying it from an external supplier. In order to make the goal frames, it would need to buy a new machine. The machine would be expected to last 7 years, and sold for $8,000 at the end of year 7. Its annual maintenance and operating cost would be $50,000. RC would also need to buy $30 worth of material to produce each goal frame. The padding and netting would continue to be purchased from the same external supplier for the same price, and it would still cost $5 in labor cost to assemble one goal. Assuming RC Sport is able to produce and sell 10,000 hockey goals annually, what is the maximum purchase price of the machine that would make it economical for RC Sport to make the hockey goal frames internally? Use the annual worth method, assuming 15% MARR, and the Excel Goal Seek function to find the answer. Please make sure you save your Excel file after running Goal Seek, so that I can verify your file to make sure that you did use the Goal Seek function to arrive at your answer. (Do not use the trial and error, or another method.) Provide a written statement indicating the maximum economical price of the machine. Note: You will need to make up a purchase price of the machine to start with, which you can then override with the Goal Seek function.

Question 5 (based on Week 7 Excel Examples Chapter 18)

Angola Superheros, Inc. is considering launching a production of a new superhero toy. The production and sales are expected to last 4 years. The project would require a new machine, with a cost of $1,000,000. The machine is expected to be sold for $300,000 at the end of the project. The company estimates that 40,000 toys would be sold annually, with a $15 contribution margin per unit (the difference between the selling price and the variable cost per unit). In addition, the company will have to pay fixed costs equal to $35,000 each year. The minimum attractive rate of return is 13%. (Note: You will need to provide a written answer for part a, and written comments discussing the results of your analysis in part b.) (Note: Do not include the selling price or the variable cost per unit in your analysis. Also, you will need to work with the total contribution to profits for all units, and fixed costs, instead of Revenues and Costs.)

Compute the projects cash flows for years 0-4, calculate the present worth, annual worth, and the rate of return of the project, and determine whether the project should be accepted, based on the information given (disregarding any sensitivity analysis). Explain your answer.

Conduct the following sensitivity analysis, using the projects ANNUAL WORTH. Create a table where you vary the following variables, one at a time: the contribution margin per unit, the annual number of units sold, the annual fixed cost, and the machines salvage value at the end of the project. Create a table, starting with a Percent Change in its first column, with the following values: -50%, -40%, -30%, -20%, -10%, 0, 10%, 20%, 30%, 40%, and 50%. Follow with each variable and an Annual Worth column, corresponding to each of the variables you vary. You can use the Excel example file posted under Week 7 as your guide (your tables and set up will be slightly different, but similar). Vary each of the variables ranging from -50% to 50%, one at a time, while keeping all of the other variables at their baseline values, recomputing the projects annual worth each time. Insert a chart with the percent change on the horizontal axis, and the contribution margin, number of units, fixed cost, and salvage value on the vertical axis. Comment on the sensitivity of the projects annual worth to changes in each of the four variables, and the implications for accepting or rejecting the project.