Through our seven wholesale branches and three luxury showrooms, we are in the business of wholesaling top-quality plumbing and other products to our customers with a level of service recognized throughout the industry as exceptional.
We seek a BI expert from Experfy to produce a model for us to build some net profit analysis reports in Tableau.
Attached you will find the header lines for the data that we submit. The “Sales Data” tab contains the data that is pulled directly from our SQL database. We pull this information and submit monthly. The Expenses tab shows the header lines from the reports we submit showing our expenses. This information is pulled, calculated monthly except for the ‘customer rebate’ and ‘outside sales wages’, which are calculated monthly.
Details by Tab:
Sales Data:
- Invoice Header: Details the per invoice sales data
- Invoice Lines: Details the per line sales data
- Direct Shipment: Identifies Direct ship invoices – allocation of expenses differs for these invoices
- Customer: Details customer information
- Users: Details writer and sales rep information
- Product: Details product information
- Category Map: Profides a hierarchy of our product distribution (Market, Group, Price Line)
- Vendor: Details Supplier data
This data needs to be linked to provide various multidimensional reports based on Customer, Product, Branch, SalesRep and or Writer. This is a very simple model to build and we already utilizing this in a number of ways.
Expenses: calculated by Branch (price or shipping)
GL Code: Summary of GL expenses into simple work functions that will be assigned to the associated sales function
Credit Card Fee: Summary of credit card fees earned by customer
Customer Discount: Summary of payment terms discounts taken by customer
Purchases Discount and Rebate: Two separate reports summarizing the purchase discounts taken and rebates earned by price line.
Outside Sales Wages: Total annual earnings summarized by outside sales rep
Customer Rebate: Summary of rebates paid out by customer.
The expenses file will need to be developed for each year. With the allocation calculation levels being ‘brought forward’ into the current period if there is no data available. For example; fiscal year expenses calculations from 2015 will be used to calculate and allocate expenses in 2016 until we add data to the expense model at the end of Q1 2016. 2015 Sales would utilize 2015 expense calculations, allowing for year over year comparisons.
The expenses will be allocated to the sales data following these rules.
CoGS-Other: Calculated and assigned as a % of COGS per price branch
Fleet Expenses: This represents our internal trucking expenses. Calculation and assigning these expenses are the most involved. We need to determine a per shipment charge for each shipping branch. A shipment will be defined as all invoices delivered to a customer on a single ship via on a single day. So four invoices, shipped to a single customer from one branch on one day on the same ship via would count as one shipment. So the individual branches Fleet charge would be calculated by dividing the total expense value by the calculated total number of shipments as defined above. Assuming that the Fleet charge is Forty dollars per shipment, the forty dollars would need to be divided amongst the four invoices, prorated by the total CoGS of the invoices
Shipping and Delivery: This line is similar to Fleet expenses above, only it represents 3rd party freight providers
G&A Assigned: this is a total by branch of the fixed expenses. These expenses will need to be allocated by invoice by price branch
Order Entry Expense: Total sales order entry expenses per branch to be calculated and assigned per line keyed.
Selling Expense: By price branch assigned to sales as a % of COGS
Warehouse and Inventory Expense: Total warehouse expenses per shipping branch and assigned per line picked.
Credit Card Fees, Customer Discounts and Customer Rebates will be assigned to the specific customer’s accounts as a percent of their sales
Purchase Discount and Rebate will be applied as a percent of the COGS at the price line level
Outside Sales Wages – calculated as a % of GP$ earned and assigned to the invoices headers that the Rep is named as the Outside Salesperson.
Once these two areas are merged, I will need to be able to create reports showing the total sales, cogs, rebate, expense for each line of sales data. Items that are assigned at the invoice level would need to be assigned on a prorated basis to the individual lines on the invoice, using COGS as the basis. This will allow us to calculate, at an extreme granular basis, the net profits earned on every single product for every individual customer. We should also then be able to build up summary reports at the Writer, sales rep, branch level, as well as at the product, price line, and buyline level. The inclusion of date data on the expense level would permit the calculation of year over year delta reports.
Some key reports we are utilizing now include:
- Multi-Dimensional P&L report at the Rep, Customer, Branch, or Product level. The summary tab/X axis would be defined by the parameter chosen (Ship date, branch etc) (see Base Report Tab for example).
- Ranking reports by customer, rep, product and price line.
- Year over year delta reports (Customer, rep, product, branch etc)
- Summary Reports of Top/Bottom 5 accounts filtered by any of the above parameters.
I have included a sample of one of the Multi-dimensional P&L reports that we produce currently on the third tab of the excel sheet.
I am thinking that we would utilize these data sets to generate a monthly ‘flat file’ that would provide the base for running our traditional reports off of.
The deliverable for this project would be Tableau dashboards that replace all third-party tools we are using. We are already using Tableau as one our primary reporting tools. It is connected directly to our ERP system.
Please provide us with an estimate of what this would take in terms of effort and cost. Please also provide your approach and past experience. Given this is a relatively large project, we would prefer that it be performed in phases, starting with a proof of concept.
