Rolling Quaterly Forecast in Access

I am trying to build a rolling forecast input form, which should display the past quarter of actuals + 4 future quarters (the current one + 3 future quarters.

How should I:
1) structure the forecasting table?
2) structure the actuals table
2) create the query to display a rolling view of 5 quarters?
3) build the form to display the past quarter data and allow entries for the current quarter + the following 3?

Thank you
fitalianoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see that you're new to EE, so welcome!

Your question is quite broad and not terribly specific about any one thing. EE works best when you ask a single, focused question and then move along from there. Asking 4 questions at once, even though they are somewhat related, doesn't really produce good results, and many Experts won't even look at a question this broad.

The structure of your database would depend entirely on your requirements. There is no predefined structure to this sort of thing, and in fact you typically would NOT have a "forecasting" table and an "actual" table. Your rolling forecast would be built using data from other tables.

Also, often this sort of thing is best done in Excel. Access is great at storing relational data, but Excel is much better at handling numbers and financial stuff.

I've built forecast sceanrios for financial interests in the past, and the best method seems to use a temporary table to store the flattened data (for reporting), while keeping the normalized data intact. This works well, but it a very advanced subject (especially if you need to allow users to interact with that flattened data).

I was not overly involved in the initial structure of the tables, but in general they looked something like this:

tBudget
------------------
lBudgetID
sBudgetDescription
dBudgetSTart
dBudgetEnd
cBudgetAmount

tBudgetDetails
------------------------
lBudgetDetailID
lBudgetID
sItemDesc
cItemAmount (+ or - depending on the item)

tActualItems
-----------------------
lActItemID
lBudgetID
dItemDate
sItemDesc
cItemAmount (+/-)

and so on ...

This is a VERY sketchy structure, and not one that would be useful for many things. As Budgetary and Forecasting requirements are wildly different for different companies (and even for different departments/projects in the same company) it's tough to come up with a single structure that works.


0
fitalianoAuthor Commented:
Ok thanks for the advice.
I have the following Tables:

QUARTERS TABLE where I listed all the quarters until 2020 (pretty much everytihng I need) to make calculation easier if I want to pinpoint a quarter later on. The table has the following fields
Number
Quarter
Start Date
End Date

ACTIVITIES TABLE
Where I have a list of predefined activities

GROUPS TABLE
Where I have a list of predefined groups

ACTIVITIES FORECAST TABLE with the following fields
Groups
Activities
Quarters
Low Estimates
High Estimates

Groups are linked to the GROUPS TABLE with a 1 to many relationship
Activities are linked to the ACTIVITIES TABLE with a 1 to many relationship
Quarters are linked to the QUARTERS TABLE with a 1 to many relationship

I want to create an input form that displays, for a specific group, the list of activities in the rows and the current quarter + the 3 following quarters with high and low estimates in thre columns

The input form should look like:
                                          2010-Q1                                      2010-Q2
                         Low Estimate | High Estimate      Low Estimate | High Estimate
Activity 1                (Input forecast number)             (Input forecast number)
Activity 2                (Input forecast number)             (Input forecast number)

I want to be able to input high and low estimates for each of the displayed quarters
I want to be able to chose the current quarter
0
fitalianoAuthor Commented:
So... is anyone answering the question above?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you created your input form? If so, what issues are you having with it?

The interface you're designing isn't terribly conducive to Access; this is more of a "number crunching" scenario, and might be better suited for Excel.

Also, please understand that EE is staffed entirely by volunteers, and posting comments like "So... is anyone answering the question above?" will rub many of the Experts the wrong way. If you need assistance, you can use the Request Assistance link/button (near your original question text) to ask the Moderators for assistance.

And again: What you're asking is quite broad, and really would be best suited broken up into several questions. Rolling forecast reports are very difficult for Access to handle, since your data must be moved from a normalized state (which yours isn't) to a flattened state, then back into the normalized state.

I'll be out of town for the next few days so I'd suggest you use the Request Attention link as suggested above.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fitalianoAuthor Commented:
Solution not provided. I got a few over the head for the way I posted my questions but nithing more.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.