Automated Booking Template

Hello Experts,

I have sales booking data for 3 years

I am trying to Analyze Increase or decrease in booking at customer Level based on the history, I mean current (2015) Fiscal Year; Fiscal
Month; Fiscal week; Vs Previous (2013/2014) Fiscal Year; Fiscal Month; Fiscal week

Whenever there is significant changes (increase or decrease) in booking behavior it should be highlighted.

If there is a holiday on particular day it should be highlighted (Holiday list for current year is available in the current spreadsheet)

I would like to show comparison between FY 2015 Vs FY 2014; FY 2013 at customer level and overall
e.g.: Average bookings - 2015: 10M
                                     2014: 7M
                                     2013: 9M      

Comparison between Fiscal Week 50 2015 Vs Fiscal Week 50 2014; Fiscal Week 50 2013; at customer level and overall.

If I select a dropdown list or click a macro button the data should populate.

I tried using Pivot table and Slicers. But, stuck in between! Not able to show the comparisons accurately and not sure if we can show all the metrics using Excel. Hence it would of great help if you anyone could tell me which “tool” is best suit for these kind of analysis.

I have attached the file I am working on and looking forward for experts who can help me organizing the existing data, or helping me to create a model using Excel/ VBA script or any other tool which meets my exact needs.

Any suggestions would be greatly appreciated!

I am looking forward for the positive response!!!!!!

Thanks,
Ganesh
Ganesh VijaykumarAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
1. Without a very specific single "Question" here, ...this is just a request for a "Complete Project"
...and as such, ...is too broad in scope to be dealt with in a forum like this.
It is highly recommended that you narrow the focus of this question to one specific, targeted question.
Then post addition, separate new question for any remaining concerns.
2.
I have attached the file I am working on
There is no attachment...?

JeffCoachman
0
Dale FyeCommented:
Access would be an effective tool for this analysis.

with your data, you could configure a form with inputs for ClientID (one or all) and period ("yyyy", "m", "w")  which would allow you to create an aggregate query something like:

SELECT Format([DateField], "yyyy") as Period, SUM([Amount]) as Bookings
FROM yourTable
GROUP BY Format([DateField], "yyyy")

If you wanted this for a particular Client, you would add a WHERE clause

SELECT Format([DateField], "yyyy") as Period, SUM([Amount]) as Bookings
FROM yourTable
WHERE ClientID = 2
GROUP BY Format([DateField], "yyyy")

To take holidays into the mix, you might change that to something like:

SELECT Format([DateField], "yyyy") as Period
, SUM([Amount]) as Bookings
, Count([DateField] as PeriodDays
, SUM(IIF(IsNull(HolidayDate), 0, 1) ) as Holidays
FROM yourTable
LEFT JOIN tbl_Holidays ON yourTable.DateField = tbl_Holidays.HolidayDate
WHERE ClientID = 2
GROUP BY Format([DateField], "yyyy")

Then challenge then becomes the analysis.  What is a "significant" amount (5%, 10%, 15%)?
1

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
Ganesh VijaykumarAuthor Commented:
Thanks!
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
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.