Data Forecasting


I have a set of financial data for a company for the past 3 years. The company's most profitable days in a 5-business-day week is Monday (most), Tuesday (next most)....and the Friday being the least profitable day.

However, overall, this balances out (as nearly 70% of the revenues and costs get registered on Monday)

Now the problem is, if a Month ends on a Friday, some of its revenues goes to Mondays and bloats next month's revenues or vice-versa.

How can I create a forecasting model using historic data to see the 'true' revenue vs costs?(calculated of course)
LVL 13
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.

If the daily data is used for foercasting you have no problem. You already can see that Mondays are different from Fridays.
If monthly data is important, average the daily data to get the monthly data to be used in forecasting.
You could look at each week as a whole and normalize the data by week. That is, take the average revenue for each day of that week and use that in the month calculations.

You may also need to take into account the different number of business days in the months. Some months with 30 days will have 20 business days (1st is Saturday), others will have 22 (1st is Monday-Thursday), and of course some will have 21 if 1st is Friday.
aburr, that might not help. The issue is when the week boundary crosses the month boundary.

Any time you try to calculate stuff by month you will run into issues. Fiscal quarters are probably a better method. The year is divided into 4 13 week periods. (There's still that issue of the extra day (or two in leap years)) but it's much better.
For finer granularity, you can look at 4 week periods and forget the notion of a month entirely (you'll have 13 "fiscal months" per year).

If you are forced to do it by month, then normalizing each week is probably the best you can do.

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
Shanan212Author Commented:
Thank you all! I will be using Tommy's suggestion of fiscal quaters if not normalizing weekly data!
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
Math / Science

From novice to tech pro — start learning today.