Professional blog

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.

Page 1 (Tips) :

In 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 :

In 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) : 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 end of financial statements.

Page 3 (Inputs) :

In page inputs 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. On this page all inputs are manually entered in green color cells. All Model can be modified if you change the assumptions in 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) – 5 points :

Make an Amortization calculating page for all your assets including the new CAPEX annually during the life of your project (7 years). Consider the amortization cost for annual future amortization of existing Assets same as average annual amortization of last 3 years from your realized financial statements in P2 History. If in your Financial Statements there is no amortization 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.

cIn 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 let 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

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 shareholder’s 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 in 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 simplification reason, you are expected to use only the costs related to your CAPEX for amortization costs and financial interests in P&L.

For dividends calculation we shall assume that the only condition to pay it annually is a positive net income.

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 CF’s in Page 7. Now, you can apply your CFO’s 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 CF’s obtained from the page 7 including the new investment (CAPEX).

However, to have your CF’s resulting from only your new CAPEX you need to take only part of total annual CF’s 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 CF’s 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.

Such obtained CF’s 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.

The post MBA Financial Management Global Case Study Assignment, SSBM appeared first on Singapore Assignment Help.