Basic A/P and A/R payment tracker

RRABruce

We're a property management company with a construction arm. Our construction is basic and requires us simply to track

Owner/Address/Date of approval/Job number (which we create)/Contractor's Bid/Change orders/Payments to Contractor/Balance to contractor/Our Bid/Change Orders/Payments to us/Balance due us/A/R for cash paid out still due/Job open or closed.

We currently use Google Sheets, but we're having a difficult time. Each job has a row, but that row must appear on multliple tabs, as the amounts paid to contractors and the amounts paid to us, as well as the cost of change orders, are all tracked on the different tabs. 

So, we have General tab that shows the summary of the job

We have the Contractor payments tab where we list the date and amount of each payment to the contractor, which Sums on the General tab to show total paid to the contractor, which is then subtracted from the amount in the Bid cell on the General tab to show the balance due the contractor on the General Tab.

We then have a Receipt tab, which functions exactly like the Contractor payments tab, but the amounts entered sum on the General tab to show how much we've received, which is then subtracted from our Bid cell on the General tab to show Balance Due on the General tab.

This is very messy. As the rows on the General Tab must match exactly with the rows on the other two tabs, or our income and expenses will be off. We also run into a problem when the job is completed, because if we want to remove the job from the general tab, we have to do the same to the payments and receipts tabs, but then lose the data, because everything is tied together. The A/R due from payables is also a mess a because I don't know how to get the cell to not create that number if the amount we've received is more than the amount we've paid out (as the current formula shows a negative when this happens.) 

I'm looking here for answers, but don't really the solution. 

I'd love to have a sheet where changes to the General tab created the rows in the other two or three tabs we need.

I'd love to be able to identify a job as done, and have it removed from the General tab, be archived elsewhere for reference, while not requiring me to change the landscape of the entire worksheet.

I'd love to be able to hover over the Paid to Contractor cell and see the check dates and amounts pop up in an auto-populated comment/note

I'd love to be able to hover over the Recd from Owner cell and see the check dates and amounts pop up in an auto-populated comment or note. 

A check box to indicate a job is Active or Closed that removes and archives the job data would be great

The Job Number cell auto-creating a job number based on a formula would be great.

Having cells that sum totals for the main headings as well.

I have an original sheet I can share. 

Having the contractor be from a drop down of contacts would be great.

I have no real need to manage the project, time lines, dates, subs, materials, or any of that. I just need to track when I got the approval, who's doing the job, how much they're expecting, my approved bid, how much I've paid the sub, and how much I've been paid. 

Anyone have a template that does this?

 

Andrée Starå

Hi, 

I'd happily work with you and help you with setting up a solution tailored to your specific needs.

If you'd like we can book a time for a free discovery call and take it from there. How does that sound?

I'm based in Europe/Sweden so it will be remotely if that works for you.

Have a fantastic week! 

Best regards 

Andrée 

Andrée Starå

Deleted because there is a bug in the forum that duplicate posts.

JGould

Good Morning, this sounds like something that could help me, was a template ever created for this?