Power BI Consultation & Implementation
Budget: $1,500 – $3,000 AUD
Brief:
- Provide advice on best MS licensing to build Power BI reporting environment. There will be initially two users who will view the Power BI reports.
- Design a Power BI reporting process where source data is provided by external accounting package software weekly for month to date period and saved to a folder on our existing SharePoint site.
- Reports will be set to auto run and export from accounting package and import to folder on SharePoint site every Monday morning (external accounting packing report generation and export not part of scope of works) and immediately following month end.
- Setup data transformations and calculations to enable data presentation in Power BI tables. In doing this, there is a need to setup so that items can be added and employees can be added and the data transformations still work.
- Configure Power BI scheduled refresh so that reports update automatically when new data is added.
- Design Power BI reports, charts and graphs to best display the data in consultation with us.
- At project end, provide an overview document outlining a summary of the design of the environment and process that can be referred to by future designers who may work on the space and need an overview of how it works and fits together.
- Future project 1 (not part of the scope of this project but may need to be considered for data organisation). Setup of Metrics and Scorecards in Power BI and use exported reports from accounting package to track monthly progress towards monthly targets.
- Future project 2 (not part of the scope of this project but may need to be considered for data organisation). Development of a Power App that runs on a tablet and allows a person to walk around the factory entering values onto the tablet that populates Fields in the ProductionWorkSheet.
Overview:
- There are two separate companies and accounting files, Co-Packers and Spacer Company. Operationally, we are a single company so wish to consolidate the reporting and display in Power BI.
- Source reporting comes from our accounting package. Our desired process is that we will export reports each week and at month end in a consistent format and save to a designated folder in our SharePoint site. We would like Power BI to look at this folder and these reports and update each time a new set of reports is added. We will follow a naming convention for the reports as advised.
- If possible, where indicated, there are to be target values for each metric. These are to be set/stored in a "KPI Targets" file. If, in the KPI Targets file the entry is "no target" then there is to be no display of target. If targets are updated, we understand that this will update on all charts for all periods.
- Data to be displayed in graphs/charts as per the opinion of the designer in consultation with us. If possible we would like to be able to drill through to see the underlying data table the graph/chart is based upon to verify data.
- For month to date comparisons with historical periods, the historical data should be treated as pro-rata data. E.g. if importing a reporting set that is for the current month to the 17th of February, the comparative period should be 17/28 of the February report totals for prior corresponding periods.
- We will generate 2 years of data to begin via monthly date range reports for the past two years.
Section 1 - Sales Metrics
- Metric 1
○ Sales Vs prior period - Source SpacerP&L <Total Income> plus CoPackP&L<Total Income> Less CoPackP&L<Sales-Spacer>
§ Target - Growth%
- Metric 2
○ $GP Vs prior period - Source SpacerP&L <Gross Profit> less CoPackP&L<Sales-Spacer> plus CoPackP&L<Gross Profit>
§ Target Growth%
- Metric 3
○ GP% Vs prior period - Calculated $GP from graph 2 / $Sales from metric 1
§ Target - fixed%
- Metric 4
○ Top sales volume lines
For sales values and identification of sales volume lines we would like to report on, see green highlight sections in CoPackItems and SpacerCoItems.
CoPack sections; Assembly, Bulk Packaging Pricing. SpacerCo sections; Conduit Spacers, Assembly.
Rank sales by "Amount" for two companies' combined to identify top sales value lines. (Note the need to sum pallet and bag lines to give totals. The best way to do this is to remove "Bag" and "Plt" from Inventory code and sum. It is possible for there to be additional lines where bag/pallet sales occur to those listed in the sample report in future periods, so this needs to be considered in the data transformation setup.)
Section 2 - Production metrics
- Metric 1
○ Injection and Blow moulding capacity utilisation - Source ProductionWorkSheet sum<Tab Inj_BlowMouldProd column V> ratio to <Tab Rates cell B12>
§ Target - fixed%
- Metric 2
○ Router machine capacity utilisation - Source ProductionWorkSheet sum<Tab Other cell B1>
§ Target - fixed%
- Metric 3
○ Paint line capacity utilisation - Source ProductionWorkSheet sum<Tab Other cell B2>
§ Target - fixed%
○ Paint line - total number of colour runs - Source ProductionWorkSheet sum<Tab Other cell B4>
○ Paint line - total number of units - Source ProductionWorkSheet sum<Tab Other cell B3>
Section 3 Expense metrics
- Metric 1
○ Labour cost Vs prior period pro rata - Source CoPackPayroll <Adjusted Gross Pay> Total column Plus CoPackPayroll <Superannuation Guarantee and Contributions> Total column. No labour costs in Spacer Co. Weekly pay period starting on Thursday, finishing on Wednesday. (when setting up data transformations, need to be mindful that employees may be added in the future). Would like to be able to filter the report in Power BI by employee as well as date range.
- Metric 2
○ Labour cost ratio to $GP - Calculated
§ Target - fixed%
- Metric 3
○ Electricity Vs prior period - Source CoPackP&L<Electricity> (no electricity costs in Spacer Co)
- Metric 4
○ Electricity ratio to $GP - Calculated
○ Target - fixed%
- Metric 5
○ Freight expense less freight revenue Vs prior period - Source SpacerP&L <Freight and Cartage> plus CoPackP&L<Freight and Cartage> Less CoPackP&L<Freight> (this is in Income section) Less SpacerP&L <Freight Income>
- Metric 6
○ External supplier purchases (exclude Dulux, exclude injection moulding) source CoPackAP. Exclude <DULUX Group (Australia) Pty Ltd> & <Injection Moulding>. Rank by value.
- Metric 7
○ Labour cost ratio to production - Calculated
§ Labour cost divided by total units produced. To find units produced see ProductionWorkSheet <Tab Inj_BlowMouldProd column U> and ProductionWorkSheet <Tab Other cell B3>. Would like to be able to filter the report by either Inj_BlowMouldProd column U total or ProductionWorkSheet <Tab Other cell B3>.
Section 4 Inventory
- Metric 1
○ Inventory value total snap shot - Source SpacerSOH <Total> Asset Value column Plus CoPackSOH <Total> Asset Value column. Need to be able to see underlying data that contributes to the total. Units and value.
§ Target - fixed value$
- Metric 2
○ Top sales volume lines weeks in stock snap shot, specific items only. Calculation.
For sales values, see green highlight in CoPackItems and SpacerCoItems. Need to be able to add and remove from this list in the future if required.
(Note the need to sum pallet and bag lines to give totals. The best way to do this is to remove "Bag" and "Plt" from Inventory code and sum. It is possible for there to be additional lines where bag/pallet sales occur to those listed in the sample report in future periods.)
For the green highlight items, divide sales amount by Asset Value (column F) in CoPackSOH and SpacerCoSOH to give "weeks in stock" value.
- Metric 3
○ Raw material volume lines weeks in stock snap shot. Source SpacerSOH and CoPackSOH. Display by Asset Value (column F) for the below lines only. Need to have ability to add/remove lines.
§ SpacerCoSOH Celluka Sheet 15mm
§ CoPackSOH Innoplus
§ CoPackSOH 71437
§ CoPackSOH N1/1150
§ CoPackSOH Black Resin - PPA
§ CoPackSOH RUPP080BLACK
§ CoPackSOH BF970MO
Section 5 Customers
- Metric 1
○ AR snapshot. To display as per raw data following consolidation of CoPackAR and SpacerCoAR reports - current/31-60/61-90/Total.
- Metric 2
○ Top external customer sales for two companies consolidated
Sum DULUX Gp & Dulux Parchem and treat as a single customer.
Exclude INJECT & The Spacer Company
Group different outlets of the one customer e.g. Haymans, MMEM, Rexel. May be more customers to be grouped in subsequent periods. If cannot automate, mechanism will need to be provided for manual grouping to occur.
- Metric 3
○ Dulux Vs Total sales less Dulux sales. Bar chart.
§ Sum DULUX Gs (Australia) Pty Ltd and Dulux Parchem.
§ Sum all other customers excluding INJECT, DULUX Gs (Australia) Pty Ltd and Dulux Parchem.
- Metric 4
○ Total Wheels sales Vs Total Spacers sales.
§ Wheels reported by CoPackITEMS Total Assembly Column C (highlighted yellow)
Spacer reported by SpacerITEMS Total Conduit Spacers Colum C plus Total Assembly Column C (highlighted yellow)
Sample source documents uploaded only. Full set to be provided to successful bidder
- Provide advice on best MS licensing to build Power BI reporting environment. There will be initially two users who will view the Power BI reports.
- Design a Power BI reporting process where source data is provided by external accounting package software weekly for month to date period and saved to a folder on our existing SharePoint site.
- Reports will be set to auto run and export from accounting package and import to folder on SharePoint site every Monday morning (external accounting packing report generation and export not part of scope of works) and immediately following month end.
- Setup data transformations and calculations to enable data presentation in Power BI tables. In doing this, there is a need to setup so that items can be added and employees can be added and the data transformations still work.
- Configure Power BI scheduled refresh so that reports update automatically when new data is added.
- Design Power BI reports, charts and graphs to best display the data in consultation with us.
- At project end, provide an overview document outlining a summary of the design of the environment and process that can be referred to by future designers who may work on the space and need an overview of how it works and fits together.
- Future project 1 (not part of the scope of this project but may need to be considered for data organisation). Setup of Metrics and Scorecards in Power BI and use exported reports from accounting package to track monthly progress towards monthly targets.
- Future project 2 (not part of the scope of this project but may need to be considered for data organisation). Development of a Power App that runs on a tablet and allows a person to walk around the factory entering values onto the tablet that populates Fields in the ProductionWorkSheet.
Overview:
- There are two separate companies and accounting files, Co-Packers and Spacer Company. Operationally, we are a single company so wish to consolidate the reporting and display in Power BI.
- Source reporting comes from our accounting package. Our desired process is that we will export reports each week and at month end in a consistent format and save to a designated folder in our SharePoint site. We would like Power BI to look at this folder and these reports and update each time a new set of reports is added. We will follow a naming convention for the reports as advised.
- If possible, where indicated, there are to be target values for each metric. These are to be set/stored in a "KPI Targets" file. If, in the KPI Targets file the entry is "no target" then there is to be no display of target. If targets are updated, we understand that this will update on all charts for all periods.
- Data to be displayed in graphs/charts as per the opinion of the designer in consultation with us. If possible we would like to be able to drill through to see the underlying data table the graph/chart is based upon to verify data.
- For month to date comparisons with historical periods, the historical data should be treated as pro-rata data. E.g. if importing a reporting set that is for the current month to the 17th of February, the comparative period should be 17/28 of the February report totals for prior corresponding periods.
- We will generate 2 years of data to begin via monthly date range reports for the past two years.
Section 1 - Sales Metrics
- Metric 1
○ Sales Vs prior period - Source SpacerP&L <Total Income> plus CoPackP&L<Total Income> Less CoPackP&L<Sales-Spacer>
§ Target - Growth%
- Metric 2
○ $GP Vs prior period - Source SpacerP&L <Gross Profit> less CoPackP&L<Sales-Spacer> plus CoPackP&L<Gross Profit>
§ Target Growth%
- Metric 3
○ GP% Vs prior period - Calculated $GP from graph 2 / $Sales from metric 1
§ Target - fixed%
- Metric 4
○ Top sales volume lines
For sales values and identification of sales volume lines we would like to report on, see green highlight sections in CoPackItems and SpacerCoItems.
CoPack sections; Assembly, Bulk Packaging Pricing. SpacerCo sections; Conduit Spacers, Assembly.
Rank sales by "Amount" for two companies' combined to identify top sales value lines. (Note the need to sum pallet and bag lines to give totals. The best way to do this is to remove "Bag" and "Plt" from Inventory code and sum. It is possible for there to be additional lines where bag/pallet sales occur to those listed in the sample report in future periods, so this needs to be considered in the data transformation setup.)
Section 2 - Production metrics
- Metric 1
○ Injection and Blow moulding capacity utilisation - Source ProductionWorkSheet sum<Tab Inj_BlowMouldProd column V> ratio to <Tab Rates cell B12>
§ Target - fixed%
- Metric 2
○ Router machine capacity utilisation - Source ProductionWorkSheet sum<Tab Other cell B1>
§ Target - fixed%
- Metric 3
○ Paint line capacity utilisation - Source ProductionWorkSheet sum<Tab Other cell B2>
§ Target - fixed%
○ Paint line - total number of colour runs - Source ProductionWorkSheet sum<Tab Other cell B4>
○ Paint line - total number of units - Source ProductionWorkSheet sum<Tab Other cell B3>
Section 3 Expense metrics
- Metric 1
○ Labour cost Vs prior period pro rata - Source CoPackPayroll <Adjusted Gross Pay> Total column Plus CoPackPayroll <Superannuation Guarantee and Contributions> Total column. No labour costs in Spacer Co. Weekly pay period starting on Thursday, finishing on Wednesday. (when setting up data transformations, need to be mindful that employees may be added in the future). Would like to be able to filter the report in Power BI by employee as well as date range.
- Metric 2
○ Labour cost ratio to $GP - Calculated
§ Target - fixed%
- Metric 3
○ Electricity Vs prior period - Source CoPackP&L<Electricity> (no electricity costs in Spacer Co)
- Metric 4
○ Electricity ratio to $GP - Calculated
○ Target - fixed%
- Metric 5
○ Freight expense less freight revenue Vs prior period - Source SpacerP&L <Freight and Cartage> plus CoPackP&L<Freight and Cartage> Less CoPackP&L<Freight> (this is in Income section) Less SpacerP&L <Freight Income>
- Metric 6
○ External supplier purchases (exclude Dulux, exclude injection moulding) source CoPackAP. Exclude <DULUX Group (Australia) Pty Ltd> & <Injection Moulding>. Rank by value.
- Metric 7
○ Labour cost ratio to production - Calculated
§ Labour cost divided by total units produced. To find units produced see ProductionWorkSheet <Tab Inj_BlowMouldProd column U> and ProductionWorkSheet <Tab Other cell B3>. Would like to be able to filter the report by either Inj_BlowMouldProd column U total or ProductionWorkSheet <Tab Other cell B3>.
Section 4 Inventory
- Metric 1
○ Inventory value total snap shot - Source SpacerSOH <Total> Asset Value column Plus CoPackSOH <Total> Asset Value column. Need to be able to see underlying data that contributes to the total. Units and value.
§ Target - fixed value$
- Metric 2
○ Top sales volume lines weeks in stock snap shot, specific items only. Calculation.
For sales values, see green highlight in CoPackItems and SpacerCoItems. Need to be able to add and remove from this list in the future if required.
(Note the need to sum pallet and bag lines to give totals. The best way to do this is to remove "Bag" and "Plt" from Inventory code and sum. It is possible for there to be additional lines where bag/pallet sales occur to those listed in the sample report in future periods.)
For the green highlight items, divide sales amount by Asset Value (column F) in CoPackSOH and SpacerCoSOH to give "weeks in stock" value.
- Metric 3
○ Raw material volume lines weeks in stock snap shot. Source SpacerSOH and CoPackSOH. Display by Asset Value (column F) for the below lines only. Need to have ability to add/remove lines.
§ SpacerCoSOH Celluka Sheet 15mm
§ CoPackSOH Innoplus
§ CoPackSOH 71437
§ CoPackSOH N1/1150
§ CoPackSOH Black Resin - PPA
§ CoPackSOH RUPP080BLACK
§ CoPackSOH BF970MO
Section 5 Customers
- Metric 1
○ AR snapshot. To display as per raw data following consolidation of CoPackAR and SpacerCoAR reports - current/31-60/61-90/Total.
- Metric 2
○ Top external customer sales for two companies consolidated
Sum DULUX Gp & Dulux Parchem and treat as a single customer.
Exclude INJECT & The Spacer Company
Group different outlets of the one customer e.g. Haymans, MMEM, Rexel. May be more customers to be grouped in subsequent periods. If cannot automate, mechanism will need to be provided for manual grouping to occur.
- Metric 3
○ Dulux Vs Total sales less Dulux sales. Bar chart.
§ Sum DULUX Gs (Australia) Pty Ltd and Dulux Parchem.
§ Sum all other customers excluding INJECT, DULUX Gs (Australia) Pty Ltd and Dulux Parchem.
- Metric 4
○ Total Wheels sales Vs Total Spacers sales.
§ Wheels reported by CoPackITEMS Total Assembly Column C (highlighted yellow)
Spacer reported by SpacerITEMS Total Conduit Spacers Colum C plus Total Assembly Column C (highlighted yellow)
Sample source documents uploaded only. Full set to be provided to successful bidder