Example Customised Modelling Solutions

Introduction: We have developed fit for purpose and customised modelling solutions in Excel to suit the unique requirements of each business. These have evolved as the businesses lacked the required transparent management information to effectively plan, cost or monitor the business. All examples delivered significant results, improved productivity or greatly reduced risk and errors. We use visual management techniques to enhance information understanding and decisions.

Scope: We use our financial and operational expertise and have developed:
  • Master Schedules - for operational planning.
  • KPI Dashboards (financial and operational).
  • Product Costing Estimating models (integrating product and process features and options).
  • Hourly Rate developments (sometimes requiring P&L redesign using design template below).
  • Sales and Operations Planning (S&OP) process and models.
  • Simplified Financial summaries using the "Dupont" model.
  • Debtor financing draw-down calculations for input to cash flow worksheets.
  • Debtor Finance - pre-implementation assessment scenarios.
  • A combination of these to assist M&A integration decisions and assessments.

Excel development principles applied
  • Using advanced functions, the models are developed to maximise user productivity by simplifying entries. The user does not have to be an Excel expert.
  • Drop down box choice options used.
  • Warning messages or colour codes used to highlight issues automatically.
  • Integrated flow charts.
  • Customised summary reports using pivot tables.

Key Features by Model Type:

a) Master Schedules
  • Daily or weekly schedules by product / process types.
  • Resource loading – highlights under / over loads.
  • Gross material requirements determined – where applicable.
b) Product Costing Estimating Models
  • Integrates process methodologies or IP unique to the business.
  • Selects compatible combinations.
  • Financial and operational summaries - run times, set up time.
  • User focuses on what customer requires, not how to manufacture or process.
  • Integrates packaging material and freight costs where applicable.
Model Type / Example
Industry / Product
Key Outcomes / Features
Financial modelling - 3 way (P&L, Balance Sheet, Cash Flow) and Debtor Finance Examples
Multiple businesses:
  • Manufacturers
  • Services
  • NFP’s
  • Improve on existing business models, or develop to suit
  • Develop cash flow reports
  • Often customised to suit business
  • Includes key financial metrics and ratios
  • Integrated debtor finance draw-downs where required
KPI Dashboards – financial & operational
Multiple businesses:
  • Manufacturers
  • Services
  • NFP’s
  • Graphical summaries of key metrics
  • P&L & Balance Sheet financials
  • Integrates Dupont style summary – where required
  • Highly flexible month range selection summaries
  • Key financial ratios calculated
  • Compares to budgets – where applicable
  • Key exceptions visually highlighted

      Operational Dashboard includes:
  • DIFOT
  • Safety statistics
  • Staff absences / leave plans
  • Contractor & overtime hours
Hourly Rate Development
Multiple businesses:
  • Fabrication
  • Powder coating
  • Various manufacturers
  • Injection & Roto Molding
  • Beverage manufacturer
  • Mine vehicle customisation
  • Redesign of P&L to align costing models, COGS and operational processes
  • Validation of accounting cost allocation
  • Compliance with accounting standards
  • Integrate correct values into estimating models  or standard cost systems
  • Many developments resolved under pricing 
  • Target gross margins to achieve breakeven and net profit objectives
Master Schedule with integrated Sales & Operating Plan
  • Design & build to order mobile compactor manufacturer
  • Contributed to $1M inventory reduction
  • Productivity up 18% by better coordination
  • Robust alignment and synergy with sales, engineering & procurement
  • Focus shifted from reaction to collaborative planning
  • Negated design variations entering fabrication plan
  • Integrated revenue & margin forecasts v budgets
  • Included manning plan by skill type with variable work week
  • Strong traffic light system flagging call to action
  • Integrated dashboard
Product Estimate Costings
  • Building Materials Laminator
  • Manual estimates by product expert deskilled and standardised
  • Poor margin products quickly identified
  • Determines optimal equipment to use, given options
  • Selects compatible substrates, finishes & glue – avoids technically incompatible combination offers
  • Packaging options selected and costed
  • Multiple Freight locations with weights & volumes & packaging calculated
  • Roll change over time and process waste included
  • Production run times and gross materials required per job
Product Estimate Costings
  • Sign Manufacturer
  • Manual estimates by product expert deskilled and standardised
  • Determines optimal equipment to use, given options
  • Poor margin products quickly identified
  • Determines optimal equipment to use, given options
  • Packaging options selected and costed.
  • Multiple Freight locations & packaging calculated
  • Material nesting calculations
  • Compares screen printing to hand applied break points
  • Production run times and gross materials required per job
Master Schedule & high level MRP
  • Beverage Manufacturer & Distributor
  • Product mix & product run modelling
  • Integrated with stock on hand to determine purchase needs
  • Improved product availability
  • Key system data easily imported into model
  • Improved control of long lead time items
  • Stock outs or shelf life issues negated
  • Staff resources and roster planning
Master Schedule
  • Mineralogy Testing Laboratory
  • Forward loading on test equipment
  • Order intake / revenue / margin per week
  • Traffic light system highlighting exceptions
  • Loss making services identified
  • Staff resource & skill technician requirements
Master Schedule
  • Water Testing Laboratory
  • Forward loading on work centres
  • Order intake / revenue plan per week
  • Loss services identified
  • Traffic light system highlighting exceptions
  • Staff resource requirements
  • Prepping aligned to plan reducing lead times and test costs
    Integrated into a new sales & operations planning process