I have been tasked to set up a new costing model for a department at my company.
The setup in the department is as follows:
All operations are based on work orders. Every time there is a new piece of equipment coming into their production line there is a new work order (WO) created in the ERP. At the same time there is a line created, with the same WO number, in a WO tracker outside the ERP
My available sources for data is
- ERP which contains info on account, period, amount, quantity, WO,
- WO tracker which contains info on WO, serial number, part number, customer, project etc.
And lastly, a table from our ERP where there is info on the status of the WO (Started, in progress, finished)
I have uploaded an excel which show what type of data i have available,
What I want to end up with is a model where I can measure how much money is spent every time a piece of equipment has gone through the production line. I.e. by part number, serial number, check for variances between customers etc…
My current challenge is is how do i get started, and also I’m a bit worried about data quality.
Mainly because there seems to be quite a few cases where there are multiple unique Serial/part numbers within one WO, meaning that there i no way to know what cost belongs to which serial/part number within the WO.
Any tips on how to get started would be highly appreciated!
scenario.xlsx (12.1 KB)