Data Forecasting

Posted on 2013-08-27
Medium Priority
Last Modified: 2013-08-27

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)
Question by:Shanan212
  • 2
LVL 27

Assisted Solution

aburr earned 200 total points
ID: 39443107
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.
LVL 37

Assisted Solution

TommySzalapski earned 1800 total points
ID: 39443112
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.
LVL 37

Accepted Solution

TommySzalapski earned 1800 total points
ID: 39443123
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.
LVL 13

Author Closing Comment

ID: 39443196
Thank you all! I will be using Tommy's suggestion of fiscal quaters if not normalizing weekly data!

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.

Join & Write a Comment

Foreword (May 2015) This web page has appeared at Google.  It's definitely worth considering! https://www.google.com/about/careers/students/guide-to-technical-development.html How to Know You are Making a Difference at EE In August, 2013, one …
Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question