Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Trying to create a Sales Projection/Forecast/Estimate
I'm trying to create a estimated sales pace for the month at my restaurant
from a chart that has our daily sales in it. The current chart has the following formula "=(C38/COUNT(C6:C36))*31" in cell C42. This formula takes cell C38 which is the TOTAL SALES for the month using the formula "=SUM(C6:C36)". This formula takes the DAILY SALES from C6 to C36 and obviously adds them up. Our daily sales vary greatly, as on the weekends we do double to triple our weekday business. Right now the formula is giving us the same number as our sales total for the month which from prior experience is probably off by about 27 thousand dollars. According to what I can tell the COUNT function allows 1 to 30 arguments is this what is causing the problem? And how do I create a solution? Softwa Excel 2000 O/S: Windows 2000 |
#2
|
|||
|
|||
If you have zeros or formulas that evaluate to zero in column C for the
future days in the month, the COUNT() function will still count them. Thus, you are taking your total sales so far, dividing by 31, then multiplying by 31, leaving you with your total sales for the month. Try using =COUNTIF(C6:C36,"0") "S L Pace" wrote: I'm trying to create a estimated sales pace for the month at my restaurant from a chart that has our daily sales in it. The current chart has the following formula "=(C38/COUNT(C6:C36))*31" in cell C42. This formula takes cell C38 which is the TOTAL SALES for the month using the formula "=SUM(C6:C36)". This formula takes the DAILY SALES from C6 to C36 and obviously adds them up. Our daily sales vary greatly, as on the weekends we do double to triple our weekday business. Right now the formula is giving us the same number as our sales total for the month which from prior experience is probably off by about 27 thousand dollars. According to what I can tell the COUNT function allows 1 to 30 arguments is this what is causing the problem? And how do I create a solution? Softwa Excel 2000 O/S: Windows 2000 |
#3
|
|||
|
|||
My first guess is that you have calculation set to manual.
tools|options|calculation tab. And the 30 arguments isn't a problem for your formula. It refers to things like: =count(a1,a2,a3,b1,b2,b3,....) =count(c6:c36) is just using one argument. === My second guess is that some of your numbers aren't really numbers--they're text masquerading as numbers. What do you get when you put =count(c6:c36) in another cell? what do you get when you put =counta(c6:c36) in another cell? If you think all your values are numbers, do these formulas match? S L Pace wrote: I'm trying to create a estimated sales pace for the month at my restaurant from a chart that has our daily sales in it. The current chart has the following formula "=(C38/COUNT(C6:C36))*31" in cell C42. This formula takes cell C38 which is the TOTAL SALES for the month using the formula "=SUM(C6:C36)". This formula takes the DAILY SALES from C6 to C36 and obviously adds them up. Our daily sales vary greatly, as on the weekends we do double to triple our weekday business. Right now the formula is giving us the same number as our sales total for the month which from prior experience is probably off by about 27 thousand dollars. According to what I can tell the COUNT function allows 1 to 30 arguments is this what is causing the problem? And how do I create a solution? Softwa Excel 2000 O/S: Windows 2000 -- Dave Peterson |
#4
|
|||
|
|||
Your formula (C38/COUNT(C6:C36))*31 takes the average daily sales and
multiplies by the number of days in the month, which is why it matches the total sales amount. If you have a date associated with each daily sales figure, you might use the WEEKDAY function to sum sales for each Monday, each Tuesday, etc. This would show how each day contributes to your total monthly sales figure, and (from a marketing perspective) would provide visibility as to what day you might have a sales promotion. For instance: to get total sales for all Sundays: =SUMPRODUCT(--(WEEKDAY(B6:B36)=1),C6:C36) .... assuming your dates are in B6:B36. Note the sum of these numbers will still add up to your total sales figure; from here you can apply historical percentages of sales increase / decrease (if you have that data) to make projections. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to calculate sales tax from total sales | Excel Worksheet Functions | |||
Template in Excel | New Users to Excel | |||
Complex Sales Tax | Excel Worksheet Functions | |||
How do i create a "look up" to sum values in excel? | Excel Worksheet Functions | |||
How do I create a formula that will multiply a 5% of a value >$150 | Excel Worksheet Functions |