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 !!
