Posted: August 5th, 2022

MBA : GLOBAL CASE STUDY ASSIGNMENT
COURSE : FINANCIAL MANAGEMENT (FM)
As a way to succeed on the course FM, as said within the Syllabus, it’s essential to make a International Case Research within the type of a monetary mannequin in excel.
The objective is to use in follow the monetary instruments you’ve discovered on FM lectures. It’s a particular activity whose answer makes it doable to grasp and handle company finance.
International Case Research: INSTRUCTIONS
Goal: To create an excel monetary mannequin related to formulation from accounting to money stream to Valuate the corporate, to calculate CF, FCF, optimum capital construction and to find out the chance of funding (CAPEX) utilizing totally different methods for measuring funding profitability. You’ll have an organization with its monetary forecasts in excel with a risk to do state of affairs or sensitivity Assessment.
Please construct a mannequin in excel with the next sheets (sheets=pages):
Web page 1 (Suggestions) :
On this web page you’ve some helpful suggestions to be thought of in total task. Earlier than you begin with Web page 2 it’s totally suggested that you simply learn fastidiously all suggestions with separate suggestions what is suggested (+) and what’s not (-) suggested in task preparation. It doesn’t imply that you’ll get unfavourable factors if you don’t respect all suggestions however in case you respect what’s there suggested your world case shall be extra precise, full and in accordance with task expectations.
Web page 2 (Historical past) – 5 factors :
On this web page you must add lacking information in blue elements. You must add in blue elements for three final years (12 months N, 12 months N-1, 12 months N-2). You’ll be able to copy the information from the supply web page and paste it on this sheet. Stability sheet information, Revenue assertion (P&L) and Money stream assertion. You must select the corporate listed on inventory change out of your alternative from one of many following net sources : www.nasdaq.com, www.finance.yahoo.com, www.marketwatch.com, www.reuters.com, or different. Please add the title of supply in blue half on the finish of monetary statements.
Web page three (Inputs) :
On this web page you must add all of your assumptions: progress/decline charges of Revenues and Expenditures and different varied inputs you’ll use within the monetary mannequin, nonetheless please notice that some inexperienced cells are already predefined/stuffed in and It’s essential to not change them. On this web page all inputs are manually entered within the inexperienced colored cells.
The entire Mannequin will be modified in case you change the assumptions on this web page. It helps you as CFO to do the eventualities or sensitivities simulations to be able to current to your administration the doable undertaking outcomes or firm valuations.
Web page four (Belongings Amortization/Depreciation) – 5 factors :
Make an Amortization/Depreciation calculating web page for all long-term belongings together with the brand new
CAPEX yearly throughout the lifetime of your undertaking (7 years). Think about the amortization/depreciation price for annual future amortization/depreciation of current Belongings identical as common annual A&D of final three years out of your realized monetary statements in P2 Historical past. If in your Monetary Statements there isn’t a A&D of belongings price you then simply add zero in P4 in line for Present annual belongings amortization. When you wouldn’t have all final three previous years you possibly can take solely the amortization price of the final 12 months.
In a separate line for the brand new amortization price of your new CAPEX calculate annual amortization prices utilizing the linear or straight line amortization technique from 12 months N+1 to the tip of your undertaking.
To do that activity we assume that Firm will begin a brand new giant funding (CAPEX) to start with 12 months N+1 with an assumption that CAPEX is the same as 10% of the Complete Revenues of the 12 months N+1.
Web page 5 (Price of Capital) – 15 factors :
On this web page your activity is to calculate the WACC. The low cost price (price of capital) is the weighted common of the price of capital (WACC) of all of your long-term (Financial institution mortgage, bonds and fairness) funding sources.
As CFO of your organization your objective is to scale back the general price of financing of your CAPEX from Web page four. So, your administration accepted your proposal to finance your CAPEX equally with financial institution mortgage, bonds and fairness.
To find out WACC you must calculate the price of lenders (Financial institution mortgage), the price of bondholders (bonds) and the price of shareholders (fairness).
Primarily based on inputs from web page three, make a desk of amortization of mortgage and bonds, calculation of pursuits and coupons, principals utilizing formulation (PMT or different)
Financial institution Mortgage has an rate of interest outlined in P3 inputs and is repaid in annuities over 7 years.
Bonds have a coupon price outlined in P3 inputs and with face worth paid at maturity in 12 months 7.
Each debt sources of funding are obtained one shot and used for the funding financing at starting of 12 months N+1. We assume there are not any finance charges to be calculated and regarded.
For price of financial institution mortgage or bonds (charges) you possibly can both use the identical charges as the present financial institution loans (use the bottom mortgage price) or bonds (yield) if any in firm, or you can even use charges out of your friends or comparative firms of comparable threat which is printed on net pages equivalent to www.bloomberg.com/markets/rates-bonds or different supply.
Please add every time a supply you’re utilizing if any. If you don’t use any of proposed charges then you possibly can estimate your charges wherein case please add a brief clarification of such estimated charges.
Calculate the shareholders price utilizing the DDM mannequin (Gordon).
For the aim of calculation of price of fairness solely we will assume that your dividends are identical perpetually, ranging from 12 months zero, with an annual improve perpetually outlined in P3 Inputs.
For all long run financing sources right here let we assume that the corresponding charges are identical for every supply of financing till the tip of undertaking.
Web page 6 (Projection of Monetary Statements) – 15 factors :
As CFO of your Firm it is best to be capable of undertaking your Monetary Statements, specifically P&L and Stability sheet as first step on Web page 6 and CF assertion on Web page 7.
Projection of P&L
Begin with P&L after which with Stability sheet. To undertaking your Revenue and Loss assertion it is best to consider the influence of amortization coverage, monetary prices of your long run financing of your CAPEX, revenue tax and different parts of P&L.
Make a projection of the revenue and loss account for 7 years primarily based on the assumptions from the web page inputs. Your revenues and prices from the Inputs web page needs to be elevated/decreased for charges of income/prices progress / decline. Revenues (Gross sales) for the 12 months 1 needs to be taken from the Complete Revenues from the realized earlier 12 months zero (N) elevated/decreased by the speed of progress/decline of your Complete Revenues in 12 months zero.
Your projected P&L is simplified and considers solely the strains outlined on P6.
If in your projected P&L you get hold of losses most of years it is best to most likely modify your inputs to be able to get hold of web incomes that are extra attention-grabbing for use for modelling. For the simplification cause, you’re anticipated to make use of solely the prices associated to COGS, depreciation/amortization associated to current LT belongings and new belongings (CAPEX) and and monetary pursuits referring to new monetary debt in P&L.
For dividends calculation we will assume that the one situation to pay it yearly is a constructive web earnings.
Projection of Stability sheet
Money of 12 months zero needs to be thought of out of your final realized 12 months, such Money merchandise to be added in inputs. From Money in 12 months zero it is best to calculate future money merchandise primarily based on assumptions in inputs.
Receivables and Shares are calculated primarily based on assumptions in inputs. We assume there are not any Receivables and Shares in 12 months zero.
Tangible belongings or Mounted Belongings needs to be thought of out of your final realized 12 months as Internet Tangible belongings, such merchandise to be added in inputs. In 12 months 1 the Tangible Belongings needs to be elevated for the quantity of your new CAPEX even whether it is realized at starting of 12 months.
If there are not any Tangible Belongings in your final realized steadiness sheet then think about solely your new CAPEX in 12 months 1 as Tangible Belongings.
Gathered amortization in 12 months 1 think about solely the Gathered amortization from current Capex for simplification causes.
Payables and different Liabilities are calculated from 12 months 1 primarily based on assumptions in Inputs.
For simplification causes the Lengthy Time period debt is calculated as a distinction between Complete Liabilities and Complete Brief Tern liabilities. For simplification causes the Complete Liabilities is the same as distinction between Complete Fairness and Liabilities and Fairness.
Shareholders fairness in 12 months zero needs to be thought of out of your final realized Stability sheet, the merchandise to be added in inputs. The Shareholders fairness in 12 months N corresponds to Complete Fairness in 12 months zero. In 12 months 1 don’t forget so as to add in Shareholders Fairness the Fairness used to finance a part of your Capex from Web page 5.
Retained earnings/Deferred losses merchandise in 12 months zero needs to be thought of out of your final realized Stability sheet, the merchandise to be added in inputs. In Retained earnings/Deferred losses merchandise please additionally think about the long run incomes/losses from 12 months 1 to 12 months 7.
Complete Fairness and Liabilities must be equal to Complete Belongings. Examine check at finish of Stability sheet ought to state “OK” if that is verified, if not check will present “NOT OK”.
Web page 7 (Money Stream) – 10 factors :
To do the eventualities Assessment you want Money Flows (CF). On this web page, you must decide the corresponding CF’s ensuing from firm’s working, funding and finance actions throughout the undertaking life.
Take into account that for the simplification causes the thought is to have solely primary and fundamental gadgets of firm in CF assertion as proposed in formatted desk. In different phrases, you’ll think about solely actions ensuing out of your projections from 12 months 1 to 12 months 7 in P6.
Web page eight (Situations) – 10 factors :
Lastly, you’ve your CFs in Web page 7. Now, you possibly can apply your CFOs abilities you’ve acquired on Monetary Administration course at SSBM and reveal to your administration board how totally different eventualities can influence the profitability of firm.
On this web page you’ll use the CFs obtained from the web page 7 together with the brand new funding (CAPEX)
Nonetheless, to have your CFs ensuing from solely your new CAPEX you’ll want to take solely a part of complete annual CFs in CF assertion. We will assume that 20% (as outlined in inputs Web page three) of every annual Money Stream (use Change of Money line in CF assertion) outcomes out of your new CAPEX. Add such lowered CFs in Money Stream line utilizing method. Don’t forget so as to add your new CAPEX in 12 months zero as an alternative of 12 months 1 in Money Stream line. Solely in 12 months 1 It’s best to take away new Capex from Change of money in 12 months 1 (notice that – and – make +) to be able to get CF of 12 months 1 in Base Case State of affairs.
Such obtained CFs shall be thought of as your Base case State of affairs 1.
Now, you possibly can lastly calculate the undertaking NPV (WACC price is used as low cost price) and IRR for Base Case State of affairs.
Even when administration board is ok with this you must reveal that in case of issues on market a few of the thought of inputs can deteriorate and in such state of affairs you will need to see the consequence on the profitability.
Now, in following strains you’ll do the Worst case state of affairs 2 primarily based on the next assumptions :
– a rise of your funding (new CAPEX) by 20%,
-decrease of all CFs from 12 months 1 to 12 months 7, every CF is decreased by 5%.
Repeat, the calculation of the undertaking NPV (WACC price is used as low cost price) and IRR for Worst Case State of affairs.
To simplify the calculations, you should utilize the CAPEX and CFs from Base case state of affairs.
Nonetheless, your administration bord considers that you’re maybe too pesimistic and as they wish to anticipate the profitability and better dividends they want you to sho them additionally the profitability in Finest case state of affairs.
Now, in following strains you’ll do the Finest case state of affairs three primarily based on the next assumptions
:
– a discount of your funding (new CAPEX) by 15%,
– improve of CFs from 12 months 1 to 12 months 7, every CF is elevated by 6%.
Repeat, the calculation of the undertaking NPV (WACC price is used as low cost price) and IRR for Base Case State of affairs.
To simplify the calculations, you should utilize the CAPEX and CFs from Base case state of affairs.
In case your Money Flows are very disproportional to the CAPEX and you can’t calculate IRR and/or NPV, you possibly can attempt to modify inputs, gadgets like CAPEX or CF’s to acquire NPV and IRR extra life like however please then clarify the issues you’re going through and the way in which to resolve it with new inputs you plan.
Web page 9 (NWC) – 5 factors :
Your administration has now a fairly good image on CF’s and totally different eventualities. Nonetheless, they’re involved if firm has ample Internet working capital to make sure the undertaking growth. To get the reply and to guarantee your administration board, you, as CFO is meant to calculate the NWC in Web page 9. To try this use the simplified calculation primarily based in your projected values on Web page 6. For Present Belongings think about solely Receivables and Shares whereas for Present Liabilities think about Payables and Different liabilities.
You might be additionally requested to calculate the Improve or Lower of NWC, the figures you will have in Firm valuation.
Web page 10 (Valuation) – 15 factors :
Upon getting obtained your projections in earlier pages you are actually in place to proceed to the valuation of your organization.
On this web page, you’ll decide the corporate worth primarily based on Free Money Flows (FCF). Apply the monetary administration to calculate your companys worth for shareholders.
Add the empty cells utilizing formulation. In line Capex think about solely the brand new funding from Web page 5. For NWC line you’ve the values obtained in Web page 9.
For Terminal Worth of your FCF use Gordons mannequin used to low cost the infinite FCF’s. Which means your FCF’s from 12 months eight progress by progress price (outlined in Inputs) of FCF perpetually.
Calculate the worth of your organization with the Helpance of discounted FCFs in infinite and utilizing WACC as low cost price Money and Long run debt are from the final realized 12 months..
Calculate the Fairness worth primarily based on FCF and inventory worth. As fairness worth is in 1000’s it is best to multiply your obtained inventory worth by 1000 to get the complete inventory worth primarily based on FCF. You will have obtained the inventory worth utilizing intrinsic technique of valuation primarily based on FCF’s. You wish to examine if the obtained worth is life like. To do that, you must apply the second intrinsic technique of fairness valuation which relies on dividends.
Utilizing method get your dividends you’ve already calculated in Web page 6.
For Terminal Worth of your Dividends we will assume that paid Dividends shall be identical perpetually. Which means firm pays identical quantity of Dividends from 12 months eight perpetually.
Calculate your discounted dividends utilizing price of fairness from Web page 5 as low cost price.
Calculate your Fairness primarily based on Dividends.
As fairness worth is in 1000’s it is best to multiply your obtained inventory worth by 1000 to get the complete inventory worth primarily based on Dividends.
Add market worth and Internet Revenue utilizing method from Enter web page. Calculate Market Capitalization utilizing values from Web page Inputs.
Calculate Worth Earnings Ratio.
The chart with comparability of Inventory costs primarily based on FCF, Dividends and Market inventory worth allow to see the variations.
Now, you’ve all vital outcomes to proceed to the Conclusion.
Web page 11 (Conclusion) 20 factors :
Congratulations, in case you are at Conclusion web page it means that you’ve got managed to use in follow the monetary instruments you’ve discovered on FM lectures by way of pages 1-10. The web page is a very powerful one when it comes to factors for grading because the interpretation of monetary outcomes is one thing essential and essential for everybody who offers with company finance and monetary administration.
The obtained figures in earlier pages is a situation precedent to make doable understanding the monetary state of affairs of your organization. In different phrases, the obtained outputs primarily based on inputs will Help you, as CFO, to proceed now to the interpretation of the corporate worth, totally different dangers of firm, capital construction, profitability of funding and worth of fairness for homeowners.
Your administration board request you to current the monetary state of affairs of firm on this web page by way of following elements :
Capital Construction
Use the outcomes from Web page 5 to think about in case your capital construction used for CAPEX financing is properly structured ? Clarify what would you do to make it extra optimum, if doable ?
Use the outcomes from Web page 9 to research the quick time period financing wants. Is your NWC optimum ? What would you do to enhance your NWC ? Do you’ve money gaps and the way would you financial it ?
Monetary Statements Assessment
Use the outcomes from Web page 6 to research the monetary statements. What do you concentrate on the profitability in projected P&L and may you determine any drawback there ? Is your steadiness sheet properly structured and are there any points ?
Profitability
Is your CAPEX utilized in Web page eight worthwhile in Base Case State of affairs ?
What are the conclusions of Finest case and Worst case Situations in comparison with Base Case State of affairs ?
What’s your IRR in Base case state of affairs in comparison with WACC (web page 5) and what conclusion you may make from it ?
Valuation
What would you do as a CFO to extend the corporate worth ? Is the corporate market inventory worth overvalued or undervalued in comparison with obtained intrinsic values of inventory worth primarily based on FCF and Dividends ?
Clarify how you’ll additional improve IRR and/or scale back WACC and improve the Firm worth. What do you counsel to your Administration Board as an incentive to attain the deliberate targets of elevating the companys worth and inventory worth.
Web page 12 (Teacher):
On this Web page the Pages 2,four,5,6,7,eight,9,10 and 11 shall be evaluated primarily based factors given by Teacher that you will discover on high of every web page. The entire will give the entire variety of factors which shall be added in cell and corresponding pupil grade primarily based on grading scale shall be added right here. The Complete variety of factors is similar as share quantity.
Prof Dario Silic SSBM

Order | Check Discount