It is not too sunny here, but I am going to put on my business man hat. At the end of each month, I ask myself if my business (chandoo.org that is) has performed better or worse. One simple way is to look at previous month’s numbers and then I know how good the latest month is.
But thanks to awesome people like you, my business is growing every month. So mere comparison with previous month’s values is not enough. I would like to know, for eg. if the latest month is,
- The best month ever
- The best month in last 12 months (trailing 12 months)
Now, it would be a shame if I have to find these answers manually. So I write an Excel formula. That is right!, a formula to tell me if the latest month’s value is all time best, best in last 12 months.
How to write such formulas?
Oh, the formulas are really simple. More so, if you compare it with the effort it takes to make a month all time best in sales (or any other metric).
Assuming we have a bunch of sales numbers by month in the range B6:C30,
To test for All time high condition:
- In cell D6, write =C6=MAX($C$6:C6)
- Drag the formula to fill remaining cells in column D
- Now you will see a bunch of TRUE and FALSE values. TRUE means the corresponding month’s sales is an all time high.
Tip: See how we are using $C$6:C6 in the MAX. This style of referencing is called as mixed cell referencing. By using this, when you fill the formula for remaining months, the range inside MAX grows. Thus for each month we get the maximum sales value thus far. more ».To test for Trailing 12 month high condition:
- We will test this condition in column E.
- In E17 (trailing 12 month high can not be calculated for first 11 months…) write = C17=MAX(C6:C17)
- Drag the formula to fill remaining cells in column E
- Now you will see a bunch of TRUE and FALSE values. TRUE means the corresponding month’s sales is a trailing 12 month high.
Tip: The range C6:C17 is relative. Thus when you fill the formula down, it changes to C7:C18, C8:C19… referring to previous 12 months. more ».Download Example Workbook
I have prepared a simple example file. Download it to understand these formulas.
How do you analyze your sales data?
Apart from the above techniques, I also use line charts & trend lines to understand the sales trend. Also, I use pivot tables to segment my sales based on product, customer type, region etc. Since my business is new, I do not have previous year values for many products. But where possible, I compare sales from last month same year to see how well the product has grown / shrunk. I do not set any targets at monthly level as I aim to enjoy the process. So I do not use bullet charts or target vs. actual charts per se.
What about you? How do you analyze sales or similar data? What metrics do you use to gauge the performance? Please share using comments.
15 Responses to “Christmas Gift List – Set your budget and track gifts using Excel”
[...] Christmas Gift List – Set your budget and track gifts using Excel … [...]
I'm confused: if you spend $10, and your budget is $40, shouldn't the amount in the "Within Budget?" column stay black, since you didn't go over budget?
In other words, since we overspent on the electronic photo frame, shouldn't the $8 cell turn red?
@JP.. maybe Steven is encouraging consumerism... ?
I havent realized it earlier, but now I see it. If you unprotect the sheet, you can change the formula in Column I to =IF(G13=0;" ";F13-G13) from =IF(G13=0;" ";G13-F13), that should correct the behavior.
Thanks Chandoo. I thought of making a shopping list spreadsheet for Christmas, but this is neat so I think I'll use this instead.
Chandoo & Steven thanks for this spreadsheet. But for the sake of a person who has been staring at this megaformula in vain for the last 40 mins and not afraid to ask, would it be possible for you to walk us through the logic used here?
=SUM(SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="-"))+SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="0")))&" / "&SUBTOTAL(2,$G$13:$G$62)
Thanks Chandoo.. This is one of the best budget spreadsheets I've ever seen.. The Arrays are out of this world!! And it's FREE!!
Chandoo, can you tell us more about Steven? Does he have his own site?
JP, I think Chandoo changed it when he changed the currency formatting from £ to $, a negative figure is a good thing in this case. But don't change the formulas, the overbudget and under budget won't work properly if you do. Also Chandoo I think you've accidentally broke the conditional formatting for the alternating row colouring the formula is different to the version I sent you. As for the megaformula chrisham, it gave me a headache trying to get it all working, so I will let Chandoo talk you through it.
Hi,
In cells I6 and I7, I understand that subtotal together with offset function returns an array of ones after which, the sumproduct function gives the desired result.
But I’m not able to figure out the reason for using an array in I8 to return the most expensive gift.
Can’t the formula be just
“=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)”
Savithri, Cell I8 needs the array, if the formula was “=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)” it would find the highest price from the filtered range (i.e. highest actual in filtered range is $50) BUT then return the first person with that actual, not looking in just the filtered range (so first person on the list with a $50 actual.)
To see what I mean, change the formula, then change all the actuals to $50 then filter for baby, it lists the first name on the list.
But a good question 🙂
Thank you. I now realise that the array is used to get the ‘filtered range’ instead of the entire range, as table array for look up value.
[...] Download This Template [...]
this looks like an awesome excel sheet!! is there anyway i can get it emailed to me unprotected? for some reason, i am unable to download it 🙁 help!!
Hi I also can not download to a mac as the sheet is protected any help would be great
[...] to send her a pricey present. Rather, send a card with a picture of your child. Here’s a cool Excel sheet that will help you estimate your budget per person and let you track [...]
[...] husband and I pour/poor over the Christmas spreadsheet (yes, I do know how dorky that sounds, but we’re not the only ones!), figuring out who should give what to whom. We live at a distance from most of our family, so it [...]