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.