| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Forecast Report
Expert: Scottgem
Date: 10/9/2008
Subject: Forecast Report
Question
QUESTION: I have designed a database for forecasting monthly sales volumes. It currently works by adding a start date, size of project and anticipated monthly take against the size. I am looking to report on a rolling monthly basis and have set up a bale with month 1 month 2 etc to month 8. I have set these next to remaining 1, remaining 2 etc also to remaining 8.
I had originally set the trigger to calculate as the start date so that when the project reaches its start date the macro forecasts using the setvalue function.
it sets avalue for month 1 and then takes month 1 away from the original size to calculate remaining 1 and then takes anticipated monthly pour away from remaining 1 to set the value to month 2 etc untill the volume is run down to zero. I can do this if the start date is the trigger but I want it to forecast ahead so that if the start date for example is not for 3 months it will set month 1 and month 2 to zero and then start counting from month 3. I am not an experience access user and am trying to use iif,then, else functions via a macro to set the values in the form. It works if the project has already reached it start date and I have been able to get it to work if the project has started but not if it is wating to start. I am so far quite pleased with the database I have created but this one is now bugging me as I cannot get it to work. I am sure you will need more information but I don't know what to tell you other than the above. I am happy to send you a copy of the relevent form and macro if you need it but don't really know how to do this. PLease help if you can. Alan
ANSWER: Can you give me an idea of what the raw data looks like? and what calc you use for the forecast?
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Hi Scott - Its quite complicated (to me anyway) but I'll give it a go.
step 1 - I enter a project size e.g 1500
step 2 - I enter an anticipated size per month take off e.g 500
step 3 - I enter a start date e.g. 31/10/08
Then the fun begins
My macro first of all sets the project status to either waiting or started using
setvalue [status]
IIf([start date]>date()+1, Waiting, Started)
It then sets the value on Month 1 according to the following
IIf(Date()>[Start Date]+30,0,IIf([Start Date]>Date()+30,0,IIf([Size]-[monthly volume]<=0,[Size],[monthly volume])))
Next it sets the volume remaining after month 1 according to the following
IIf([status]="waiting",[size],IIf([status]="started",IIf([month 1]=0,IIf([Size]-[monthly volume]<=0,0,[Size]-[monthly volume]),[Size]-[month 1])))
Next set month 2
IIf(Date()>[Start Date]+60,0,IIf([Start Date]>Date()+60,0,IIf([remaining 1]-[month 1]<=0,[remaining 1],[monthly volume])))
and so on up to month 8
This is what I want it to do
if start date is more than 30 days old then month 1 = zero as this volume has already gone. if the volume has gone I want remaining to still count it down from the original size in increments equal to the monthly volume. (This bit I have got cracked)
If the start date is more than 30 days in the future I want month 1 to say zero but remaining 1 to equal the size as no volume has yet gone so it is all still remaining.
if the project is after today but not more than 30 days in advance month 1 needs to equal the monthly volume (or the size whichever is smaller) and the remaining 1 wants to count down by the original size - monthly volume but the project remains waiting as it is still yet to start. (this is the bit I can't work out with if then else statements)
Sounds awfully confusing when I put it down like this - no wonder I can't work it out- there must be an easier way than if then else macros.
Help!!! (and don't laugh at my macro)
Thanks, Alan
Answer
If I'm following this, I think you would be better off using a spreadsheet rather than a database. It looks to me like you are starting with a number then incrementing each month. The wrinkle is that you want to predict future projects in comparison to current ones in the same time frame.
So you would have a spreadsheet with 10 monthly columns. If the project starts after the current month you son't increment that column.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer
Ask a Question
|
|
