Link to home
Start Free TrialLog in
Avatar of angelfromabove
angelfromaboveFlag for United States of America

asked on

Follow up to previous question Accepted Solutionby:Ejgil Hedegaard ID: 406884912015-03-25

Please refer to the closed question referenced above as well as the spreadsheet in that closed question.  I need to introduce a new element to the same spreadsheet, (which by the way has been working perfectly!).  

In the Budget Column, I need to split it out by fiscal years, i.e. Yr 1 Budget is $125,000, Year 2 Budget is $75,000 and Year 3 Budget is $25,000.  

So, in addition to subtracting expenses from $125,000 and keeping a running total, the formulas will somehow need to recognize when the expenses are in a new fiscal year and start over with the Year 2 Budget, Year 3, etc.  I am not sure, but I'm thinking we may need to add a Year 2 and Year 3 Budget Column, or if not, perhaps we can just have the formula refer to the appropriate budget number in the drop-down menu according to the date of the expense?

I'm not sure, and I would appreciate your assistance again!

Thank you!
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Avatar of angelfromabove

ASKER

Yes, that's the link to my original question. Did you post that for me or for one of the Experts to refer to?
For anyone, so they knew which question it was as it was some time ago.
Thanks, it would be great if the original expert could answer because they are more familiar with it, which is why I posted his name.  I have not received any responses, which is unusual.  What can I do to get more responses?
The moderators will monitor and if there are no responses they will send out notifications to Experts who may be abke to help;  don't know what timescales you'd be looking at though.
I answered the question then, but don't think I have any special advantage in understanding your question due to that, but thanks anyway for your confidence.

It is not clear to me what you want.
The formulas on sheet "Journal Entries" pick up records from several fields, and positive and negative values from "Account spend" on sheet "MARKETING INPUT TOOL" that match the year and month in cell C8.
I don't see any reference to Budget values.
Where does the budget values $125,000 etc. come from, and how should that be used on "Journal Entries".
From a single date C8, how to determine if that is Year 1, 2 or 3.
Fiscal year can be any 12 month period, but guess you mean October-September, or?
Please specify.
Hi Ejgil - thanks for responding!

On the Marketing Input Tool, the Budget Column is Column P. There is a formula in Column P, referring to Column E (location) and matching that location to Column B in a hidden tab called "Drop-Down".  It's then pulling in the budget from Column J in  the "Drop-Down" tab for that particular location.  The budget numbers do not flow to the "Journal Entries" tab at all. We use that column to calculate a running budget amount based on the location and the Actual Spent in Column L on the Marketing Input Tool.  

My new ask for this question:

That budget needs to be allocated according to a percentage by year and possibly month.  For example, if Location A has a budget amount of $100,000 for Fiscal Year 2016 (November 1 through October 31), it may be allocated as $50,000 for Fiscal 2016, $25,000 for Fiscal 2017 and $25,000 for Fiscal 2018.   If expenses are incurred after the end of Fiscal Year 2016, the Fiscal 2017 budget of $50,000 would be added to whatever is left from Fiscal 2016. For example, if we spent $45,000 of the $50,000 budget in Fiscal 2016, we would have $5,000 left to carryover to Fiscal 2017 + $50,000 from the Fiscal 2017 budget, for a total of $55,000 for Fiscal 2017.

I don't know how to accomplish this with formulas and need your expert  suggestions! Perhaps a column for each Fiscal Year Budget, or possibly  just one "If" Formula in the budget column? I'm not sure.
 
Thank you in advance for your help!
Correction to the example in my question above: The Fiscal 2017 starting budget should be $25,000; thus the calculation should have read, $5,000 from Fiscal 2016 + $25,000 from Fiscal 2017 =$30,000,  not $55,000 as I've written in my previous post above.
Drop-Down has only one budget value for each Location, so how is the split made for different Fiscal years.
I guess year1 will be the Fiscal year in column D.

When not used budget flows to the next year budget, the budget values will be 50,000, 75,000 and 100,000 for the 3 years when nothing is spend.
Budget in year1 will always be initial value.
Spend in year1 will reduce budget for year2 and year3, spend in year2 will reduce budget for year3, and spend in year3 (or later) don't change any budget values.

None of the values in the example match the workbook from 2015.
Please make a new sample, matching the example, including some spending for all years.
This is the reason for this question.  I haven't figured out how to add the different Fiscal years, let alone do the split:-)

You are absolutely correct about the allocation of the budgets over the fiscal years if nothing is spent.  Also, there will be no budgets past year 3, that's where it ends.

I have attached a new sample with spending for all 3 years in highlighted Rows 12-21 on the Marketing Input Tool Tab.   To simplify things, I used the same Location for all of the spending which is "Dummy Data 7". I don't know how to portray this spending for all years in the budget columns however, that's the question I'm asking you.  For now, I just entered the entire budget of $100,000 in the Drop-Down tab in cell J3, corresponding to Location "Dummy Data 7" in cell B3.  On the Marketing Input Tool Tab, the Budget of $100,000 is carried over from the Drop-Down tab for these highlighted expenses in column P.
Sample-BudgetA.xlsx
Check attached.

Columns for Fiscal Year1, Budget Year1, Budget Year2, Budget Year3 and Budget Total added on Drop-Down sheet.
Fiscal Year1 is determined by the date in Month (column D).
Budget Total is used in Balance on "MARKETING INPUT TOOL".

Same columns on "MARKETING INPUT TOOL" except Budget Total.
Fiscal Year1 is found on Drop-Down.
There should be only one row for each Location on Drop-Down, so if more than one row, "Duplicate" is displayed, and Budget 2 and 3 returns an error.

I have deleted all not used rows on "MARKETING INPUT TOOL".
With 5000 rows the calculation time is increased significantly, and is not needed when using tables.
When you insert a new value in any column (don't use one with a formula) below the table, Excel will automatically add that row to the table.
Sample-BudgetC.xlsx
Great work, thank you! I am just concerned about the running Balance Column.  If I change the amount spent to $50,000 in the first month and then zero out every other month in 2016, the balance should be zero until 2017 kicks in.  I agree with everything you did, I'll just need to keep a running Balance.
Sorry, forgot.
Check attached where I have changed formulas in "Spent to Date" and Balance columns.
The Balance will be a little strange, since "Date Submitted" are not in ascending order.
Sample-BudgetD.xlsx
Looks great.  I think that will do it, but Why is Budget Year 3 only $24,974.93?
Due to the transfer from previous years.
Balance is negative in row 18, that is the difference.
Something is wonky there, it's not a running balance is it? Did you change the formula? The budget is static and should not change.
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sincere apologies for the lack of response thus far, We had a long term fire drill priority come up - I will close this for now and revisit it when time permits. Thank you for your help so far!!!!!