What is an Excel Dashboard?
A dashboard is usually a single pageview of reports comprising of charts, tables and other summaries of data that provide a high-level visual overview of your business.
Excel Modelling
Concrete Production Industry
Challenge
This client needed to determine the optimum mode for their concrete product plant. For a given set of inputs, production settings and forecast sales a model could be determined through calculations of expected outputs from each of the modes
The challenge was being able to assess each of the possible modes alongside each other to quickly and simply see which mode was likely to produce the require output of the different products that the plant could produce.
How we help with Dashboards?
Solution
An Excel workbook was developed to import stock levels and forecast sales data. A set of production modes were defined for the plant with each of the modes creating a set of products and waste. Depending on the forecast sales a particular production mode might need to be used.
With all the source data in place and the production modes defined the modelling could begin. The modelling process was automated in Excel through formula's and VBA macros to calculate the outputs for each of the products planned from the production mode.
The model process was designed to allow the user to choose up to 8 different production modes and see the results side by side to quickly and easily identify the most optimum produce mode for the current stock levels and forecast sales.
The key functionality included to Excel Model were:
-
Import stock and sales forecast data
-
Define production modes and expected product output
-
Run "model" across different production modes
-
See results of different production modes side by side.
Outcome & Benefits
The client reported that with the Excel Modelling solution we created in place the concrete production plan was no longer guessing which production mode to select to produce the required product and output to meet the forecast sales.
This benefit alone paid for the cost of the development for the Excel model within 3 months of use.
The model ensured that waste was kept to a minimum and products forecast for sales were always in stock when they were required by clients.
