Production Financial based metrics dashboard

My work was having an issue rolling up and trending out some production and finance information related to output performance and cost, which was making it hard for them to see over a large period of time how they were performing to plan when it came to per unit cost and unit delivery goals. All the information needed for this existed already, but (and I’m sure this is a big but for a lot of businesses) the information resided in multiple locations and formats making it extremely time consuming to pull together into a cohesive format.

My goal for the dashboard was to create a single location where both the production and finance team could look at together to see how well the company is performing to plan on a weekly/monthly/quarterly basis.

The first part of this project was identifying what information is being used by the two departments:

Finance:

Monthly overhead costs
Direct labor cost broken out by factory
Indirect labor cost broken out by factory

Production:

Number of units completed by factory
Number of standard allowed hours (SAH – how long it should take to complete a unit) completed by each factory

The second part was to find the overlapping information that both Production and Finance teams are measuring themselves against, which in our case was:

$/unit
$/SAH
Direct Labor Cost / SAH – this shows how much money we end up spending on labor over a time period versus how long it should generally take to complete the labor

Once I had a chance to look through how the teams currently use and create the reports that are generated for Production and Finance teams, I start looking into where the information was being pulled from.

On the Production side, a new ERP system (Dynamics AX) has all of the information related to finished goods completed form each production location while Finance uses ADP to manage costs related to Production. My goal was to combine these two locations of information and minimize and changes required by the users in each department. In order to accomplish this task, I had to link into the workbook used by Finance to track biweekly spending and pull information for the transaction log in AX. After getting the data pulled into a single location, I needed to change it into a usable format. I accomplished by creating three separate tables:

Financial table

rolled up all of the direct and indirect labor between all factories
tied in overhead costs
added 10% payroll tax adder

Production table

rolled up daily output into weeks due to all finical data only having visibility down to the week
pulled in SAH information for the styles being made in each factory
Rolled up total production unit output and SAH output

Combined table

This table houses the calculations to show $/SAH and $/Unit on a weekly basis

I created Pivot charts off of the Combined table to show the performance overtime and created two macros to pull the information, refresh the tables, and validate against double counting of unit output.

Attached is an example with some salted data sets the pretty closely represents what we ended up using at filson.