Stuck on a anlysis formula

  • Thread starter Thread starter Allierz
  • Start date Start date
A

Allierz

I am preparing a sales anlysis form. I want to be able to take th
current months sales to date and compare this to last years results t
see how one is performing. However, all I have is total for last year
sales. This would have to be divided by 12 to achieve an averag
monthly total and then multiply by the current number of months (i
this case 4).
The current years results must then be compared to this figure t
arrive at a % comparison.

The other problem I'm lost with is what happens next month if I want t
do the same thing. Can a formula be done to adjust for the months i
the. In other words this exercise is for 4 months. Can Excel adjust t
5 months if I change the current month.
Example:
Last Year Sales $120000 (for 12 months Jan to December)
This years Sales $36000 (for 4 months to the end of April)
What is the % differance + 0r - for the same period.
Hope this makes sense. Thank
 
Don't know how your sheet is set-up, but try this as an idea.

Labels in A1:E1
Date, Sales, Yr.Ttl., Yr.Avg., % of 2003
In F1 enter 2003's monthly average, i.e. $10,000.

In A2 to A13 enter the month ending dates,
1/31/04, 2/28/04, 3/31/04 ... etc.

In B2 to B13 enter the monthly sales total.

In C2, enter this formula:
=SUM($B$2:$B2)

In D2, enter this formula:
=C2/MONTH(A2)

In E2, enter this formula:
=ROUND(D2/$F$1%,1)

Now, select C2:E2 and drag down to copy to row 13.

This should be able to give you some idea of what you can do to present your
numbers in different fashions.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




I am preparing a sales anlysis form. I want to be able to take the
current months sales to date and compare this to last years results to
see how one is performing. However, all I have is total for last years
sales. This would have to be divided by 12 to achieve an average
monthly total and then multiply by the current number of months (in
this case 4).
The current years results must then be compared to this figure to
arrive at a % comparison.

The other problem I'm lost with is what happens next month if I want to
do the same thing. Can a formula be done to adjust for the months in
the. In other words this exercise is for 4 months. Can Excel adjust to
5 months if I change the current month.
Example:
Last Year Sales $120000 (for 12 months Jan to December)
This years Sales $36000 (for 4 months to the end of April)
What is the % differance + 0r - for the same period.
Hope this makes sense. Thanks
 
2003 Years Sales 120000
Avg monthly 2003 10000

(Col b - Made up numbers)
Monthly YTD % Prior Y
Jan 12000 12000 120
Feb 6000 18000 90
Mar 6000 24000 80
Apr 12000 36000 90
May Cell C8-> =SUM($B$5:B8)
Jun Cell D8-> =+C8/(COUNT($B$5:B8)*$B$2
Jul Cell C6 -> =SUM($B$6:B6)
Aug
Sep
Oct
Nov
Dec
 
Allie,

A1: 120000
A2: 36000
A3: 4 Where the 4 is for April......
USe this formula

=(A2)/(A1/12*A3)

Formatted as percent.

HTH,
Bernie
MS Excel MVP
 
Sorry guys. This all a bit new to me.

I have 6 cells

A1 Customers Name
B1 Customers Logo
C1 Sales Jan - Dec 2001
D1 Sales Jan - Dec 2002
E1 Sales Jan - Dec 2003
F1 Sales YTD Lan - April 2004
G1 $ Difference 2002 - 2003
H1 % Difference 2002 - 2003
Cells A1 to E1 are entered manually
Cell F1 has a formula: =D2-C2
Cell G1 has a formula: =D2/C2-100%

I need to add another column to arrive a comparison of sales YTD i
Cell F1 and compare this to the figure in Cell D2 but for the sam
period. Firgure in D2 is for 12 months, not 4 months. The answer I a
looking for is the % of diferrence be that + or -.

The slaes sheets are spilt into Areas or Reps so they vary in ht
amount of customers and are totalled at the bottom.

The other problem that I see is do I need to alter the formula ever
month or can a formula be based on a date? The above is for figures a
the end of April. What happens in May?

Trust this is clearer.

Many thanks for your help.

Alli
 
Back
Top