How to Build a Financial Model in Excel - Full Tutorial for Beginners

Pre 1 Gap analysis against industry standards (benchmarkings)

HP =  High performance potential

LP = Low performance potential

WP =  Weak performance potential

Pre 4 Strategic Map

A summary of integrated strategies 

KPIs OF THE FUNCTIONAL MANAGEMENT

1. Finance        : ROE =  ROS x ATO x  EM
• Return on Equity, Return on sales,  Asset turnover, Equity Multiplier
• ROS growth
• efficiency driven
• ATO growth
• lower assets
• EM improvement 
• Gearing
2. Customers    :   ROS =  np/m% x  quantity 
• Sales growth
• Customers retention
• Customers growth
3. Operations   :  cost per unit =  labour cost + material + overhead
• economies of scale
• efficiency driven lower costs
• shorter throughput
4. Personnel     :   productivity ratios,  profitability per worker, growth per worker
• performance gaps
• competency gaps
• best practices

Pre 3 b -MANAGING CHANGE IN MANAGEMENT

The change could be simple and complex:

1. Simple could be in communication, motivation, more authority and higher targets to the staff concern.
2. Complex change could affect systems, more than one staff, training, change in practices new models, new products or innovative systems etc etc.
3. Best practices are the easiest: copy and paste
4. Innovation in product development or business model development could be challenging.

IMPROVEMENT CHANGES AT ALL  BUSINESS MODELS.                                                                                                                                                                                                     

1. Selling at the right price, volume, costs and targeting the right profit level. (cost volume profit analysis)   MARKETING STRATEGIES
2. Adapting the right trading strategies and providing enough working capital. TRADING STRATEGIES
3. Adopting the right strategies in technology and asset investment CAPITAL EXPENDITURE
4. Financing the above through balancing credit, loan and equity. SOURCES OF FUNDS
5. Review and redefine corporate objective in ROE (Du Pont ROE Chart) ULTIMATE BUSINESS GOALS
6. Linking the targeted with current position through projected cash flows while ensuring liquidity at all times. LIQUIDITY AND FUNDING STRATEGIES
7. Prepare projected Income statement and Balance Sheet. 
• Income statement could be drawn from step 1 
• Balance Sheet could be drawn from Step 2, 3 and 4  ESTIMATE THE RESULTS

Excel templates and models have been pre-prepared to make the training more strategic and less calculation boggling.

Arriffin 012-2786282

Pre 4b - Incorporating strategies in the plans

1. Michael Porter's business strategies reflected in the ROE
• Product differentiation and premium  pricing
• Mass production and cheap pricing strategy
• Niche marketing through one of the above.
2. Trading and Marketing Strategies
• working capital strategies
• 4 Ps in marketing
3. Human Resources Strategies
• training or outsourcing
4. Outsourcing strategies reflected in CVPA
5. Capital Strategies through leverage
6. Fixed Asset Strategies - to own or financed purchase or rent or lease
7. Strategic Mapping - linking, aligning and cascading the strategies

The following strategies are identified in business planning

1. Product and pricing strategies
2. Working capital strategies
3. Fixed capital leverage strategies
4. Sources of financing strategies :  Shares or loans
5. Cash Flow strategies
6. Summarized strategies in return on equity model.

Step 7 Constructing the financial statements

(From costs, volume profit analysis and the ROE models).

1. Proforma Income Statements
• Estimate the size of business i.e volume and value
• drawn from cost volume profit analysis - step 1
• Matching of revenue against the right expenses assured
2. Proforma Balance Sheet
• drawn from working capital - step 2
• drawn from fixed assets - step 3
• drawn from equity - step 4
• financial leverage if any step 4
3. Checking the capacity, health, efficiency and balance of the company through ratio analysis.
• ROE step 5
• Liquidity
• Efficiency
• Gearing
• Potential
• Market performance

Step 6 Cash Flow Projection

1. Cash inflows
• cash sales
• credit sales
2. Cash outflows
• fixed outflows
• variable outflows
3. Total Loan required for the company
4. Loan repayment -  PMT
• interest charged - monthly or yearly rest
5. Cash Balance

If liquidity is affected, revise variables at cost volume profit analysis . Cash flows should preferably be prepared through spreadsheet.

Step 5 Checking the Return on equity objective

THE ROE MODEL IS A GOOD TOOL TO EVALUATE PAST PERFORMANCE OF THE COMPANY.

THE SAME MODEL COULD ALSO BE USED TO CHECK ON THE VIABILITY OF NEW PRODUCTS, PROJECTS OR CHANGES THAT HAS IMPACT ON THE BOTTOM LINE.

1. Checking the ROE objective
• ROS x CTO x EM =   ROE
2. Cascading the ROE to the bottom of the Du Pont Chart
3. Profit Strategy with costs
4. Minimizing Capital employment Strategies
5. The format shows a comprehensive integrated analysis of the business.
6. It shows the linkage and the balancing   of one strategy with that of another.

Step 4 Financed by Equity and borrowed funds

1. This step shows the total capital required to run the business.
2. Working Capital + Fixed Capital  =  Equity and leverage
3. The shareholdings and the leverage ratio as per the market.
4. Leverage could be in the form of debentures, preferred shares or Long term bank loans
5. In Malaysia the ratio can be 1:1 for banks.  1:5 for some other institutions

Step 3 Minimizing investment on fixed assets

NO NEED TO OWN JUST CONTROL

1. Why own when you can just control? i.e rent or lease
2. If you need to own, get it financed or on loans - borrowing is healthy
3. Alternatively rent or lease fixed assets
4. Justify you investment in fixed assets by the discounted cash flow techniques. (IRR or NPV)
5. Ensure no asset is idling or generating lower return.
6. Dispose asset if it is not generating a return higher than the return generated by the bank.
7. Fixed assets are those that are not being traded - normally kept to provide long term services or capacity.

Step 2 Adequate Working Capital - Trading Strategies

1. Based on the trading strategies, your working capital requirement can be estimated.
2. The cycle is a follows: creditors, cash, stocks and debtors.
3. Working capital is used to finance trade
4. Estimate cash in bank needs (days) to satisfy current liabilities
5. just in time stock
6. outstanding debtors in days
7. Outstanding creditors in days..
8. working capital =  currents assets minus current liabilties
9. Maximise creditors - negotiate for better credit terms from suppliers
10. Give priority to the working capital rather than fixed assets

Step 1 Cost volume profit analysis determines the size of business needed

1. Determine the expected size of the business in term of costs volume and price of the product.  
2. Profit planning could be estimated by adopting the same model.
3. The relationship between fixed and variable costs
4. Is the volume and price meet the requirement of the target market?
5. Can you convert fixed costs into variable?
6. Can some of these costs be outsourced?
7. This model could be simulated with changes in the variables such as price or costs or volume.
8. The formula:  Fixed Costs/Contribution margin = units

• or Fixed costs/Contribution margin ratio.= sales value.

Pre 2 - Performance problems and issues

Root cause analysis on critical performance gaps.

• Component ratio analysis on the problem areas.

Sources of the problems and issues.

Finding the solutions and strategies

Putting strategies into a map

designing new business models to achieve the set goals

Simulate the ROE model to get the bottom line impact.

1. Cost volume profit analysis =   Fixed Costs/contribution margin or  Fixed Costs / contribution margin ratio
2. Working Capital =  Current Assets - Current Liabilities
• Current Assets =  Cash + Stocks + Debtors + Other debtors
• Current Liabilities = Bank overdraft + Trade Creditors + Other Creditors
3. Fixed Capital
• Land & Building + Plant & Machinery + Transportation + Computers
4. Equity +  Borrowings =  Working Capital + Fixed Capital
5. P/S x  S/A x  A/E   =  Net Profit Margin x Asset turnover  x Equity Multiplier.
6. Cash flows =  Cash inflows minus Cash outflows = Cash balance
7. Financial Statement =  Income statement + Balance Sheet
• Revenue - Cost of goods sold - Fixed Expense =  Net Income
• Assets - Liabilities  = Equity
• Assets  =  Current Assets + Fixed Assets
• Liabilities =  Current Liabilities +  LT Liabilities

Business Planning in 13 strategic steps

13 steps in drawing up a strategic business plans

1. Performance Gap analysis through bench markings.
2. Critical gaps and root cause analysis
3. Performance Problems and issues identified
4. Market re-positioning
5. Solutions and improvement strategies
6. Integrating into a strategic map
7. Profit model - the initial step
8. Working capital and trading strategies
9. Fixed capital and capacity management
10. Sourcing and balancing capitals
11. Cash flow and liquidity management
12. Meeting the Return on equity as ultimate business goal
13. Estimate the expected results

copyright @ Arriffin Mansor 

We have a regular two day training program on how to prepare a good business plans.

It is a strategic designed. performance based and  model approach.

Please bring your note book as the training is excel software assisted.

It is also a zero based and case study approach training workshop.

Call us now 012-2786282  arriffin@gmail.com

Training need analysis

Pre 3 - Solutions and strategic change

How do you solve performance problems and issues.

1. Root cause analysis
2. component ratio analysis
3. Key Result Areas
4. Cause effect relationship

There are only two types of interventions

1. competency change
2. Strategic or model change