PPC budget calculator

This is my first post ever so will try to be as descriptive as possible but let me know if more details are required.

I have a task to create a paid advertising calculator. We have a different budget for paid advertising every month and want to spend it fully and proportionally. The most at the beginning of the month and less towards the end. Day of the week is also important, as we know that spending on a Monday really pays off, whereas spending on a Saturday/Sunday is not so important.

So I have a budget and 2 % weights: one for day of the month and the other for day of the week. The challenge is to take the budget and split it fully according to the weights so that when the PPC Manager enters the new budget amount at the beginning of the month, she can see how much she has to spend per day.

I tried creating the calculator by week but couldn't quite get there, as it only works if Monday is always the 1st day of the month.

I also tried doing it by day of the month but only could get the day of the month weight included. No idea how to calculate in the weekday weight.

I'm attaching my workbook:

- Columns A-I include the calculation by week (you can see cell E7 implied that in May Monday is 1st month, which is incorrect)
- Columns K-N - tried calculating by day
- Columns P- X - my look ups (% values have been assigned manually - what I think looks right)

Any tips would be hugely appreciated!

I am thinking about a formula but by your logic a month can have 6 weeks! for Example July! so your sheet is out to start with!

Yes you are right there! I think it's best to approach this by day not by week.

Hello JeteMc,

Thank you very much for your warm welcome and the calculated sheet.

All makes sense and it does give me calculated budget by day, weighted by day of the week.

Is it possible to also weight it by day of the month, with lowest % assigned to the beginning of the month and highest to the end of the month?

Many thanks!