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