Formula For Updating Sales Day by Day to Reach Target

[daniellej93]

Hi! I work in the food service industry as a store manager, and I'm trying to find a formula in Excel that will allow me to determine our new daily sales goals as we input previous day's sales to hit our target goal. My management team is not trained in Excel so I'm trying to make it as easy as possible for several different people to use on a daily basis.

Some information (dummy data)

Sales Goal for Quarter 2: $200,000
Days in Q2: 91 ($2,197.80 per day)
Day 1 Sales: $1,900
Day 2 Sales: $1,650


What I want the formula to do in this instance is take into account that 2 days of sales have been recorded and modify our daily sales goal of $2,197.80/day to tell us what our sales need to be for the next 89 days to hit our target, without changing any formulas. All I want my team to have to do is put in what our sales were for the previous day and have the total change to tell us what we need to do moving forward each day to hit our target for the quarter.

I hope that makes sense, and someone can help me! Thank you!!

 

[jtakw]

Hi,

You can do this:

Excel 2010
	A	B
1	Sales Goal for Quarter 2:	$200,000.00
2	Days in Q2:	91
3	Daily Goal	$2,207.30
4	Day 1 Sales:	$1,900.00
5	Day 2 Sales:	$1,650.00
6	Day 3 Sales:
Sheet1

Cell Formulas
Range	Formula
B3	=(B1-SUM(B4:B100))/(B2-COUNT(B4:B100))