Solved

How do I calculate a conditional running balance

Posted on 2014-12-25
36
287 Views
Last Modified: 2014-12-28
Hello --Note: Please read carefully before responding, as it gets a little tricky:

I need to calculate a running balance of items charged to our expense account from a base budget amount for each category. For example, if I spend $500 on groceries once a week and my budget is $5,000, I'd like to subtract the $500 or whatever I spend from the running total balance of groceries, which started at $5,000.  I'd also like to do the same for other categories of expenses also on the same spreadsheet: so I might have a budget of $1,000 for medical expenses, and a budget of $400 for clothing, etc.

It sounds simple, but the issue I'm having on my worksheet is that my categories are not always in consecutive rows; for instance, I may have some grocery expenses in consecutive rows, but then I may have medical expenses and then back to grocery expenses, you get the point, yes?  So I would need some sort of conditional sumif formula, (I think). To further complicate matters however, groceries is not my only criteria, I have a different base budget amount for about 600 different categories, which I have in a drop-down selection list in a column.  With all of these different categories and base budget amounts, would I have to create a table of the starting budgets matched with the 600 different categories and then do a Vlookup to my main sheet? That's fine, but how would I then combine that with a conditional sum formula to keep a running balance for these categories on the same sheet? Lastly, I'd like to be able to pull all of this into a pivot table which would sum the individual amounts spent for each category, subtracted from the running balances by category, to give me my remaining spend/overage. I've got everything figured out except how to keep the running balance piece for each category and each indidvidual amount spent within that category without it double counting the starting balance in the pivot table.

HELP!!!
Cheers!
0
Comment
Question by:angelfromabove
  • 16
  • 9
  • 5
  • +1
36 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 40517435
your design is the means to achieve what you want, you have a Ashely that includes your transactions.

You might benefit by using an sql server express to store the data in tables with excel functioning as the front end data entry as well as the means by which data is extracted.


Back to yours.
Using  ms query, you will go through the sheet where the purchases are recorded
And sum if the category is groceries as groceries. And repeat for e Rey other category.

This will only say how much you spent, but will not show how much is left in the budget.

Reverting back to using the DB, in that you would create a view which will combine the annual budget for every category and an ongoing adjustment based on purchases

You have a budget table and how much is allocated to each category

You would join tables/sheets based a common column, categories in your case.
0
 

Author Comment

by:angelfromabove
ID: 40517734
Arnold -

Thank you very much, but this will not be my solution.  I need to do this in an Excel spreadsheet and ultimately pivot tables and I need detailed instructions based on my question to accomplish this.

Does anyone else have a possible solution? Time is also of the essence here.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40517916
You can still use excel with your tables/worksheet in one file while the other uses the external db query using the excel spreadsheet as the source of data.

You would then use ms query to build the query and the rules to give you the display you want.

When using excel, make sure to include system tables so that you will get the tables available in each worksheet.  

You can the combine the tables on a common column parameter (join)

The result will be the table with data.  You can the run the pivot table on this data set.
0
 

Author Comment

by:angelfromabove
ID: 40517999
I don't know how to do what you are describing, I only know Excel. I will need a different solution.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40518017
Ok, in excel, you have a workbook with tables spanning multiple worksheets?
Close this file. Open new emply workbook.
Select column/row A1
Go to the data menu select import external data from db
On that selection use excel files as the source for tables.
The next stage should list the tables it found among the workbook's worksheets. If not look at options to make sure system tables are also selected. Each table will include references to the column heathers.



If you want, can you upload the workbook with just the column heathers as well as how each worksheet/table relates to the other.
I.e. Worksheet1 columnname is the Id for the table on worksheet 3.

I'll combine them
You can then see if that is what you are looking for. I.e. Update the workbook and then see the results on the "report workbook"
0
 

Author Comment

by:angelfromabove
ID: 40518119
No, I just have one worksheet with multiple columns: locations, dates, expense amounts, expense types and account numbers.

I don't want to over complicate this, I just want to be able to keep a running balance, subtracted from various budget amounts, and the budgets will be different depending on the location and/or expense type.

The only thing I'm having trouble figuring out as I state in my original question is how to automate the subtraction of the expenses from the various budget amounts based on the location/expense type.  The formula would need to be able to detect that ABC Expense, at XYZ Location has a budget of $5,000, but the individual expenses could be on any row in the spreadsheet, , not necessarily consecutive rows.  I thought that a combination of Sumif, Match, Index or vlookups might work somehow, but the challenge is that I can't copy the budget amounts to every row, because they will be summed multiple times in the pivot table.  The budgeted amounts have to be in one spot, the automation would have to know which budget goes with which category, and subsequently detect and subtract the expenses on each row associated with each budget amount.

Ideally, on my pivot table I would have the Expense Type, Location, Date, Expense Amount, Spent to Date, and Budget.  Perhaps, a formula can be written to detect when the budget amount has already been populated once on the worksheet, and for every subsequent expense associated with that Expense Type/Location, it knows to go to the same row for the budget. I don't know, but either I need someone to build something for me, perhaps using the method you described, or give me some formulas to work through Excel, the way I feel comfortable doing it.  I know this can be accomplished through Formulas, I just don't know the right combination, but an Excel Expert should.

Thanks for your help, but I would prefer someone else took a stab because I'm not sure I can achieve what I want by your method because I still don't understand how to accomplish it.
0
 

Author Comment

by:angelfromabove
ID: 40518123
I will also consider Code, if someone can write something for me.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40518659
You would either have to use VBA macro that will go through the cells and extract the data or you have to use the external query mode.
Without seeing how your data is organized,
You basically want the following
column1,column2,category,column4

you want a sum that looks at the column category and sums their values and this value is used to reduce the badgetery balance which is value -the cell where the respective sum is.
I am not sure you can do a sum (if category='food';expense;0) which will be the Food_expense.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40518677
In order to write anything one has to know the basis on which you want these determinations made and how your data is arranged.

Can you provide a sample/empty excel with the setup you have
i.e.
columns A1-h1 40 rows are your heading that layout your yearly budget on the budget worksheet.
Your second worksheet is your expense/data enrty
Columns A1-g1 describe the
date,category,store, method of payment,amount,etc.

This is the basis on which one can write code, test functionality etc. .
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40518994
Hi A,

This can probably be achieved without any VBA; you just need to configure a proper running SUMIF function to return YTD expenses for each category as it is entered and then subtract that amount from the budget to get the balance.

See the attached example workbook and let me know if that has the general behavior you're looking for.  (Note that the Expense Category is a drop list-data validation to the Budget table like you described and the Date column is limited to 2015 dates).

Regards,
-Glenn
EE-RunningBalances.xlsx
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 40519000
I think I know what you want but it will make things a lot easier if you could post a sample worksheet of what you've already done also with some sample data.
SUMIF certainly sounds right but it will depend on the layout if the data and integration with the categories. By having a sample sheet I'm not going to go down the wrong track only to turn around and start again
0
 

Author Comment

by:angelfromabove
ID: 40519186
Glenn!!!!!!!

Merry Christmas! Perfect! Exactly what I wanted, I just couldn't organize my Excel knowledge to get there, because I had the "SUMIf" part right, just couldn't figure out how to subtract it from the budget using a Vlookup, but you did it in 1 columns.  Good thinking!  The only thing left is  I Just need to be able to pull the budget for each category into a pivot table though, so we can see everything side by side. That's the toughest part for me.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40519210
In the budget include a column reflecting the expenses from the category, then have a total column that reflects the available remaining total.

You can then gave the allocated, the spent and the remaining columns to graph as you see fit. I.e allocated is blue, spent is ref, green, is what is still left.
0
 

Author Comment

by:angelfromabove
ID: 40519211
Thank you Arnold, but I will also await Glenn's response.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40519236
I don't believe you'll need a PivotTable; the data shown on my example Expenses sheet/table could be expanded to accommodate locations and original budget amount, if desired.  See the modified workbook attached.

Since both the Expenses and Budget sheets are using Excel Tables, you'll see structured referencing in the formulas that pull the data.  Don't let it scare you off; it's just another way of referring to the fields in each table.

-Glenn
EE-RunningBalances.xlsx
0
 

Author Comment

by:angelfromabove
ID: 40519270
Glenn, you are certainly right, I don't need a pivot table to accomplish the tracking, but for reporting purposes, I just want the functionality of it, i.e. I can slice and dice the data by month, expense type, location, budget and whatever else I have on my original spreadsheet. It's a neat way of summing and filtering at the same time. I'm the queen of pivot tables!

So, again your new table makes perfect sense, and we're almost there except it looks like the total Budget for Supplies in Location 100ABC is 10,000 if we add $5,000 + $5,000. In actuality, the budget is only $5,000 for Supplies in that location.

The challenge is , how can we show the correct total budget without duplication, when an expense is posted to the same location and expense type?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40519394
Sorry, I should have clarified what each column is showing:
Spent to Date - Year to date sum of amounts for that Location and Expense Type
Balance - Budget minus Spent to Date
Budget - Original Budget for Expense Type for that location (this value is strictly informational; will not change)

-Glenn
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:angelfromabove
ID: 40519758
Exactly, no I get it Glenn, it was very clear what each of your columns was doing and that's what I need but do you get how I want to show the budget column? Read my earlier posts, can this be accomplished?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 40520157
Just to chime in here... the budget column shouldn't be aggregated in your pivot table.  It is essentially a label it should appear either in the column or row but not in the "value" section.  Same goes for the Balance.  You've already essentially done the aggregation on those values in the data table
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40520244
You won't be able to insert the annual budget into a PivotTable.  You'd have to set up some separate lookup to retrieve it.  By extension, the "Spent to Date" and "Balance" data wouldn't be valid in a PivotTable either.

In that instance, you might want to consider using something like GETPIVOTDATA to pull summarized data and display it side-by-side with the annual budget data.

-Glenn
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 40520246
That's a good point.  You can show the individual budgets but not the overall budget total easily in the pivot.

This is where you may want to look at a BI tool that can do much more than Excel was designed for.  I have extensively used Qlikview due to its ease of use, though it may be a bit pricey.   Have a look if you're looking to do this kind of reporting more often and need a lot of flexibility.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40520257
IMO, while PT's are great for summarizing large sets of data and are great for ad hoc reporting, I think the solution here is simple and can be achieved within Excel's functionality.  

The Budget values *could* be inserted as Row Labels and the format changed to Classic PivotTable layout, but I'm not sure that's a good solution.  In my simple example file it seems okay, but with larger numbers of transactions, it might not look good.

see updated example file.

-Glenn
EE-RunningBalances.xlsx
0
 

Author Comment

by:angelfromabove
ID: 40520561
Glenn! Thank you.  Glenn, your pivot table looks identical to the pivot table I set up except you were clever enough to try the budget as a row label!  Exactly what I had in mind! I did the same with the Balances.  This is as good as it's going to get and it will work for me.

Rob - Thank you, and no disrespect, butI get that Spent to Date and Balance have already been aggregated. I am very good with pivot tables and we can also accomplish the aggregation within the pivot table by summing the individual amounts spent. I wouldn't necessarily need to use the aggregated column.  We really need to think about what I need to do here though. As I mentioned in earlier threads, I need to report exactly what Glenn has set up, but against a constant budget for each category. Glenn  has essentially done what I need, and if I need to also report on the total budget for that category, I need to be able to quickly filter it and  say, *** was $5,000 and here's what we have spent thus far----- If we repeat it in the table it will be hard for someone to quickly determine what the budget was. I need to be able to group by expense type, date, etc. as I mentioned. So while it may seem obvious to you, I know what I need to do. I already have a pivot table set up and it's working exactly like I need it, Just needed to figure out how to report the budget where it makes sense.... This will be for many business expense accounts, groceries and gas are just generic examples of what I need. I need to make it foolproof for my whole department to use.

Thanks again for all of your suggestions!
0
 

Author Comment

by:angelfromabove
ID: 40520563
I thinks there's a glitch somewhere, I tried to accept Glenn's solution and assign a grade twice, and it doesn't look like it took. Is there a delay in closing out the question?
0
 
LVL 76

Expert Comment

by:arnold
ID: 40520576
It should be as simple as clicking Accept as Solution on the post you want, or use the accept multiple and select all the posts that helped with the point value.

If you think in the future you would want a comparative historical report about the various data points, consider looking at using an mssql server express as the data store.
0
 

Author Comment

by:angelfromabove
ID: 40520602
Arnold, Thank you. It should be that simple, but obviously trying to click Accept as Solution didn't work for some reason.  I will call Customer Service on Monday.  As I mentioned previously, I don't know how to use an mssql server, but I think I will learn going forward.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40520622
MS is already offering SQL server 201.
you can download it from http://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx
make sure to also get/install the SQL server management studio express which is the graphical tool through which you can manage the data within.  You can then use excel using the data external query connect to the ms sql server to generate the queries i.e. select the database/tables and then come through the options you want etc. as well as excel being the data entry into the database.

It also includes reporting if needed, that can also be configured to generate the reports you want emailed to you ........
0
 

Author Comment

by:angelfromabove
ID: 40520782
Thank you. Power Pivot which is an add-in works similarly in Excel and can also connect to External servers like SQL. I am now trying to close this question out with Glenn's last response as soon as I can figure out what is preventing me from doing so.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40521028
When hitting Accept answers as solution, assigning a grade, do you hit the accept immediately option or the other option to the right?
0
 

Author Closing Comment

by:angelfromabove
ID: 40521032
Perfect
0
 

Author Comment

by:angelfromabove
ID: 40521035
Worked on my phone not my laptop for some reason. Thanks!
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 40521052
angelfromabove,

Glad you've got your solution and Glenn has done some great work for you.  My suggestion regarding the BI tool comes from doing extensive reporting and pivot tables in Excel and getting frustrated with the amount of time needed to create a report or PT that did everything I needed.  Especially around the aggregated values like budget not getting aggregated again but being able to appear within the aggregated area of a PT (and having complex formulas to determine exact budget amounts per reporting period), exactly as you have indicated.  It also allows for incredibly easy filtering of data by just clicking the options in any number of filters.

On top of that, the formatting was always a pain.  These tools are designed to do what you want.

I'll stop banging on about it but I'm more or less saying to not dismiss the idea of a BI tool (for me it was Qlikview) that essentially makes setting up a report like this, take only a matter of minutes, rather than hours or days.  

After all this, if you can do what you want in Excel then that's great too.  I'm just letting you know what else is out there and how much it has benefited me in similar situations.
0
 

Author Comment

by:angelfromabove
ID: 40521124
Arnold -

Sorry, I am not against your BI solution, I believe I already said I would look into it. I just needed something right now for this purpose that I could easily implement. Where it was frustrating or time consuming for you to use pivot tables, it's easy for me because I am pretty advanced with using them, it's my expertise. I was just too busy to think about it and I chose to utilize my option to enlist the Experts this time. I also mentioned Power Pivot in my last post, not Pivot Tables as we were discussing earlier. Power Pivot is quite different and functions like an SQL database.

I really hope you have a great evening and upcoming New Year, but I am going to sign off from this discussion now.

Thank you.
0
 

Author Comment

by:angelfromabove
ID: 40521126
Sorry, Rob, not Arnold.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 40521149
Sorry, didn't want to give you the wrong impression here, was just trying to say how much I've gotten out of another tool as I'm also very proficient with excel and pivot tables.  I was also aware you were referring to Power Pivot, which is also a good tool to use.

I'll also sign off but good to hear that you'll look into it.

Rob
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
In this article I will provide some simple productivity hacks that will help you use Google to specifically show results from any web site (Experts-Exchange.com in my example), with minimal effort in Chrome and Firefox. I've seen a common theme a…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now