First off, I am the store manager of a retail location. I have taken on a project to help ease some business projection calculations.
Here's the set up:
I want to figure out how much money a store would need to make every month in order to hit a specific goal. For example, let's say I would like my store to make $800,000 this year. I have each month's sales results from last year, which add up to $658,778 total for the year. So, obviously, for this I would take (800,000/658,778)*(last year's month's sales) and do that for every month to find the proportioned sales needed every month this year in order to hit that $800,000 goal.
This is where it gets tricky. NOW, I want each of those new monthly goals to automatically update in accordance with actual sales results throughout the year. For example, let's say my new sales goal for March is $30,000. Hypothetically, I make $20,000. That means that I now have to make up $10,000 in addition to the new monthly sales goals for the rest of the year in order to hit $800,000. I want to proportionally spread out that extra $10,000 between the remaining months automatically based on sales share for the rest of the year.
I have a cell designated for "Goal Year to Date (YTD)." Our fiscal year is February-January, so the January "Goal YTD" should ALWAYS equal $800,000.
If anyone knows how to do this and would like to share, that would be AWESOME!
TEST 910 Metric and Sales Goals by Month.xlsx
-Alex
Bookmarks