Homework answers / question archive / 1)Was Precision Castparts overpay/underpay based on the enterprise and market values implied by the median multiples given in Exhibit 11 – Line 8 Overpay Underpay Company Name MV Equity Enterprise Value Book Value CY '14 EBITDA EBIT Enterprise Value as Multiple of: Revenue EBITDA EBIT MV of Equity as Multiple of: Net Income Book Value Rev Net Income Alcoa $10,599 0

1)Was Precision Castparts overpay/underpay based on the enterprise and market values implied by the median multiples given in Exhibit 11 – Line 8 Overpay Underpay Company Name MV Equity Enterprise Value Book Value CY '14 EBITDA EBIT Enterprise Value as Multiple of: Revenue EBITDA EBIT MV of Equity as Multiple of: Net Income Book Value Rev Net Income Alcoa $10,599 0

Finance

Share With

1)Was Precision Castparts overpay/underpay based on the enterprise and market values implied by the median multiples given in Exhibit 11 – Line 8

1. Overpay
2. Underpay

Company Name MV Equity Enterprise Value Book Value CY '14 EBITDA EBIT Enterprise Value as Multiple of: Revenue EBITDA EBIT MV of Equity as Multiple of: Net Income Book Value Rev Net Income Alcoa $10,599 0.97x 6.51x 6.68x 1.29x LISI $709 1.16x $13,637 $1,332 $9,460 $1,804 $1,627 $23,164 $1,517 $12,924 $3,193 $2,220 ThyssenKrupp Allegheny Technologies Carpenter Technology $23,906 $1,307 $41,304 $4,223 $2,173 $2,043 $81 $210 $3,556 $193 $2,290 $283 $382 $3,182 $2,598 $1,326 $2,185 $131 $1,314 $106 $212 16.36x 45.05x 7.86x 5.64x 11.28x 0.31x 0.76x 10.60x 11.56x 9.84x 30.09x 10.47x 1.88x 2.97x 0.69x ($3) NM $133 1.02x 5.81x 12.25x 1.23x Median 0.97x 6.51x 14.31x 1.29x $1,804 $5,572 $3,193 $8,603 $2,598 $3,683 $4,223 $14,583 $382 $1,341 $212 $790 $133 $493 10.60x 14.51x Mean 0.84x 7.42x 20.08x 1.61x $10,929 $10,005 $2,927 $2,602 $1,530 Precision Castparts Implied Value - Median? Implied Value - Mean? $9,705 $8,404 $19,055 $21,718 $27,581 $37,755 $21,894 $30,722 $14,098 $17,596 The calculation of the implied values for PCP based on the median of the peer firms' multiples takes the product of the median value of the multiples of comparable firms (line 8) and multiplies it times the relevant base (revenue, EBITDA, EBIT, net income, or book value) for PCP. The same method is used for the calculation of the implied value based on the average or mean of the peer firms' multiples (line 9). For instance, the implied value based on the median multiple of EBIT ($37,755 million) is derived by multiplying 14.51 (the mean EBIT multiple for the comparable firms) times $2,602 million (the EBIT of PCP).

2)

1. Open the SierraPacific-02.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Set range names for the workbook.
   1. Select the Student Loan sheet, and select cells B5:C8.
   2. Click the Create from Selection button [Formulas tab, Defined Names group].
   3. Verify that the Left column box in the Create Names from Selection dialog box is selected.
   4. Deselect the Top row box if it is checked and click OK.

      Figure 2-90-Mac Define Name dialog box

   5. Select cells E5:F7. Repeat steps a−d to create range names.
   6. Click the Define Name button [Formulas tab, Defined Names group] to view the names in the Define Name dialog box (Figure 2-90-Mac). Notice that the cell references are absolute.
   7. Click Close.
3. Enter a PMT function.
   1. Select C8.
   2. Click the Financial button [Formulas tab, Function Library group] and select PMT.
   3. Click the Rate box and click cell C7. The range name Rate is substituted and is an absolute reference.
   4. Type /12 immediately after Rate to divide by 12 for monthly payments.
   5. Click the Nper box and click cell C6. The substituted range name is Loan_Term.
   6. Type *12 after Loan_Term to multiply by 12.
   7. Click the Pv box and type a minus sign (-) to set the argument as a negative amount.
   8. Click cell C5 (Loan_Amount) for the pv argument. A negative loan amount reflects the lender’s perspective, since the money is paid out now (Figure 2-91-Mac).

      Figure 2-91-Mac Pv argument is negative in the PMT function

   9. Leave the Fv and Type boxes empty.
   10. Click Done. The payment for a loan at this rate is $186.43, shown as a positive value.
   11. Verify or format cell C8 as Accounting Number Format to match cell C5.
4. Create a total interest formula.
   1. Click cell F5 (Total_Interest). This value is calculated by multiplying the monthly payment by the total number of payments to determine total outlay. From this amount, you subtract the loan amount.
   2. Type = and click cell C8 (the Payment).
   3. Type * to multiply and click cell C6 (Loan_Term).
   4. Type *12 to multiply by 12 for monthly payments. Values typed in a formula are constants and are absolute references.
   5. Type – immediately after *12 to subtract.
   6. Click cell C5 (the Loan_Amount). The formula is Payment * Loan_Term * 12 – Loan_Amount. Parentheses are not required, because the multiplications are done from left to right, followed by the subtraction (Figure 2-92).

      Figure 2-92 Left-to-right operations

   7. Press Enter. The result is $1,185.81.
5. Create the total principal formula and the total loan cost.
   1. Select cell F6 (Total_Principal). This value is calculated by multiplying the monthly payment by the total number of payments. From this amount, subtract the total interest.
   2. Type = and click cell C8 (the Payment).
   3. Type * to multiply and click cell C6 (Loan_Term).
   4. Type *12 to multiply by 12 for monthly payments.
   5. Type – immediately after *12 to subtract.
   6. Click cell F5 (the Total_Interest). The formula is Payment * Loan_Term * 12 – Total_Interest.
   7. Press Enter. Total principal is the amount of the loan.
   8. Click cell F7, the Total_Cost of the loan. This is the total principal plus the total interest.
   9. Type =, click cell F5, type +, click cell F6, and then press Enter.
6. Set order of mathematical operations to build an amortization schedule.
   1. Click cell B13. The beginning balance is the loan amount.
   2. Type =, click cell C5, and press Enter.
   3. Format the value as Accounting Number Format.
   4. Select cell C13. The interest for each payment is calculated by multiplying the balance in column B by the rate divided by 12.
   5. Type = and click cell B13.
   6. Type *( and click cell C7.
   7. Type /12). Parentheses are necessary so that the division is done first (Figure 2-93).
   8. Press Enter and format the results (37.5) as Accounting Number Format.

      Figure 2-93 The interest formula

   9. Select cell D13. The portion of the payment that is applied to the principal is calculated by subtracting the interest portion from the payment.
   10. Type =, click cell C8 (the Payment).
   11. Type -, click cell C13, and press Enter. From the first month’s payment, $148.93 is applied to the principal and $37.50 is interest.
   12. Click cell E13. The total payment is the interest portion plus the principal portion.
   13. Type =, click cell C13, type +, click cell D13, and then press Enter. The value matches the amount in cell C8.
   14. Select cell F13. The ending balance is the beginning balance minus the principal payment. The interest is part of the cost of the loan.
   15. Type =, click cell B13, type -, click cell D13, and then press Enter. The ending balance is $9,851.07.

       The table shows the formulas in row 13 after you complete Step 6.

       Formulas in cells B13:F13
       B13	=Loan_Amount
       C13	=B13*(Rate/12)
       D13	=Payment-C13
       E13	=C13+D13
       F13	=B13-D13

7. Fill data and copy formulas.
   1. Select cells A13:A14. This is a series with an increment of 1.
   2. Drag the Fill pointer to reach cell A72. This sets 60 payments for a five-year loan term.
   3. Select cell B14. The beginning balance for the second payment is the ending balance for the first payment.
   4. Type =, click cell F13, and press Enter.
   5. Double-click the Fill pointer for cell B14 to fill the formula down to row 72. The results are zero (displayed as a hyphen in Accounting Number Format) until the rest of the schedule is complete.
   6. Select cells C13:F13.
   7. Double-click the Fill pointer at cell F13. All of the formulas are filled (copied) to row 72 (Figure 2-94).

      Figure 2-94 Formulas copied down columns

   8. Scroll to see the values in row 72. The loan balance reaches 0.
   9. Press fn+Ctrl+Left Arrow or fn+Cmd+Left Arrow.