Budget Management

bcwilson.ca

All I wanted to show you how I am using smartsheet to keep my budget reporting in real-time.

I have 3 sheets

1) A Budget sheet

2) An Invoice Sheet

3) A Project Action Log

The purposes are as follows

Budget sheet

This houses my line my approved budget using MasterFormat and also my Non-construction costs like Architect Permitting, FF&E

I then use vlookups based on the "Budget Line" to look up in my Invoice Sheet to populate the "Costs Approved/Paid" and vlook-ups to my Project Action to highlight my upcoming spend.

The vlookups filter also looks at the project chosen in the Invoice and Project Action Sheetas I have multiple projects in the Invoice Sheet

An Invoice Sheet

The Invoice sheet is what I use to workflow and record all the invoices. It is done by a physical business unit so it also has Facility Maintenance Items in it..

But when I code the invoice and assign the invoice to the project I can also assign it to the "Sub Ledger" aka Budget Line it then populates to the lookup on the Budget Sheet.

Accounting loves this as they have all my invoices at a moments notice.

A Project Action Log

The project Action sheet I use to track just that buy stuff, fix stuff change stuff.. it holds my change orders and my purchase order commitment for stuff like FF&E.

So once a change is approved or something is ordered it is tagged like the invoice sheet to the Budget Line. It then shows a spending commitment in the budget sheet.

I have some more logic where I manually have to put in a deposit amount or progress draw from invoicing

An example would be $100,000 in Furniture which requires a 10% deposit and 50% remainder upon shipping and 50% upon installation completion. You can see this in the BDC Furniture line where the partial invoice is taken off the quote amount and the Budget committed is moved to the budget report.

So there is a reconciliation where I have to manually enter in the 10,000 then the 55,000 as the invoices come in lowering the committed amount. I use the Request-ID as a purchase order to issue to the vendor and when I load that in the PO# in the invoice system it shows up in the Action Log (just so I can reconcile a bit easier)

A Bit More

You can see from the budget report we are tracking in the red on the project items.. So I created more detailed descriptions, went back and coded the invoices a with those.

So I can present in more detail to the owners

So now I can break out the issues related to the Structural Issues we found and the charges related to the Civil work that the city required us to do in order to obtain permits or the fact we had to have 8 submissions for site plan approval.

And the best thing is now I can go to the Invoice Sheet and pull up all those invoices by filtering on the budget lines

New Reports Functionality

And now with the new reporting features, it opens up some more amazing things !!

I created this in about 10 minutes.. You can see how I look like a rockstar when people ask what is going on !!

Stephanie Viers

@bcwilson.ca This is fantastic! Nice work on this solution. I'm curious, to what degree does accounting interact with these sheets? Are they shared directly? Dashboard?

bcwilson.ca

https://community.smartsheet.com/discussion/comment/271856#Comment_271856

Yes, there are a few dashboards.. but more for navigation and accessing the reports.

Accounting gets a workflow email once the status of the invoice is set to approved..

It emails them as to what GL to place the invoice in as well as vendor information, notes and obviously the attached invoice. I also have the ability to tag as urgent and it will copy upper management in.

They then have to acknowledge it for processing by updating in the workflow via the email. It was too much work for them on top of the regular process to do it any other way.

So basically if I can ensure accounting has the invoice I am satisfied is now within their internal process for payment.

Also Controlling is looking at it in its raw format for Depreciation and financing for the construction. They love it because someone can call and say why is invoice 12345 booked against A rather than B.. they type 12345 in and pull up the PDF immediately.

This also helps me see where invoices are if vendors ask..

John DeLeon IV

@bcwilson.ca 

This is brilliant. Do you have a way for your contractors to submit invoices against an SOV charge(Dynamicview)? Or are you collecting these via email and uploading them to your invoice sheet?

It seems like you might have almost all the elements for creating connections between an ERP Job cost module.

bcwilson.ca

@John DeLeon IV .. I tried with dynamic view but I ended up just using 1 web form by project.

the vendor uploads the invoice.. puts some key details like invoice number and date and pricing..

then when they submit it routes to the PM and they code it against the SOV.. this way I can balance it a bit.. then it flows to payables for processing.. my company requires a wet signature so I have to download it.. stamp it in Brava Revu and upload it again.. but other then that pretty seemless

So if you look at my list I have even created some sub categories under the architectural fees as the city required us to do some civil work. That way I am still tracking under architects but for my knowledge I know exactly where the spend was in more detail.

I also tell my subs that if they load it in the web form it will be processed faster as I review open invoices every morning but upload them a few days a week..

so pretty much all of my subs submit them based on that statement.. I make sure that I do process them efficiently so the word at site is it works you should use it.. I have even created some custom reports for key vendors so that can come in and see the status.. (if accounting has posted, paid and what cheque etc)

Dan Eshleman

So, the answer is YES! This is awesome. Well done. I am currently drawing out a similar process. If you have any sheets you are able to send this way, I am willing to receive them. Man, this is good stuff.

Mine will be much simpler, but this is the Holy Grail right here!

Brent Wilson

Hello all..

My posting profile has been deleted if you have any questions on this reach out to me at this profile.. Thanks