VALUATION PROJECT Case Solution
PART I: CREATE PRO-FORMA FINANCIAL STATEMENTS
Forecast all the line items in the income statement and balance sheet. In the table below, write down all the line items that you chose to forecast in the following tables (if you run out of room, add a new line to the table).
Income Statement Line Item | Forecast method |
Revenue | Assumed 3% growth, from 10K’s |
Cost of goods sold | Assumed 3% as inflation rate |
SGA Expenses | Assumed 3 percent |
Depreciation | Assumed same as per 2015 |
Interest Expenses | Calculation by multiplying cost of debt into LT debts |
Other Expenses | Assumed moving average for last 4 years |
Income Tax | Assumed 30% |
Balance Sheet Line Item | Forecast Method |
Cash | Assumed 7% of sales, in line with historical values |
A/R, Inventories, PP&E, and others | Calculated by taking moving averages |
How did you forecast depreciation?
The Depreciation is assumed as same for all the proceeding years as in the year 2015
How did you forecast your interest expense?
The Interest Expense has been calculated by multiplying the interest rate that is cost of debt by the long term debts.
What did you use as your plug number and how did you do this?
The Plug is used to balance the Total assets and Total liabilities & equity in the balance sheet. The Long term Debt is used as a plug in order to gain the extra financing required for the operation of the company. The Plug is calculated by taking the difference in the total assets and total liabilities & equity.
Calculate the numbers in “Useful stuff” below the income statement and balance sheet (these include EBIAT, NWC, etc.).
Useful stuff | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 |
CAPEX1 (change in PP&E gross) | 10119 | 6632 | 10496 | 10242 | -5121.0 | 2560.5 | -1280.3 | 640.1 |
CAPEX2 (change in PP&E gross + change in other LTA) | 49472.0 | 20790.0 | 36063.0 | 46188.0 | -23094.0 | 11547.0 | -5773.5 | 2886.8 |
Delta LT assets | 47028.0 | 15303.0 | 29594.0 | 37793.0 | 41735.0 | 22804.0 | 5483.5 | 14143.8 |
NWC | 19111.0 | 29628.0 | 5083.0 | 8768.0 | 21758.9 | 21151.5 | 15622.8 | 16513.5 |
Delta NWC | 2093.0 | 10517.0 | -24545.0 | 3685.0 | 12990.9 | -607.3 | -5528.7 | 890.7 |
Delta LT liabilities | 7526.0 | 20481.0 | 17051.0 | 33670.0 | 46371.4 | 13257.2 | -9610.3 | 4799.9 |
PART II: FORECAST FREE CASH FLOWS AND FLOWS TO EQUITY
Calculate your free cash flows (FCF) using equations (3) and (4) from the book. Calculate your flows to equity (FTE) in the table provided in the excel template. Copy that table below (format so it fits):
2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | |
FCF = EBIAT - DeltaNWC - DeltaLTA (3) | 2843.0 | 16422.0 | 39508.0 | 18495.0 | 7626.1 | 42605.5 | 67371.2 | 54891.0 |
FCF = EBIAT - DeltaNWC - CAPEX2 + DEPR (4) | 3676.0 | 17692.0 | 40985.0 | 21357.0 | 83712.1 | 65119.5 | 89885.2 | 77405.0 |
FTE = NI - DeltaNWC - CAPEX2 + DEPR + DeltaLTLia | -3350.0 | 26211.0 | 45043.0 | 37191.0 | 111183.1 | 58917.6 | 60157.1 | 61287.1 |
PART III: DTERMINE YOUR DISCOUNT RATES
Determine your equity discount rate Re using the CAPM and add it to the Excel template. Describe how you did this below.
The Re (Equity discount rate) is calculated by the using the formula Re = Rf + B*(Rm-Rf) of the Capital Asset Pricing Model. The Beta is taken as 1.03 from the google finance. And the Risk Free rate is taken as 0.26% that is 1 month Treasury bill rate and the Market return is assumed as 6 percent. The Re is calculated as 6.17 percent.
Determine your debt discount rate Rd. Describe how you did this below.
The Debt rate is calculated by dividing the interest expense from the Long Term Debt. In order to provide a better and accurate figure, the four year interest expense was divided by the 4 year long term debt. The Debt discount rate is calculated as 1.26 percent......................
This is just a sample partial case solution. Please place the order on the website to order your own originally done case solution.