Tracking Sales vs Goals

[blackroundpirelli]

Hi,

I'm working on an excel workbook that compares sales for individuals in my company vs their goals. I've got a pretty decently functioning workbook that displays nice graphs for several things like sales vs goal, commissions vs goal, etc. However, when I make any changes, its a pretty tedious endeavor. I have to go in and change everything at least 12 times (12 pages).

I'd like to simplify this. On each page I have something that looks like this:

Sales

Total:
Daily Average:
Tracking:
Target:
Target Average:

For each of those the adjacent cell does some math such as...Total would pull the sum of that months sales so far, where Daily Average would be "=total/current day in month, and so on.

There are also charts that use this information on each month's page.

Is there a way to not have to repeat this over and over for every single little chart and group of data I want to show?

Also, everything is only accurate for the current month. I'd like to lock down the charts and data on the last day of the month. Is there a way to do that?

[rcm]

please send a sample workbook with mock but structured input data and the expected results (graphs, info, etc.)...

[blackroundpirelli]

Hey, I think I have uploaded this correctly.

[rcm]

I got it. could you elaborate what kind of atomization is needed?

[blackroundpirelli]

Thanks RCM. I feel like my automation works okay, but I'm always interested in learning better ways of doing things.

I'd like to know if there was a better way to make it update friendly. Every time I add something to track, say, customer contacts, I have to redo everything. You see how I've got so much information at the bottom of every page which is essentially the same every month. Could I just put that down once and use the table for every month? I don't know if you even get what I'm asking... I don't know all the terminology for Excel, I just kind of put this together to the best of my ability.

Also: A big question. We're in February, so the charts for Feb. are accurate, but any previous months are not. I'd like to find a way to freeze months on the last day of the month so when looking back at them the numbers and charts show where I finished.

Do you think I did this in the best way? Is there a better way?

[rcm]

It all depends what do you do with it. How do you analyze it.

[blackroundpirelli]

I review it every day at the end of the day. I unlocked everything for the example. The only cells which are unlocked naturally are the list and the yellow cells: demos, write ups, sales goal, and commissions goal.

At the beginning of the month I set my sales and commissions goal. At the end of the day I add my demos and write ups to the number already in the box from yesterday and it updates my tracking for those. Also I update the list with any vehicles I've sold, their count and commission. The spreadsheet adds these up and compares what I'm currently tracking to the goal I set at the beginning of the month.

It also tracks my demo/sale and write-up/sale ratio from the beginning of the year and uses that number to tell me how many I need per day to achieve my goals.

[blackroundpirelli]

Do you see how I have tables which track demos, write-ups, sales, etc? Each table is set up the same way, it just tracks the same information.

Currently I have a table for demos, write-ups, sales, etc. each month. The information displayed in the charts is pulled from these tables. I was wondering, since each table is basically the same, if I could have one table set up somewhere that does the same thing? How would I make one table handle different information for each month?

I hope I've explained myself better. It would GREATLY simplify things, especially when making changes.

[blackroundpirelli]

Well, thanks I guess...