Maths related question - please help
Discussion
Hi all,
I am almost at my wits end figuring this out so hopefully someone here could help me out
I'm normally a database geek but I've been tasked to come out with a bespoke calculation tool.
I was presented with 2 things, one is a set of historical data to base the tool on, and the other was an example of what boss expects. This is for a machine to cut big pieces of board down to required sizes.
Historical data consists on the following fields : Month, Length, Width, Qty Bought, Tonnes, Product Group
The existing spreadsheet has a pivot table that groups data first by Product Group then followed by Width (which is totalled). Next to the pivottable there are a few calculated columns to calculate the Waste and few others.
So the layout of the spreadsheet it like so (sorry had to blur due to confidentiality) :
Basically this is to cut a big board down to size. The "Width" column to the left contains all the used widths. The leftmost 3 columns are part of a pivot table while the rest are manually created calculated columns.
In this example the coloured parts are done by a human where he highlights a few groups of boards to buy. So instead of buying all the widths you can by one large one and trim it down to required sized or buy a few small ones and trim to satisfy requirements. The value the the bottom most of a coloured group would be the original width ("Reel Size" column). This width is either split into 2 (as the machine can handle that) or run through the machine as while. The next bottom most value will be the original width trimmed to meet the size requirement and then the waste percentage subsequently calculated from this.
All values in coloured groups are all within the waste tolerances (i.e you cannot trim down too much to have too much waste = uneconomical). Some of the widths are not groups (no colours) hence ignored. Hence by looking at the spreadsheet you can work out groups of reels you need to buy to satisfy demand and manually tweak again if you do not like it.
I can understand how this can be done via a human after going through the spread sheet manuall but I am tasked to come up with a tool (either in Excel or Access) that will automate this process. I cannot seem to think of a best way to do this. What I have now is :
1) Scan the data and pick the smallest possible size needed (Dmax...)
2) Work out how much of the historial demand you can satisfy before needing to trim
3) Trim to half and then repeat #2 to trim again
This is not very efficient obviously as it only assumes you buy a single size then trim them down. Is there a formula/method to closely replicate what the spreadsheet does?
Maybe I am approching to from the wrong angle but I am really stuck and deadline is just round the corner
I am almost at my wits end figuring this out so hopefully someone here could help me out
I'm normally a database geek but I've been tasked to come out with a bespoke calculation tool.I was presented with 2 things, one is a set of historical data to base the tool on, and the other was an example of what boss expects. This is for a machine to cut big pieces of board down to required sizes.
Historical data consists on the following fields : Month, Length, Width, Qty Bought, Tonnes, Product Group
The existing spreadsheet has a pivot table that groups data first by Product Group then followed by Width (which is totalled). Next to the pivottable there are a few calculated columns to calculate the Waste and few others.
So the layout of the spreadsheet it like so (sorry had to blur due to confidentiality) :
Basically this is to cut a big board down to size. The "Width" column to the left contains all the used widths. The leftmost 3 columns are part of a pivot table while the rest are manually created calculated columns.
In this example the coloured parts are done by a human where he highlights a few groups of boards to buy. So instead of buying all the widths you can by one large one and trim it down to required sized or buy a few small ones and trim to satisfy requirements. The value the the bottom most of a coloured group would be the original width ("Reel Size" column). This width is either split into 2 (as the machine can handle that) or run through the machine as while. The next bottom most value will be the original width trimmed to meet the size requirement and then the waste percentage subsequently calculated from this.
All values in coloured groups are all within the waste tolerances (i.e you cannot trim down too much to have too much waste = uneconomical). Some of the widths are not groups (no colours) hence ignored. Hence by looking at the spreadsheet you can work out groups of reels you need to buy to satisfy demand and manually tweak again if you do not like it.
I can understand how this can be done via a human after going through the spread sheet manuall but I am tasked to come up with a tool (either in Excel or Access) that will automate this process. I cannot seem to think of a best way to do this. What I have now is :
1) Scan the data and pick the smallest possible size needed (Dmax...)
2) Work out how much of the historial demand you can satisfy before needing to trim
3) Trim to half and then repeat #2 to trim again
This is not very efficient obviously as it only assumes you buy a single size then trim them down. Is there a formula/method to closely replicate what the spreadsheet does?
Maybe I am approching to from the wrong angle but I am really stuck and deadline is just round the corner
Edited by crazy about cars on Tuesday 12th June 16:20
Edited by crazy about cars on Tuesday 12th June 16:22
Edited by crazy about cars on Tuesday 12th June 16:24
Gassing Station | General Gassing | Top of Page | What's New | My Stuff