2- Financial Analysis

Global Case Study: FINANCIAL MODEL

Objective:
To create an excel financial model associated with formulas from accounting to cash flow to calculate the company profitability, to calculate CFs, 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 scenarios or sensitivity analysis.

In Page 1
(past statements/benchmarks):
a- Make a page in excel where you will explain what type of company you want to analyze (your choice of company).
Answer a- My choice company is stc.com.sa (Saudi telecom company) please use this company, All information about the stc company and all_Anuual_financial Reports_from 2004 tel 2021, in Link below:
https://www.stc.com.sa/content/stcgroupwebsite/sa/en/investors/financial-reports/reports/annual-reports.html
b- add for past years (Year N, Year N-1, Year N-2) : Balance sheet data, Income statement (P&L) and Cash flow statement in this page.
Or you can chose a company does not exist and you want to start the new company from 0 than you do not need to fill this page with past figures but rather to explain your plans with such company.

In Page 2
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 additional large investment (CAPEX) at your choice at the beginning Year ( you can use the assumption that such CAPEX is equal to the average of total investments last 3 years). On top of that you will continue to amortize the existing past investments using linear amortization or you will amortize only new CAPEX if the company starts from 0.
Financial plan Year 1 Year 2 Year 3 Year 4 Year 5
Key assumptions
Sales increase/decrease
Income tax 30% 30% 30% 30% 30%
Interest rate on long term financing (loan)
Coupon rate on long term financing (bonds)
payout ratio 10% 10% 10% 10% 10%
Number of stocks of the company

Sales Increase / Decrease assumption can be at student’s decision or using some other assumptions, average of last years, CPI, etc. Interest rate on Long term financing (bank loan) assumption has to be explained. You can eventually take the existing cost of financing of the company or interest rate which correspond to the same profile of risk and based on the maturity of your loan.
In Page 3
Make an Amortization calculating page for all your existing 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+1 to the end of your project.

In 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 / decrease. 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 is estimated at 10% or you can calculate it as explained on lectures. Income tax is estimated at 30%. Year 1 here after correspond to the year N+1.

P&L M$ Year 1 Year 2 Year 3 Year 4 Year 5

Sales
Cost of good sold COGS (70% of sale)
Gross ernings

General and adminstrative cost (10% of COGS)
Amortisation
EBIT

Interists on loan ( only from thre loan & bonds of new CAPEX
Income tax
Net income/Loss
Number of stocks in Milion
EPS

EPS share
Dividends
Retained ernings

In Page 5
(Debt): Based on page 2, make a table of amortization of bank long term loan, calculation of interests and annuities, annual principals using formulas (PMT or other) Use your long term bank loan to finance your investment. Loan has an interest rate. Bank loan is used for the duration of the investment and is repaid during the maturity of your project.
In 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 Payables for current liabilities.
Current assets
Recievables (25% of sales)
Stocks (6% of COGS )
Current liabilities
Payaples ( 8% of COGS )
You can also use the average of past receivables, stocks and payables to project the futures ones.
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.

Cach flow statement (Mil $) Year 1 Year 2 Year 3 Year 4 Year 5

CF from operating activities
Net income/Loss
+ amortization
– Recievables
– Stocks
+ payables
Total CF from operating activities

CF from investment activities
– CAPEX (only 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 activiteis

Total cash at end of period
Discount all your Cash Flow’s (using Cash at end of period and Capex from the beginning of the first year) with WACC assuming two sources of financing (equity and long term bank loan). You have to calculate WACC in this Page. The cost of equity can be calculated using different methods you have seen on lectures (CAPM or DDM models). Calculate the shareholder’s cost using the DDM model (Gordon) or CAPM model with your assumptions of risk free, market rate and Beta. For Long term bank Loan use the interest rate you estimate or the lowest interest rate from the financial statement of the existing Loans. Analyse the optimal capital structure. What would you do as a CFO to improve the capital structure and to increase the dividends and consequently the company value ?

In Page 7
(Scenarios): Make a page by using the CF’s obtained from the page 6 including the initial investment (CAPEX). -Consider these CF’s as base case scenario. For Base case scenario calculate NPV, IRR and PI. -Below, add the worst case scenario where you have an increase of your investment by 20%, and all CF’s (each of CF) decrease by 5%. For Worst case scenario calculate NPV, IRR and PI. -Add a best case scenario where you can reduce the investment by 12% and increase all the CF’s (each of CF) by 6%. For Best case scenario calculate NPV, IRR and PI. Analyze the profitability of your investment in all scenarios and explain the obtained results from NPV, IRR and PI. What would you do as a CFO to improve the profitability of your investment in each scenario ?
In Page 8
(Financial Management in Sport): On this page, make an analysis of the results you got. Apply the Financial Management in Sport to understand your company’s status, value, risk, return for shareholders and business success and all shortly describe. Calculate the value of stock price with the help of discounted Dividends. If you have a start up company explain your projections, risks, assumptions and try to apply the tools you have learned on all lectures in order to determine whether your company investment in your project is profitable or not and how to make it more profitable and how to increase the net incomes, future earnings for owners and consequently the value of company.
In Page 9
(Conclusion): On page 9, “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 Bank loan, pay dividends to shareholders, and ensure that IRR is greater than WACC. What are the risks (operational/financial etc) of your Company ? Once you calculate the NPV,IRR and PI 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 profitability and the company’s value ?

Notes:
– Please use all information in Link of the company stc.com.sa https://www.stc.com.sa/content/stcgroupwebsite/sa/en/investors/financial-reports/reports/annual-reports.html Because all info you need, will find in this link.
Or you can chose another company on your choice or one company does not exist and you want to start the new company from 0 than you do not need to fill this page with past figures but rather to explain your plans with such company.
– I need an Introduction, an Explanation, and a Conclusion –
-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.
-Explain your sources
– First projection year is N+1 (or Year 1)
– 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 etc)
– Please put data sources and to explain where have you found your datas and inputs Thanks