Forecast based off a changing % of spend

[AdamPriest]

Hi,

I'm trying to forecast daily spend for an advertising campaign and would like to know if it's possible to 'pivot' the data based off a %.

I have four different budgets amounts to share between two products and would like to pivot my data based off different % splits between the two i.e. what would my projected sales be if put 40% of the budget into product A and 60% into Product B. Ideally I'd like to be able to update these %'s on the go and have sheet recalculate as i do so.

My data is based off budget amounts of;

• £250
• £500
• £750
• £1000

An example of my set up is on the image below

• 92 Day campaign
• The daily spend figures are currently calculated off a 50% split, which I've simply done by dividing the budget amount by 92 and then 2 i.e =A6/92/2

Is it instead possible to run the calculations for daily spend off the % in cell B2?

Thanks,

Adam

 

[AlanY]

try

Book1
	A	B
1	A	40%
2	B	60%
3	Days	92
4
5	Forcast	B
6	Budget	Daily
7	250	1.63
8	500	3.26
9	750	4.89
10	1000	6.52
Sheet1

Cell Formulas
Range	Formula
B2	B2	=1-B1
B7:B10	B7	=A7*VLOOKUP($B$5,$A$1:$B$2,2,0)/$B$3

Cells with Data Validation
Cell	Allow	Criteria
B5	List	=$A$1:$A$2

 

[AdamPriest]

Thank you!

 

[AlanY]

you're welcome