• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macro: Demand Forecast

Kristen Sorenson

Kristen Sorenson

New Member
I have a complicated spreadsheet that is currently a manual process that I would like to automate to create more visibility in our workforce. I'm not sure if this is the correct forum, but I'm hoping someone can help. The spreadsheet I attached is an "example: of a forecast for test products that we import from china. The second tab (consumption) contains a list of items that I would like populated in a master spreadsheet (see MASTER). The consumption tab contains a list of items with the layered unit cost, current quantity on hand, qty on purchase order, and total consumption within a specific date range. The information on this tab needs to be populated (preferably by a MACRO on the master spreadsheet). Multiple items are added on a daily basis, so maintaining this master spreadsheet can be quite consuming without a MACRO automating it.

The top of the master spreadsheet columns (A:J) are all pulling information from the consumption tab. If it is not referencing a value on the master tab, it is calculating a value based on the information pulled.

Columns L:Y - is referencing the Monthly Average tab. I would like values aggregated by Year/Month. Then calculating the average of all periods and total for fiscal year.

The information that is pulled in Columns (A:Y) will then be used to populate the forecast in the rows below.

The information updated in each tab is always done on Monday, so the forecast should always start on a Monday example 12/8/14 is a (Monday).

Basically I want a forecast generated for each item on (consumption) tab on the master spreadsheet. If an item exist on the consumption tab, it should populate on the master tab with the information from both the consumption and monthly average. The information from both this information needs to be generated into a forecast. I would like a macro to update the master spreadsheet with items that are listed on the consumption and monthly average spreadsheets to automatically handle this.

My company would be willing to compensate any work or time that is needed to complete this. It would help us significantly.

The file is saying it is to big to upload. Please advise.
 
Hi Kristen ,

This forum allows uploads of files which are less than 1 MB in size.

If your file is bigger than 1 MB , you will have to use any public file-sharing service , such as RapidShare , SpeedyShare , GoogleDocs , DropBox , SkyDrive to upload your file to. After you have uploaded your file to that site , give others permission to access your file , then post the share link here in this same thread.

Narayan
 
Hi Kristen ,

It's still not very clear.

1. Is the file you have uploaded just a sample file , or is it your working file ? The more data a file contains , the better will be the testing for correctness / validity of the formulae / macro.

If you can upload a file which has real data , for hundreds or even thousands of items , that will be nice.

2. Will the data on the consumption tab just keep accumulating or does fresh data overwrite earlier data ? I ask because you have the forecast section beneath the consumption section in your MASTER tab ; how will this work when more rows of data are available in the consumption section ?

3. Which parts of the MASTER tab do you want to automate ? You seem to have formulae for all columns except columns A and B in the consumption section.

Narayan
 
Multiple items are added on a daily basis, so maintaining this master spreadsheet can be quite consuming without a MACRO automating it.

The top of the master spreadsheet columns (A:J) are all pulling information from the consumption tab. If it is not referencing a value on the master tab, it is calculating a value based on the information pulled.

Columns L:Y - is referencing the Monthly Average tab. I would like values aggregated by Year/Month. Then calculating the average of all periods and total for fiscal year.
Click to expand...

Kristen: This sounds like a job for a PivotTable. Are you familiar with Pivots? What version of Excel are you using?
 
1. Is the file you have uploaded just a sample file , or is it your working file ? The more data a file contains , the better will be the testing for correctness / validity of the formulae / macro. I can upload a file with real data, I just can't disclose the description and pricing for items that I include. I will attach that file shortly.

If you can upload a file which has real data , for hundreds or even thousands of items , that will be nice.

2. Will the data on the consumption tab just keep accumulating or does fresh data overwrite earlier data ? I ask because you have the forecast section beneath the consumption section in your MASTER tab ; how will this work when more rows of data are available in the consumption section ? The data on the consumption tab will keep accumulated. This tab is a report that will be ran on a weekly basis and will override existing data on this tab. The column headings however will remain the same. I am not familiar with macros, so if I need to separate the top portion of my master tab and keep the forecast on a different spreadsheet that would work as well. I want the MACRO to populate the forecast for each item that exists on the "consumption" tab.

3. Which parts of the MASTER tab do you want to automate ? You seem to have formulae for all columns except columns A and B in the consumption section.
There is nearly 400-600 items that would be included in this spreadsheet. I want parts added to the master tab if they exist on the consumption tab. If it makes sense to handle this section manually that is fine. For the most part the formula's are working fine.

My MAIN goal is getting the forecast to generate for anything that exists in the TOP portion of the master tab. If the top portion of the master tab is handled manually. Would you be able to create a macro that explodes the forecast for each item that is listed?
Narayan
 
Hi Kristen ,

This forum consists of a lot of happy volunteers !

Unless the application is really a very demanding one , I doubt that payment will be necessary ; in fact you can see the following thread for one such demanding one which was totally without any monetary compensation.

http://chandoo.org/forum/threads/compile-data-and-produce-a-dashboard.15000/

If we can have detailed discussions in this forum , it should not take too much time to come to a decision on how big this task is. When it comes to implementing the task , we will see whether compensation is necessary.

Let me go through what you have posted , and I'll get back with doubts / comments , if any. As and when your data file is ready , please upload it.

Narayan
 
Interesting and it's possible to do.
But, I need to get more information that
I could get image what do You really need and how you would like to use it.

VLEtm
 
I got some ideas.
1.) Do You need Filtering in every tab?
2.) I would like to count different way all 'Master'-tab calculations.
=> if You need to add or delete any of itemlines.
=> and there are a lot of same kind of formulas, 'not good'
3.) 'Forecast Master', Do You need to see 'all or some or just one items' in one time?
=> 'Macro' isn't so so difficult to do if You need to see just one item and
if more ... it need few more lines
4.) How many weeks do You need to 'Forecast'? and from which date?
How about next years?
Or You would like to see all ways previous 12 months 'Master'-tab?
5.) Do You 'manually' update 'Monthly Average'-tab?
How about next year?
6.) Which version of Excel do You use?
.. and so on
Did You get an ideas?
 
I got some ideas.
1.) Do You need Filtering in every tab? It would be nice to be able to filter, but it is not absolutely needed.
2.) I would like to count different way all 'Master'-tab calculations.
=> if You need to add or delete any of itemlines.
=> and there are a lot of same kind of formulas, 'not good'
If you have a better formula to ues in the 'master' tab calculations - you are welcome to change them.
3.) 'Forecast Master', Do You need to see 'all or some or just one items' in one time?
=> 'Macro' isn't so so difficult to do if You need to see just one item and
if more ... it need few more lines
Generally when our buyer is forecasting, he is looking at all items. There are generally 500 items he reviews on a weekly basis. I would prefer to see all items at once and formatted the same way.

4.) How many weeks do You need to 'Forecast'? and from which date?
How about next years? Are longest least time is 6 months. Generally we are forecasting 7 months out. Currently lead time is not populated in the spreadsheet.
Or You would like to see all ways previous 12 months 'Master'-tab?
I'm not sure what you mean, the forecast and master tab are two separate spreadsheets. The previous 12 months is just used as a guide to see where "actual" usage has compared to the past.

5.) Do You 'manually' update 'Monthly Average'-tab? Yes this is updated at the end of every month. The buyer likes to keep a 12 month history, we could keep more if needed.
How about next year?
6.) Which version of Excel do You use? I was on 2013 but just recently changed back to 2010 because of issues I was having. I could try to re-install 2013 if needed.
.. and so on
Did You get an ideas?

Attaching updated file share.

https://docs.google.com/spreadsheets/d/1wf0n32zXVzuZ2Yk6bShyBz2KA8OwUsDL51p3EhekDVI/edit?usp=sharing
 
about #3: 500 items and want to see (500*9 rows) 4500 rows at once?

Could You give any e-mail address?
That we could continue easier?
 
Is there a better way to do this? One way or another, I wanted the macro to create the forecast as demonstrated in the "Forecast Master". It could run for one item at a time, but we would have to make it easy and efficient for the user to know which item he is reviewing.

My email is ksorenson [at] Lci1 [dot] com
 
Last edited by a moderator:
What if I create a product class within these 400 items so that it breaks it up? The buyer could select the "classification" that he wants to review and the macro would run for those items only?
 
Errr....Ok so....

-What is it you're looking for in terms of the actual FC calculation? VBA can iterate any calculation you want, it's just a matter of scoping out something that's applicable to your business.

-You said in your first post, that you're looking for forecast in monthly buckets. Your forecast master is calculating ROC in weekly buckets. Is the calculation "dumb"? Just monthly fc/weeks? Are you using a plant calendar or anything?

-12 months usage seems short for any sort of calculation where there's an expectation of spotting any sort of demand variation or seasonality. That might just be me.
 
You must log in or register to reply here.
Back
Top