Duplicating sales trend for next year using Excel

[PP3321]

I have a data of daily sales in September 2015.

My requirement is to copy the trend for next year, i.e. September 2016
*Please find the attached.

Problems is that weekends change in 2016.
So I can not just copy & past from 2015 data.

What would analysis experts do in this case?

Should I instead:

-get weekly trend?
-get trend by day, rather than weekday name?
-get moving average?

Thanking you in advance...

 

[PP3321]

The requirement is to automate the calculation on Excel.

So maybe I can do Index&Match using week number.

For example...first Monday of month, 2nd Monday of the month...etc

 

[PP3321]

I tried vlookup. It works fine, but towards the end of month, if the week 5 does not exist in next year, it would not have any value...

 

[Chihiro]

Well, since you are doing forecasting based on week and day of week. You really need to have data for matching set.

Ex. For each month you need to find first Sunday (or Monday) of the month. If the Sunday does not fall on 1st of the month then, you extend range by x number of days backward. And do same for end of month (for Saturday or Sunday), but forward.

FYI - you have formula error in J2. You excluded J3 in range, throwing off % Daily Contribution calc.

Another approach. Is to calculate avg contribution of each day of week for a given month/segment. Then use that for forecasting.

Note: For an accurate forecast you'd need to build a model that suites your business. See link for what's typically needed.
http://chandoo.org/forum/threads/projecting-trends.28776/#post-172271

 

[Chihiro]

For simple time series forecast for data exhibiting seasonal trend. Do a search on "Seasonal naïve method" & "Drift method". I think the methods matches what you are looking to accomplish here.

 

[Hui]

Your basic data with and without weekends


Looking at the above there appears to me no use trying to use the monthly data for any prediction.

To look at the Daily data you will need to chart all the monday's together and see if there is a trend, then the same for Tues, Wed etc

 

[Hui]

If you look at the Week Days


I'm also not sure that you have any suitable trends?

 

[PP3321]

@Chihiro
Thank you! I googled as you suggested and found this useful article.
https://www.otexts.org/fpp/2/3

@Hui
Thank you! I learned to graph the data, to look for trend and patterns...