What I have is a spreadsheet which needs to calculate future monthly required sales goals, based on an annual sales goal, and previous monthly sales.
EXAMPLE: My annual sales goal is $1,200,000. This amount is divided into twelve (12) monthly amounts, based on the previous year's percentage of total sales for each month, for a year, (i.e., January 7.17%, February 6.98%, etc.). This provides me with the required sales amount for each month, to reach my new annual goal.
The problem I'm having is, I need to take each month's actual sales, and subtract it from that month's required sales. Then add/subtract that amount to each future month's required sales goal, without changing the required sales for the current month, or any previous month.
EXAMPLE: At the beginning of the year I have a goal for each month, which totals to my annual goal. As each month's actual sales are entered, I need to adjust the required sales goals for each of the remaining months in the year (without changing the required monthly goals for the current, or previous, months). And so that those future monthly sales goals, plus the actual sales for the current, and previous, months total my annual sales goal.
My spreadsheet is set up as follows:
- Column "A" = Initial Monthly Sales Goals
- Column "B" = Actual Monthly Sales
- Column "C" = Actual Monthly Sales Goals Gain Or Loss: [A - B]
- Column "D" = Adjusted Monthly Sales Goals Required To Meet Annual Goal: [(C / Remaining Months) + Current Amount In Column "D"]
- Column "E" = True/False Column To Track If Column "D" Has Previously Been Adjusted
NOTE: When adding or subtracting to each future month's required goals in Column "D", cannot change Column "D" for current, or previous, month(s) required sales goals. Also, the sum of all future month's required sales goals, plus the actual sales amounts for the current, and previous, months must equal the annual sales goal.
Okay, hopefully I've given the right amount of information to be able to get some help, coming up with the right cell formulas to accomplish what I need to. Or, to allow someone one to come up with an AppScript to perform the required calculations. Either one would be greatly appreciated.
Thanks for taking the time to help with this situation.