• Home
  • Who We Are
    • Where We Have Worked
  • What We Do
    • Board Advisor
    • Interim Management
    • Project Management
  • How We Work
    • Our Values
  • Recent Assignments
    • Case Study - Project Yellow
    • Case Study - Project Olive
    • Case Study - Project Gold
    • Case Study - Project Green
    • Case Study - Project Orange
    • Case Study - Project Blue
    • Example Customised Modelling Solutions
  • Testimonials
  • Resources & Links
  • Presentations
  • Publications Released
  • Blog
  • BBS Docs
  • Contact Form - Enquiries
  • Our Contact Details
Blueprint Business Solutions
Contact Us +61 (0)409-490-530

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
    


Home  Who We Are   How We Work   Our Values   Recent Assignments    Resources & Links    Where We have Worked   Publications   Contact Form or Enquiries   Our Contact Details
What We Do: Interim Management  Board Advisor   Project Management            Presentations   Testimonials                                            Proudly Powered by Keith Bailey (C) 2019