Budgeting: Totals by Month for Yearly Budget
We have Account Numbers that have a yearly budget, but we're responsible for spreading that budget across all 12 months appropriately and then turning in variance reports if our numbers are off from what we budgeted. I want to see my expenses in real time against what I have budgeted so I can move funds if needed before the month closes.
We have an Expense Reports app to track expenses throughout the month and I'm trying to figure out how to sum expenses for each month for that account code in THAT month only so I can keep track of how close I am and if I need a variance report.
For instance...
Office Supplies budget by month:
Jan = $21
Feb = $0
Mar = $111
Apr = $0
So I want to be able to sum Office Supplies expenses from the Expense Reports by each month it's spent. I don't want to sum my total spent because it won't show me accurately where I am for the month.
Does that make sense?
Does anybody have any suggestions?
-
Hi Beth,
for a suggestion which makes sense it would be helpful if you could share screenshots of both apps to see their structure (the screenshots should show the fields needed for the calculation).
Some questions:
1. I assume the month budget fields are money or number fields - correct?
2. Are the apps where the monthly budgets are in and the Expense Reports app connected via a relationship field?
3. Do you use Globiflow (it's included in the Podio Premium Licence)?Rainer
-
Hi Rainer,
I think I've narrowed it down to just 2 apps: Expense Reports and Expense Budgets.
I have linked them with a relationship field in Expense Reports.
I also added 2 category fields in Expense Report - "Year" and "Month" for the sole purpose of being able to do a conditional calculation in Expense Budgets.
I'm thinking, for example, if I can make the "March Actual" calculation in Expense Budgets say "if Year = 2019 and Month = March, then calculate the sum, otherwise don't calculate a number."
That way, I'm hoping it'll only total the sum of Expense Report items that reference that Account Name if the Year = 2019 and Month = March.
Would that work?
Here's a screenshot of both apps. Thank you!
-
I think a formula like this in your Accounts app calculation field [March Actual] should give the figure you want.
var amounts = @All of Amount with nulls
var years = @All of Year with nulls
var months = @All of Month with nulls
var total = 0;
for(var i = 0; i < amounts.length ; i++) {
if (years[i][0]==@Year && months[i][0]=="March") {
total +=amounts[i][0]||0;
}
}
total;If it works I must credit Rainer here who taught me to do these sorts of calculations!
-
Hi Andrew,
It alllllmost worked. I had to sub out "@year" for "2019" because it wasn't pulling up a variable and I need the calculation to be for the year. (was that wrong?)
Now the equation is populating a 0 - which is closer than I've gotten! 😄
I tested it on one account that should be totaling $294 based on an expense that was submitted with that account code but it's still saying $0.
Any insight?
-
Beth
var years = @all of years with nulls
not
var years = @all of amount with nulls
Does that fix it?
Also, you can automate the production of the Notes entries using a calc field to create an html table
See Rainer's previous post for details.
Please sign in to leave a comment.
Comments
8 comments