Finance for Engineers:

Setting up your file: 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

Consider the two mutually exclusive projects described in the table below. (Note: Each part of the question requires a written answer.) a) Assuming 9% minimum attractive rate of return (MARR), should either of the two projects be accepted? Why? b) Assuming 16% MARR, should either of the two projects be accepted? Why? c) 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 Page 2 of 3

Question 2

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) 1. 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. 2. 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

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.