mikeman1489
New Member
- Joined
- Apr 22, 2020
- Messages
- 2
- Office Version
- 2019
- Platform
- MacOS
Context: I built a Forecast model in Excel that gets updated weekly. Currently I am saving off monthly snapshots as separate worksheets within the Forecast workbook, to archive. Throughout the week, as I make changes to the forecast I copy the forecasts line by line (~25 SKUs) into a separate Inventory Planning Model Excel file, which in turn updates the SKUs' Weeks of Supply, Purchase Orders needed, etc.
Problem/Questions: It's an inefficient process that involves a lot of copying & pasting between two large files. I've ran into problems where I forgot to copy over some SKU forecasts, and where lack of regular forecast archiving makes it difficult to trace the root of an inventory issue. I would like the Forecast file to remain separate from the inventory model, while also being connected to it w/minimal manipulation needed.
1) I've attempted to create a table in the forecast file, and thought of trying to use the table in the inventory plan to pull in the forecast automatically, but not sure if that's the best way to pull in the updated forecast data? Table, pivot table, other? One of the confusing parts of creating the table was that it wasn't clear whether I needed to set up the data with a SUMIF, etc or if it should automatically do this for me.
2) What are good solutions for archiving data in Excel? Database? Just saving off different versions of the file? I want to save the forecasts in a regular cadence.
Problem/Questions: It's an inefficient process that involves a lot of copying & pasting between two large files. I've ran into problems where I forgot to copy over some SKU forecasts, and where lack of regular forecast archiving makes it difficult to trace the root of an inventory issue. I would like the Forecast file to remain separate from the inventory model, while also being connected to it w/minimal manipulation needed.
1) I've attempted to create a table in the forecast file, and thought of trying to use the table in the inventory plan to pull in the forecast automatically, but not sure if that's the best way to pull in the updated forecast data? Table, pivot table, other? One of the confusing parts of creating the table was that it wasn't clear whether I needed to set up the data with a SUMIF, etc or if it should automatically do this for me.
2) What are good solutions for archiving data in Excel? Database? Just saving off different versions of the file? I want to save the forecasts in a regular cadence.
