Please answer the following questions. Show all your formulas to be eligible for partial credit. And remember that when you use the perpetuity formula, the formula automatically present values the perpetuity one period, so if you use the perpetuity formula in year 7, the result is as of the beginning of year 7 and not the end. To make it easier for you, I have prepared the template below for your use, if you wish to use it. The parts you need to put your formulas in are in yellow.
Note the following:
- growth rates work off the base year of 2019 and compound
- taxes are 21% of EBT
- the Bank Loan formula is Total Assets minus Payables minus (CPLTD + Other + LTD + Equity); this is a balancing account that makes both sides of the balance sheet equal to each other
- Working Capital is Current Assets minus (Payables + CPLTD + Other); the Bank Loan is NOT included because it is not a true balance sheet liability
- the Free Cash Flows (FCF) uses EBIT minus taxes and it is calculated as (EBIT minus Taxes) minus (change in working capital) minus increase in Other Assets
- the terminal value in 2022 uses the formula 2022 FCF *(1+2022 average growth rate)^1/(Discount Rate minus 2022 average growth rate); by using 1 in the exponent, you keep the perpetuity at the end of year 2022. You then add the perpetuity value to the 2022 FCF
- you present value the 2020 FCF as being one period out, the 2021 FCF as being two periods out and the sum of the 2022 FCF and the Terminal Value (the perpetuity) as being three periods out
- the debt and cash values for the PV of FCF adjustments are the values from 2020
Jasper Industries A Company Valuation
Your manager has asked you to value Jasper Industries, a potential acquisition. To make your life easier, your manager gave you some of the numbers in the template shown above. Note that your manager wants the dollar price per share, so you must calculate the dollar value of the equity and then divide by the number of shares outstanding. Also, you must run sensitivities on the price per share using the shown discount and growth rates. Finally, you are asked to comment on the impact of discount and growth rates on the price per share.
Q- Search Sheet 9+ Share A A SU: Home Insert Draw Times New... Paste B I U Page Layout Formulas 12 - A A- = S A . Data = = Mar 2020 template-4 (1).xls [Compatibility Mode] Review View D Wrap Text General Merge & Center $ - % ) 38 Tell me 2 ' A Insert Delete Format . Z " Conditional Format Formatting as Table Cell Styles . Sort & Filter Find & Select H12 x fx E J K M N O P Q R = PV of Jasper FCF B C D Actual Projected 2019 2020 2021 5,000 3,073 1.925 1.550 2021 2022 Notes 2022 Notes Increasc at average growth rate Increase at average growth rate Insert formula Increase al average growth rale Insert formula Hold flat Insert formula Use actual 2019 tax rate Insert farinula I Free Cash Flow Valuation 2020 EBIT minus Taxes minus increase in Working Cap minus increase in Other Assets Free Casa Flow Terminal Value Prescot Value PV of Free Cash Flows + Cash Existiny Deht -PV or Jasper FCF Price per share 375 100 Soc row 35 From Pro Formas Calculation Us terminal value formula and 2022 growth rate Calculation Calculation Cash + Bank Loan (if negative) LTDCPLTD Insert formula Insert formula 275 104 0% 21% 5% 21% 4% Given 21% Hold at 2019 tax rate 250 (all figures in ks) 2 Income Statement 3 Net Sales 4 - Cost of Goods Sold S - Gross Profit 6 - S.G & A Expenses 7 -EBLT 8 - Lutcrcat 9 -ERT 10 - Taxes 11 - Net Income 12 Key Assumptions: 13 Average growth rate 14 Tax Rate 15 Discount rate 16 of shares outstanding (K) 17 Balance Sheet 18 Cash 19 Notes and Acc. Rec. 20 Invcptory 21 Prepaid 22 Current Assets 23 Other 24 Total Assets 25 26 Bank Loan 27 Pavables 28 CPLTD 29 Other 30 Curreal Liabilities 31 LTD 32 Equity 33 Total Liabilities & Equity Sensitivities 1 LT Growth Rate Disc. Rate 3.0% 4.0% 5.00% 8.0% 1,600 1.500 Increase at average growth rate for all accounts 100% 1.250 1.250 4,500 Insert formula Increase at average growth rate Insert formula 250 500 450 1.250 1.000 2.250 4,500 Plug to make balance sheet balanou Increase al average growth rate Hald flat Increase at average growth rate Insur forinula Falls yearly by 550,00 Equity of previous year plus Net Income Insert formula 34 35 Working Capital 2.250 Insurt lorinula 36 Jasper Industries + E - - + 90% Q- Search Sheet 9+ Share A A SU: Home Insert Draw Times New... Paste B I U Page Layout Formulas 12 - A A- = S A . Data = = Mar 2020 template-4 (1).xls [Compatibility Mode] Review View D Wrap Text General Merge & Center $ - % ) 38 Tell me 2 ' A Insert Delete Format . Z " Conditional Format Formatting as Table Cell Styles . Sort & Filter Find & Select H12 x fx E J K M N O P Q R = PV of Jasper FCF B C D Actual Projected 2019 2020 2021 5,000 3,073 1.925 1.550 2021 2022 Notes 2022 Notes Increasc at average growth rate Increase at average growth rate Insert formula Increase al average growth rale Insert formula Hold flat Insert formula Use actual 2019 tax rate Insert farinula I Free Cash Flow Valuation 2020 EBIT minus Taxes minus increase in Working Cap minus increase in Other Assets Free Casa Flow Terminal Value Prescot Value PV of Free Cash Flows + Cash Existiny Deht -PV or Jasper FCF Price per share 375 100 Soc row 35 From Pro Formas Calculation Us terminal value formula and 2022 growth rate Calculation Calculation Cash + Bank Loan (if negative) LTDCPLTD Insert formula Insert formula 275 104 0% 21% 5% 21% 4% Given 21% Hold at 2019 tax rate 250 (all figures in ks) 2 Income Statement 3 Net Sales 4 - Cost of Goods Sold S - Gross Profit 6 - S.G & A Expenses 7 -EBLT 8 - Lutcrcat 9 -ERT 10 - Taxes 11 - Net Income 12 Key Assumptions: 13 Average growth rate 14 Tax Rate 15 Discount rate 16 of shares outstanding (K) 17 Balance Sheet 18 Cash 19 Notes and Acc. Rec. 20 Invcptory 21 Prepaid 22 Current Assets 23 Other 24 Total Assets 25 26 Bank Loan 27 Pavables 28 CPLTD 29 Other 30 Curreal Liabilities 31 LTD 32 Equity 33 Total Liabilities & Equity Sensitivities 1 LT Growth Rate Disc. Rate 3.0% 4.0% 5.00% 8.0% 1,600 1.500 Increase at average growth rate for all accounts 100% 1.250 1.250 4,500 Insert formula Increase at average growth rate Insert formula 250 500 450 1.250 1.000 2.250 4,500 Plug to make balance sheet balanou Increase al average growth rate Hald flat Increase at average growth rate Insur forinula Falls yearly by 550,00 Equity of previous year plus Net Income Insert formula 34 35 Working Capital 2.250 Insurt lorinula 36 Jasper Industries + E - - + 90%
