• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting Monthly data into weekly actuals

B

Ben Hale

New Member
I get monthly forecast data which makes me bang my head against the wall as it doesn't fit into actual weeks and we end up making rough calculations.
What I made was a very inelegant solution which takes the lead forecast volume (Weekly tab D1:D17) and using a working day calculation breaks that down into leads per day.

On the daily tab I used a manual way of working out leads per day and get a pivot table to summarise that into actual weeks which I can load into another forecasting model.

It works but I don't like it and wanted to know if there's a more elegant solution to this issue which comes up all the time.

Thanks in advance.
 

Attachments

  • Monthy into Weekly.xlsx
    28 KB · Views: 17
Thank you @NARAYANK991 & @chirayu both using the elegance I was after! I was trying to avoid manual rework as I know that the forecasts will change again and again and......
I appreciate the help - thanks both
 
Welcome. By the way don't think I mentioned this in the file but the Named Range "Holiday" is a dynamic list so technically you can add/remove records from the Holidays column in Lists sheet & you don't need to update the range.

Also the main manual work in the file is essentially updating cell A2 in CALC sheet/ cell A2 in DATA sheet and the Lists sheet where you add/remove Holidays/ make the table for the LOOKUP formula to work. Rest of it is just dragging formulas down really.

I've reattached file with manual bits highlighted yellow
 

Attachments

  • Chirayu's solution.xlsx
    33.2 KB · Views: 7
Last edited:
You must log in or register to reply here.
Back
Top