MBA : GLOBAL CASE STUDY ASSIGNMENT
COURSE : FINANCIAL MANAGEMENT (FM)
In order to succeed on the course FM, as stated in the Syllabus, it is necessary to make a Global Case Study in the form of a financial model in excel.
The goal is to apply in practice the financial tools you have learned on FM lectures. It is a specific task whose solution makes it possible to understand and manage corporate finance.
Global Case Study: INSTRUCTIONS
Objective: To create an excel financial model associated with formulas from accounting to cash flow to Valuate the company, to calculate CF, FCF, optimal capital structure and to determine the risk of investment (CAPEX) using different techniques for measuring investment profitability. You will have a company with its financial forecasts in excel with a possibility to do scenario or sensitivity analysis.
Please build a model in excel with the following sheets (sheets=pages):
Page 1 (Tips) :
On this page you have some useful recommendations to be considered in overall assignment. Before you start with Page 2 it is fully advised that you read carefully all tips with separate recommendations what is advised (+) and what is not (-) advised in assignment preparation. It does not mean that you will get negative points if you do not respect all recommendations but if you respect what is there advised your global case will be more exact, complete and in accordance with assignment expectations.
Page 2 (History) - 5 points :
On this page you have to add missing data in blue parts. You have to add in blue parts for 3 last years (Year N, Year N-1, Year N-2). You can copy the data from the source page and paste it in this sheet. Balance sheet data, Income statement (P&L) and Cash flow statement. You have to choose the company listed on stock exchange from your choice from one of the following web sources : www.nasdaq.com, www.finance.yahoo.com, www.marketwatch.com, www.reuters.com, or other. Please add the name of source in blue part at the end of financial statements.
Page 3 (Inputs) :
On this page you have to add all your assumptions: growth/decline rates of Revenues and Expenditures and other various inputs you will use in the financial model, however please note that some green cells are already predefined/filled in and You must not change them. On this page all inputs are manually entered in the green coloured cells.
The whole Model can be modified if you change the assumptions on this page. It helps you as CFO to do the scenarios or sensitivities simulations in order to present to your management the possible project outcomes or company valuations.
Page 4 (Assets Amortization/Depreciation) – 5 points :
Make an Amortization/Depreciation calculating page for all long-term assets including the new
CAPEX annually during the life of your project (7 years). Consider the amortization/depreciation cost for annual future amortization/depreciation of existing Assets same as average annual A&D of last 3 years from your realized financial statements in P2 History. If in your Financial Statements there is no A&D of assets cost then you just add 0 in P4 in line for Existing annual assets amortization. If you do not have all last 3 past years you can take only the amortization cost of the last year.
In a separate line for the new amortization cost of your new CAPEX calculate annual amortization costs using the linear or straight line amortization method from Year N+1 to the end of your project.
To do this task we assume that Company will start a new large investment (CAPEX) in the beginning Year N+1 with an assumption that CAPEX is equal to 10% of the Total Revenues of the Year N+1.
Page 5 (Cost of Capital) – 15 points :
On this page your task is to calculate the WACC. The discount rate (cost of capital) is the weighted average of the cost of capital (WACC) of all your long-term (Bank loan, bonds and equity) funding sources.
As CFO of your company your goal is to reduce the overall cost of financing of your CAPEX from Page 4. So, your management accepted your proposal to finance your CAPEX equally with bank loan, bonds and equity.
To determine WACC you have to calculate the cost of lenders (Bank loan), the cost of bondholders (bonds) and the cost of shareholders (equity).
Based on inputs from page 3, make a table of amortization of loan and bonds, calculation of interests and coupons, principals using formulas (PMT or other)
Bank Loan has an interest rate defined in P3 inputs and is repaid in annuities over 7 years.
Bonds have a coupon rate defined in P3 inputs and with face value paid at maturity in year 7.
Both debt sources of funding are received one shot and used for the investment financing at beginning of year N+1. We assume there are no finance fees to be calculated and considered.
For cost of bank loan or bonds (rates) you can either use the same rates as the existing bank loans (use the lowest loan rate) or bonds (yield) if any in company, or you can also use rates from your peers or comparative companies of similar risk which is published on web pages such as www.bloomberg.com/markets/rates-bonds or other source.
Please add each time a source you are using if any. If you do not use any of proposed rates then you can estimate your rates in which case please add a short explanation of such estimated rates.
Calculate the shareholders cost using the DDM model (Gordon).
For the purpose of calculation of cost of equity only we shall assume that your dividends are same forever, starting from Year 0, with an annual increase forever defined in P3 Inputs.
For all long term financing sources here let we assume that the corresponding rates are same for each source of financing until the end of project.
Page 6 (Projection of Financial Statements) – 15 points :
As CFO of your Company you should be able to project your Financial Statements, namely P&L and Balance sheet as first step on Page 6 and CF statement on Page 7.
Projection of P&L
Start with P&L and then with Balance sheet. To project your Profit and Loss statement you should take into consideration the impact of amortization policy, financial costs of your long term financing of your CAPEX, profit tax and other components of P&L.
Make a projection of the profit and loss account for 7 years based on the assumptions from the page inputs. Your revenues and costs from the Inputs page should be increased/decreased for rates of revenue/costs growth / decline. Revenues (Sales) for the Year 1 should be taken from the Total Revenues from the realized previous Year 0 (N) increased/decreased by the rate of growth/decline of your Total Revenues in Year 0.
Your projected P&L is simplified and considers only the lines defined on P6.
If in your projected P&L you obtain losses most of years you should probably modify your inputs in order to obtain net incomes which are more interesting to be used for modelling. For the simplification reason, you are expected to use only the costs related to COGS, depreciation/amortization related to existing LT assets and new assets (CAPEX) and and financial interests relating to new financial debt in P&L.
For dividends calculation we shall assume that the only condition to pay it annually is a positive net income.
Projection of Balance sheet
Cash of Year 0 should be considered from your last realized year, such Cash item to be added in inputs. From Cash in Year 0 you should calculate future cash item based on assumptions in inputs.
Receivables and Stocks are calculated based on assumptions in inputs. We assume there are no Receivables and Stocks in Year 0.
Tangible assets or Fixed Assets should be considered from your last realized year as Net Tangible assets, such item to be added in inputs. In Year 1 the Tangible Assets should be increased for the amount of your new CAPEX even if it is realized at beginning of year.
If there are no Tangible Assets in your last realized balance sheet then consider only your new CAPEX in Year 1 as Tangible Assets.
Accumulated amortization in Year 1 consider only the Accumulated amortization from existing Capex for simplification reasons.
Payables and other Liabilities are calculated from Year 1 based on assumptions in Inputs.
For simplification reasons the Long Term debt is calculated as a difference between Total Liabilities and Total Short Tern liabilities. For simplification reasons the Total Liabilities is equal to difference between Total Equity and Liabilities and Equity.
Shareholders equity in Year 0 should be considered from your last realized Balance sheet, the item to be added in inputs. The Shareholders equity in Year N corresponds to Total Equity in Year 0. In Year 1 do not forget to add in Shareholders Equity the Equity used to finance part of your Capex from Page 5.
Retained earnings/Deferred losses item in Year 0 should be considered from your last realized Balance sheet, the item to be added in inputs. In Retained earnings/Deferred losses item please also consider the future incomes/losses from Year 1 to Year 7.
Total Equity and Liabilities has to be equal to Total Assets. Check test at end of Balance sheet should state “OK” if this is verified, if not test will show “NOT OK”.
Page 7 (Cash Flow) – 10 points :
To do the scenarios analysis you need Cash Flows (CF). In this page, you have to determine the corresponding CF’s resulting from company’s operating, investment and finance activities during the project life.
Keep in mind that for the simplification reasons the idea is to have only basic and main items of company in CF statement as proposed in formatted table. In other words, you will consider only activities resulting from your projections from Year 1 to Year 7 in P6.
Page 8 (Scenarios) – 10 points :
Finally, you have your CFs in Page 7. Now, you can apply your CFOs skills you have acquired on Financial Management course at SSBM and demonstrate to your management board how different scenarios can impact the profitability of company.
In this page you will use the CFs obtained from the page 7 including the new investment (CAPEX)
However, to have your CFs resulting from only your new CAPEX you need to take only part of total annual CFs in CF statement. We shall assume that 20% (as defined in inputs Page 3) of each annual Cash Flow (use Change of Cash line in CF statement) results from your new CAPEX. Add such reduced CFs in Cash Flow line using formula. Do not forget to add your new CAPEX in Year 0 instead of year 1 in Cash Flow line. Only in Year 1 You should remove new Capex from Change of cash in Year 1 (note that – and – make +) in order to get CF of Year 1 in Base Case Scenario.
Such obtained CFs will be considered as your Base case Scenario 1.
Now, you can finally calculate the project NPV (WACC rate is used as discount rate) and IRR for Base Case Scenario.
Even if management board is fine with this you have to demonstrate that in case of problems on market some of the considered inputs can deteriorate and in such situation it is important to see the consequence on the profitability.
Now, in following lines you will do the Worst case scenario 2 based on the following assumptions :
- an increase of your investment (new CAPEX) by 20%,
-decrease of all CFs from Year 1 to Year 7, each CF is decreased by 5%.
Repeat, the calculation of the project NPV (WACC rate is used as discount rate) and IRR for Worst Case Scenario.
To simplify the calculations, you can use the CAPEX and CFs from Base case scenario.
However, your management bord considers that you are perhaps too pesimistic and as they like to expect the profitability and higher dividends they would like you to sho them also the profitability in Best case scenario.
Now, in following lines you will do the Best case scenario 3 based on the following assumptions
:
- a reduction of your investment (new CAPEX) by 15%,
- increase of CFs from Year 1 to Year 7, each CF is increased by 6%.
Repeat, the calculation of the project NPV (WACC rate is used as discount rate) and IRR for Base Case Scenario.
To simplify the calculations, you can use the CAPEX and CFs from Base case scenario.
If your Cash Flows are very disproportional to the CAPEX and you cannot calculate IRR and/or NPV, you can try to modify inputs, items like CAPEX or CF’s to obtain NPV and IRR more realistic but please then explain the problems you are facing and the way to solve it with new inputs you propose.
Page 9 (NWC) - 5 points :
Your management has now a quite good picture on CF’s and different scenarios. However, they are concerned if company has sufficient Net working capital to ensure the project development. To get the answer and to assure your management board, you, as CFO is supposed to calculate the NWC in Page 9. To do that use the simplified calculation based on your projected values on Page 6. For Current Assets consider only Receivables and Stocks while for Current Liabilities consider Payables and Other liabilities.
You are also requested to calculate the Increase or Decrease of NWC, the figures you will need in Company valuation.
Page 10 (Valuation) – 15 points :
Once you have obtained your projections in previous pages you are now in position to proceed to the valuation of your company.
On this page, you will determine the company value based on Free Cash Flows (FCF). Apply the financial management to calculate your companys value for shareholders.
Add the empty cells using formulas. In line Capex consider only the new investment from Page 5. For NWC line you have the values obtained in Page 9.
For Terminal Value of your FCF use Gordons model used to discount the infinite FCF’s. This means that your FCF’s from Year 8 growth by growth rate (defined in Inputs) of FCF forever.
Calculate the value of your company with the help of discounted FCFs in infinite and using WACC as discount rate Cash and Long term debt are from the last realized year..
Calculate the Equity value based on FCF and stock price. As equity value is in thousands you should multiply your obtained stock price by 1000 to get the full stock price based on FCF. You have obtained the stock price using intrinsic method of valuation based on FCF’s. You would like to check if the obtained value is realistic. To do this, you have to apply the second intrinsic method of equity valuation which is based on dividends.
Using formula get your dividends you have already calculated in Page 6.
For Terminal Value of your Dividends we shall assume that paid Dividends will be same forever. This means that company will pay same amount of Dividends from Year 8 forever.
Calculate your discounted dividends using cost of equity from Page 5 as discount rate.
Calculate your Equity based on Dividends.
As equity value is in thousands you should multiply your obtained stock price by 1000 to get the full stock price based on Dividends.
Add market price and Net Income using formula from Input page. Calculate Market Capitalization using values from Page Inputs.
Calculate Price Earnings Ratio.
The chart with comparison of Stock prices based on FCF, Dividends and Market stock price enable to see the differences.
Now, you have all necessary results to proceed to the Conclusion.
Page 11 (Conclusion) 20 points :
Congratulations, if you are at Conclusion page it means that you have managed to apply in practice the financial tools you have learned on FM lectures through pages 1-10. The page is the most important one in terms of points for grading as the interpretation of financial results is something very important and crucial for everyone who deals with corporate finance and financial management.
The obtained figures in previous pages is a condition precedent to make possible understanding the financial situation of your company. In other words, the obtained outputs based on inputs will help you, as CFO, to proceed now to the interpretation of the company value, different risks of company, capital structure, profitability of investment and value of equity for owners.
Your management board request you to present the financial situation of company in this page through following parts :
Capital Structure
Use the results from Page 5 to consider if your capital structure used for CAPEX financing is well structured ? Explain what would you do to make it more optimal, if possible ?
Use the results from Page 9 to analyze the short term financing needs. Is your NWC optimal ? What would you do to improve your NWC ? Do you have cash gaps and how would you finance it ?
Financial Statements Analysis
Use the results from Page 6 to analyze the financial statements. What do you think about the profitability in projected P&L and can you identify any problem there ? Is your balance sheet well structured and are there any issues ?
Profitability
Is your CAPEX used in Page 8 profitable in Base Case Scenario ?
What are the conclusions of Best case and Worst case Scenarios compared to Base Case Scenario ?
What is your IRR in Base case scenario compared to WACC (page 5) and what conclusion you can make from it ?
Valuation
What would you do as a CFO to increase the company value ? Is the company market stock price overvalued or undervalued compared to obtained intrinsic values of stock price based on FCF and Dividends ?
Explain how you would further increase IRR and/or reduce WACC and increase the Company value. What do you suggest to your Management Board as an incentive to achieve the planned goals of raising the companys value and stock price.
Page 12 (Instructor):
In this Page the Pages 2,4,5,6,7,8,9,10 and 11 will be evaluated based points given by Instructor that you can find on top of each page. The total will give the total number of points which will be added in cell and corresponding student grade based on grading scale will be added here. The Total number of points is the same as percentage number.
Prof Dario Silic SSBM