Post

3 followers Follow
0
Avatar

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?

Beth Barbaglia

Please sign in to leave a comment.

8 comments

1
Avatar

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

Rainer Grabowski 1 vote
Comment actions Permalink
0
Avatar

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! 

Beth Barbaglia 0 votes
Comment actions Permalink
0
Avatar

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!

Andrew Wardle 0 votes
Comment actions Permalink
0
Avatar

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 Barbaglia 0 votes
Comment actions Permalink
0
Avatar

If you remove

if (years[i][0]==@Year && months[i][0]=="March") 

do you get a number? If so then there's a problem with one of the two conditions.

I assume the Marketing Account field in the lower app (Expenses?) is a direct reference to the Subscriptions Account shown above?

Andrew Wardle 0 votes
Comment actions Permalink
0
Avatar

Hi Beth, 
can you please share a screenshot of your calculation field and also a screenshot of the item where you tried it plus a screenshot of one of the related items (where we can see the fields amount, year, month). 

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer and Andrew, 

Attached are the screenshots requested:

The calculation

 

The app the calculation is in

The app the calculation is trying to total amounts from

Beth Barbaglia 0 votes
Comment actions Permalink
0
Avatar

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.

 

Andrew Wardle 0 votes
Comment actions Permalink