Eat More Bacon
Member
As I formulate weekly feed diets, What is a good Excel structure to keep track of changing prices?
For example, If
I have a dozen diets containing 10-20 ingredients each
Ingredient prices are updated (sometimes on a weekly basis)
I want the current price to reflect in diet cost, without changing the diet cost from the previous week.
I want to total the amount of product used totaled each week.
Here is what I have currently.
we.tl
It works but has become extremely slow and I wonder if it is related to some complex formulas or if I inadvertently introduced some other problems? (The file size seems unreasonably large for the amount of data it contains.)
Every week, I copy rows 1-62 on feed order sheet and duplicate it. Thus my most current diets are at the top of the sheet and the older ones are lower.
Each diet consists of several
Ing 1 Prod To Use Price Amount Used Batch 1 ...2 Hidden Column After 8 to total Amount Used and Cost
Ing 2 Prod To Use Price Amount Used Batch 1 ...2 Hidden Column After 8 to total Amount Used and Cost
To get the cost of each ingredient, I check the date and the ingredient name and Lookup the current cost on Ingredient Price List
To track inventory, I total all the estimated usage in HL and the hidden columns for actual usage in HK. The formula in HO and HP use these numbers.
Because of the structure of the spreadsheet, the formula in HK and HL are quite complex. Can someone point me to an example of a simplified spreadsheet or formula?
Is there a better method of tracking frequent price changes rather than having each weeks price listed as I have on the ingredient price sheet?
If someone knows of an example spreadsheet for any type of production work involving multiple pieces where inventory and pricing is tracked, it would be very helpful. Or suggestions how to improve mine to improve speed and usability.
Thanks!
For example, If
I have a dozen diets containing 10-20 ingredients each
Ingredient prices are updated (sometimes on a weekly basis)
I want the current price to reflect in diet cost, without changing the diet cost from the previous week.
I want to total the amount of product used totaled each week.
Here is what I have currently.
Feed Mixing Sheets for VTM Diets 2023 reduced.xls
1 file sent via WeTransfer, the simplest way to send your files around the world
we.tl
Every week, I copy rows 1-62 on feed order sheet and duplicate it. Thus my most current diets are at the top of the sheet and the older ones are lower.
Each diet consists of several
Ing 1 Prod To Use Price Amount Used Batch 1 ...2 Hidden Column After 8 to total Amount Used and Cost
Ing 2 Prod To Use Price Amount Used Batch 1 ...2 Hidden Column After 8 to total Amount Used and Cost
To get the cost of each ingredient, I check the date and the ingredient name and Lookup the current cost on Ingredient Price List
To track inventory, I total all the estimated usage in HL and the hidden columns for actual usage in HK. The formula in HO and HP use these numbers.
Because of the structure of the spreadsheet, the formula in HK and HL are quite complex. Can someone point me to an example of a simplified spreadsheet or formula?
Is there a better method of tracking frequent price changes rather than having each weeks price listed as I have on the ingredient price sheet?
If someone knows of an example spreadsheet for any type of production work involving multiple pieces where inventory and pricing is tracked, it would be very helpful. Or suggestions how to improve mine to improve speed and usability.
Thanks!