I am looking for some help building a file. The basics are that we sell a product that we can often buy in many different lengths (500', 1k', 5k', etc.). This product is then cut to length requirements and sold to the end user. I would like to accomplish two things with this, first determine which length is the optimal size to order based on historical usage and potential scrap (this would be dependent on carrying costs as well as the amount of unsellable lengths it creates). Second, I would like to have the ability to analyze an order and determine if it is more cost effective to purchase the cut lengths (with a cutting charge), or bring in a full length to cut ourselves. This second part would take into account the amount we have to bring in vs the total amount needed and the amount of "waste" that would create (this amount would be equal to the per foot cost of the remaining length). The idea on this is to make this as user friendly as possible to give the end user as much data in order to make an informed decision. For the purposes of this file, I am not worried about cutting tolerances (I will assume all are to +/- 0).
I have some ideas on how I could do this in parts, but hoping for some direction in tying it all together.
I would compile the raw data listing how much you scrapped, on what date and what length of product it was along with the cost, then you can create a pivot table & chart with slicers that would allow you to visually view the data at each "length" in summary format. You'd be able to see trends as well as totals YTD.
Hope that helps give a little direction. It's a somewhat vague request (even though you gave great detail), but I'm more of a numbers person so I do better when I can get my hands on the data and work it to a usable state.
