MBA
COURSE : FINANCIAL MANAGEMENT (FM)
GLOBAL CASE STUDY
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: FINANCIAL MODEL
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.
Page 1 (Pro forma): Make a page where you have 3 last years (Year N, Year N-l, Year N-2) Balance sheet data, Income statement (P&L) and Cash flow statement. You have to choose the company listed you wish 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 you chose in your excel sheet.
Page 2 (Inputs): Make a page of inputs that will put all your assumptions: growth/decline rates of Revenues and Expenditures and other various inputs you will use in the financial model. On this page all inputs are manually entered. Use one large investment in the beginning year (CAPEX) Year N+l with an assumption that CAPEX is equal to 2% of the Total Assets of the Year N
Year I Year 2 Year 3 Year 4 Year 5
Financial Plan
Key Assumptions
10.0%
Sales increase/decraese (Student decision)
Income tax
Interest rate on Long term financing (Loan) (Student decision)
Coupon rate on Long term financing (Bonds) (Student decision) Pay out ratio
Number of stocks of the company
Page 3 (Amortization): Make an Amortization calculating page for all your assets including the new CAPEX annually and cumulatively with linear (straight line) amortization policy during the life of your project. Consider the amortization cost for annual future amortization of existing Assets same as average annual amortization of last 3 years from your P&L. Add a separate line for the new amortization cost of your new CAPEX using the linear amortization from Year N+l to the end of your project.
Page 4 (Projections of P&L): Make a projection of the profit and loss account for 5 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 / falls. Revenues (Sales) for the Year 1 should be taken from the Total Revenues from the Year 0 (Year N) increased/decreased by the rate of growth/decline of your Total Revenues in Year 0.
Payout ratio for dividends each Year 1 here after correspond to the year N+l.
P&L (Mil.$)
Sales
Cost of goods sold-COGS (70% of sales) year
Year 1 10%.
Year 2 Income
Year 3 tax
Year 4 30%
Year 5
Gross eamings
General and administrative costs (10% of COGS)
Amortisation(2% of total Assets)
EBIT
Interests on Loan (only from the loan and bonds of new CAPEX) Income tax
Net income/Loss
Number of stocks in million
EPS
EPS share
Dividends
Retained eamings
Page 5 (Debt): Based on page 2, make a table of amortization of loans and bonds, calculation of interests and coupons, principals using formulas (PMT or other) Use your bonds, loans and equity to finance your assets and investments. Loan has an interest rate and Bonds have a coupon rate. Both debt sources of funding are used for the duration of the investment.
For cost of bonds you can use yield which is published on web pages such as www.bloombera.com/markets/rates-bonds or other source.
Page 6 (Cash Flow):
Calculate net working capital and NWC investments. To do that use the simplified calculation Receivables and Stocks for Current assets and Liabilities for current liabilities.
Current assets
Receivables (25% of sales)
Stocks (6% of COGS)
Current liabilities
Payables (8% of COGS)
To obtain the annual receivables, Stocks and payables you should calculate it every year and determine the annual investments in such items for the purpose of your CF statement here below.
Make the cash flow calculations page from the Projections page.
Cash Flow statement (Mil.$) Year 1 Year 2 Year 3 Year 4 Year 5
CF from Operating activities
Net incomenoss
+ Amortization
- Receivables
- Stocks
Total CF from operating activities
CF from Investment activities
- CAPEX on from new investment
Total CF from Investment activities
CF from finance activities
+ Long term debt (only new loan and bonds) + Equity
- Dividends
Total CF from finance activities
Total Cash at end of period
Discount all your Cash Flow's with WACC assuming the CF of your last year (Terminal value) is growing in the infinite at a g rate ( % growth rate of CF forever) of 2% annually.
The discount rate (cost of capital) is the weighted average of the cost of capital (WACC) of all your long-term (loan, bonds and equity) funding sources. You have to calculate WACC in this page.
Calculate the shareholder's cost using the DDM model (Gordon). For bonds use the yield rate you should calculate or find it from the financial statement of your company if company has issued the bonds. For Loan use the interest rate you estimate or the lowest interest rate from the financial statement of the existing Loans.
Page 7 (Scenarios): Make a page by using the CF's obtained from the page 6 including the initial investment. Consider these CF's as base case scenario. Below, add the worst case scenario where you have an increase of your investment by 20%, and all CF's decrease by 5%. Add a best case scenario where you can reduce the investment by 12% and increase all the CPS by 6%. Calculate the XNPV and XIRR for each scenario by setting the exact dates for each year of your CF (every 30 of June of each year) with the investment starting on 01.01.Year N+l.
Analyse the optimal capital structure and what would you do as a CFO to improve the capital structure and to increase the company value. Calculate WACC in worst and best case scenarios according to the assumptions you specify.
Page 8 (Financial Management): On this page, make an analysis of the results you got. Apply the financial management to understand your company's status, value, risk, return for shareholders and business success and all shortly describe. Calculate the value of a company with the help of discounted FCFs in infinite (Gordon's model for terminal value) and using WACC. Calculate the stock value based on the method
Calculate PER (market price 12% higher than EPS) Calculate the NPV, IRR and payback period relative to the initial investment.
Value of the company should be calculated based on the following logics :
DFCF CALCULATION Year 1 Year 2 Year 3 Year 4 Year 5
EBIT -Income tax
EAT
+AMORTIZATION
-CAPEX
- NWC change
FCF
Terminal Value
DFCF
Enterprise Value
+Cash
-Long Term Debt and Bonds
Market value of Equity Number of shares
Value of Equity/share
Discount all your Free Cash FloWs with WACC assuming the FCF of your last year (Terminal value) is growing in the infinite at a g rate ( % growth rate of CF forever) of 2% annually. For Cash item use the cash amount from the balance sheet of your company from the Year N.
Page 9 (Conclusion): On page 10, -Conclusion,- explain the process, model problems, how you as a CFO can improve your company's business by using the entire above analysis to maximize your revenue and minimize your expenses. Your goal is to repay long-term loan and bonds, pay dividends to shareholders, and ensure that IRR is greater than WACC. Once you calculate the XIRR on page 7 and WACC in base case scenario explain whether you should launch the investment in your project or not. Explain how you would further increase IRR and reduce WACC and increase the Enterprise value. What do you suggest to the Executive Board as an incentive to achieve the planned goals of raising the company's value and stock price.
Notes-
-Do not copy paste datas or exact form from preparatory case which has to be used only to help students to understand the Final Assignment
- You need an Introduction, an Explanationi and a Conclusion
- First projection year is N+l (or Year 1)
- An additional plus IS If you have a detailed explanation of whether your stocks are overvalued or undervalued. Is IRR acceptable to Investors? How to increase the value of a company for shareholders ? Is the financing structure optimal?
- An additional plus is if you have good financial and adequate formulas in excel. In the model everything must be linked With formulas In excel (sum, If, NPV, IRR PMT, Price, Yield etc)
- you have to put data sources and to explain where have you found your datas and inputs
GET ANSWERS / LIVE CHAT