# How do you chart month to month profits RE: accounts receivable



## Easy Gibson (Dec 3, 2010)

I'm trying to get a good spreadsheet going so that I can track my sales/collections monthly in an effort to really just get a feel for how things are going.

I obviously have running tallies of gross/net per job and monthly totals, but I'm not sure how to deal with receivable balances that carry from one month into another.

This kind of scenario:

Signed contract for $3.50 on July 15th. Sale of $3.50 goes to July.
Deposit is collected on signing and second payment is collected July 25th.
Job is completed August 1st and balance due is invoiced as such. Which month do you credit the final payment of $.35? July or August?

I know this is kind of ticky tacky stuff, but I'm trying to get a handle on how to track this kind of stuff while I'm still small enough that it's not a huge ordeal to get all the numbers straight.


----------



## Texas Wax (Jan 16, 2012)

Cash or accrual?


----------



## blacktop (Oct 28, 2012)

I clean out my glove compartment once a month...:whistling:laughing::laughing::laughing:


----------



## Oconomowoc (Oct 13, 2011)

Texas Wax is correct. 

I don't like the accrual method personally. If you use accrual method, you should always use it and stick to it. 

I don't like counting the sale until I actually get paid. But that's me.

The key here is to do track expenses in a log (spreadsheet) but more importantly is to do post mortem after the jobs are complete to verify the math.


----------



## Oconomowoc (Oct 13, 2011)

I ran this past my wife just now, who is an accountant.

She said you would be well advised to keep a spreadsheet because you really can't tell much from a balance sheet. Obviously you know that already.

Many business set checkpoints along the way and each checkpoint has a threshold. Let's face it, what you are really tracking is expenses and labor. If a job is broke up into components and in the right order you should be able to measure against what you bid. If you bid a job by components that make up the total this becomes easy. If you bid based on square footage, well, things become hard to track.


----------



## Easy Gibson (Dec 3, 2010)

Thank as always guys.

I am absolutely awful at accounting lingo which explains why my brain nearly leaked out my ears when you said "cash or accrual?".

Really I'm just looking to get a system of accounting rolling that I can keep up with myself at the end of each day or week so at the end of the year I can see where my sales were strongest, what types of jobs I was selling the most of, what made me the best margins, etc.
I came out of a 4 year stint with a GC, so this business I started 6 months ago is all across the board. I've been painting, woodworking, handymanning, and I just got a call about doing some tiling. 
I want to be able to sit down in January of next year and say, "Well, we're never tiling again, that's for sure."

Whatever, it's been 6 months on my own now and the lights are still on, I must be doing something right.


----------



## Texas Wax (Jan 16, 2012)

Spread sheets or even the accouting pads, manual paper pencil are good. 

I've used a receivables aging spread sheet (cash) Actual invoices presented against paid. Works good see image.

In the past I've added below this area on the spread sheet the "projected" invoices, in monthly increments for projection purposes. With sub totals of to the right of each week or month projected. Using those totals to figure what revenue will 'likely' be coming in compared to the costs for that period. Low down and simple. You can tweek numbers and say what if this one don't come in and see how much clorox bleech (for the stains in the shorts) will be required, If you can't pay some bills or on teh flip side-drive you to finish and collect due to the pot of gold at the end of the excel rainbow LOL


----------



## Texas Wax (Jan 16, 2012)

BTW much more to a system - but this is the fundamental key absolute piece, in my last post

I do job costing on every job as part of the invoicing process. Have a Custom (DIY) database that tracks all of it. But it really is nothing more than an automated series of spread sheets... Spread sheets by themselves are just fine. Spread sheets are nothing more than automated accounting pads. 

Does not matter how, just that you start to "account" for your where your money is, where it went and the same for your time.


----------



## pcplumber (Oct 12, 2008)

*The answers don't seem to match the question.*

You asked how you chart your sales and did not ask about how to do book keeping.

For analyzing sales, you track your sales according to the day you close the sale and not when you collect the money.

At my business, I want to know how the $$$ of sales closed every month. We have a separate MS Access database and we would never use a spread sheet because the data and reports from spread sheets are very weak while databases are very powerful and easier to use.

There are many critical things you cannot get from book keeping and you cannot run a business efficiently by integrating everything in one software program.


----------



## hdavis (Feb 14, 2012)

One of the limitations of spreadsheets is down the road you may want to pull different sets of numbers than you do now. A database approach is the most flexible and the most time consuming to set up, but it allows you to look at pretty much anything in about any way you want.


----------



## Creter (Oct 13, 2009)

So learning the database along with spreadsheets is a two for one hit out of the park? They both are very good tools to have knowledge of?


----------



## Texas Wax (Jan 16, 2012)

Creter said:


> So learning the database along with spreadsheets is a two for one hit out of the park? They both are very good tools to have knowledge of?


Yes and no... Data base requires a different thinking. It is a logic based thinking. Thinking in sets, sub sets, and matching key information. Relational thinking as to how the structure will allow tables (spread sheets as such) to sort, place and retrieve data from other tables. (tables being smaller sets of data within the greater data base)

Me it was a progressive (obsession) from tracking data and accounting on accounting paper to spread sheets. Coolest thing ever to me in '88. Removed the manual calculator errors and modifying entries (5-6 times) did not physically destroy a sheet of paper. Lotus 123 in DOS. 

Limitations with spread sheets-You will end up with a sheet for invoices, check register, journal accounts.... that are all related and need to be reconciled on another sheet. With today macro's built into them it is possible, but a bit clunky-maybe for many. But in essence you have created a one way data base, non relational. You can readily aquire data from one spread sheet to fill into another or sort on another and create forms & reports that do the same. In a data where you can not in spread sheets.

Want to look at it a DIY Data base? Lotus Smart suite can be found for under 140 in small bills. Last copy I snagged was 70, legal. Or purchase Access as part MS Office.

A few screen captures for any calls of "BS" on the DYI D-base. This is something that 'interests' me. Been developing this ability since the early 90's, as a hobby...and it's made learning accounting and many of the other mundane clerical aspects of a business TOLERABLE. I would recommend looking at other pre-made or online options tho. 4k for an app oriented toward contracting = 100 hrs at 40/hr. That's a lot of time :whistling But learning and setting a a fairly complex data base 2-3x that


----------



## Creter (Oct 13, 2009)

Texas Wax said:


> Yes and no...


Texas, thank you for the nice reply.

Upon reading what you wrote, I sense that I am following a similar path that you have already taken.

As always being as far from the 'business' side of things my entire life all the ways of the 'accountant' are very new to me.

What I used to put on paper (lots and lots) I am now slowing transcending to the digital side of things.

This past weekend I mentioned to my wife how amazing spreadsheets are. How I can put my mind's thoughts onto a digital page and it makes sense.

Very exciting stuff in that the 'rabbit hole' is very deep and so much to learn and develop skills in.

I looked into MS Access earlier before you posted. I am leaning to trying this out as the yearly subscription gives access to all their products along with a year of XBOX LIVE (which is 50 bucks if one is into streaming NetFlix etc...)

I am a Google Head at the moment due to the Drive Feature along with the price tag as it is free. Yet, 100.00 investment into this MS thing seems to be an acceptable step for me. 

Thanks again.


----------



## Eric K (Nov 24, 2005)

Easy Gibson said:


> I'm trying to get a good spreadsheet going so that I can track my sales/collections monthly in an effort to really just get a feel for how things are going.
> 
> I obviously have running tallies of gross/net per job and monthly totals, but I'm not sure how to deal with receivable balances that carry from one month into another.
> 
> ...


Are you still using the 1936 contractors price guide?? Sorry I had too.


----------



## Easy Gibson (Dec 3, 2010)

Eric K said:


> Are you still using the 1936 contractors price guide?? Sorry I had too.


Is $3.50 not a reasonable rate to charge for a day's work?

That's what most of the guys here seem to charge for everything. I figure why rock the boat?


----------



## hdavis (Feb 14, 2012)

Free database comparison:

http://www.networkworld.com/reviews/2012/120312-databases-test-263922.html

Top downloads (windows):

http://download.cnet.com/windows/da...ows+7|licenseName=Free|&rpp=&sort=&tag=narrow

Database design takes some experience. Similar to spreadsheets, if you decide later that you need to add something in, it can be time consuming. You could end up creating a whole new database and populating it with data from the old database.


----------



## Texas Wax (Jan 16, 2012)

hdavis said:


> Database design takes some experience. Similar to spreadsheets, if you decide later that you need to add something in, it can be time consuming. You could end up creating a whole new database and populating it with data from the old database.


Research GUI for SQL based d-bases. You will need one unless you know "Mysql" / "SQL" programming and the PHP or C+ to integrate it windows or on the web.


----------



## Eric K (Nov 24, 2005)

Easy Gibson said:


> Is $3.50 not a reasonable rate to charge for a day's work?
> 
> That's what most of the guys here seem to charge for everything. I figure why rock the boat?


You took tree fiddy to a whole new level.


----------



## Easy Gibson (Dec 3, 2010)

Eric K said:


> You took tree fiddy to a whole new level.


I did a small project yesterday that literally came exactly to tree fiddy labor and materials. I was giggling the entire time I was printing the invoice.


----------



## concretemasonry (Dec 1, 2006)

From an accounting and business standpoint, the accounts receivables is the most important item to keep the business on track. If you do not get paid or can estimate what might be coming in, it is difficult to keep the wheels going and avoid short-term cash problems.

With no surprises, it gives you a chance to look at individual projects AND types of projects (big, small, new, remodeling new customer, etc.) to make planning easier. A good accounting system by someone else will provide the history and the real results of the past.

Jusy make sure you know what categories cost are allocated to, but that is not difficult to do for a good professional. - Do not let an emotional wife do the the books.


----------



## hdavis (Feb 14, 2012)

concretemasonry said:


> From an accounting and business standpoint, the accounts receivables is the most important item to keep the business on track.



Whoa, take some Bschool. Free cash flow keeps you out of the ditch, but that doesn't "keep the business on track".


----------



## Marven (Jul 15, 2013)

Easy Gibson said:


> I'm trying to get a good spreadsheet going so that I can track my sales/collections monthly in an effort to really just get a feel for how things are going.
> 
> I obviously have running tallies of gross/net per job and monthly totals, but I'm not sure how to deal with receivable balances that carry from one month into another.
> 
> ...


Introduction: I've been an accountant for 30+ years and I build every summer with Habitat for Humanity.

You will need more than one spreadsheet to do what you want. You already have the main spreadsheet that does job costing. When you post a sale, you record the income (100%)(accrual basis). when you receive a payment on the invoice, you don't post it to sales again.

You could create another spreadsheet called "cash flow". When you make a sale, you enter nothing. When they pay you, you enter what they paid. When you pay a bill, you record it. This spreadsheet doesn't show profit or loss, it shows cash flow.


----------



## concretemasonry (Dec 1, 2006)

hdavis -

I never said "FREE CASH FLOW". I was looking from a standpoint of someone had some sort of accounting structure. It is NOT FREE since it was earned and entitled to be be paid, so it is important to the person running a business on a day to day basis to keep the thing going. Monthly statements (P&Ls, Cash flows, Accounts receivable and Balance Sheets, etc.) are good as far as they go in the big picture. Unfortunately, they are usually not available until 10 or 15 days after the end of the month. That is far to late for a small contractor/supplier.

By the way, I have been a consultant for many operations large (up to $35,000,000 ) and small (as small as small as $100,000) in many industries with more than enough experience/degrees/certificates through 35 years of working and saving/making money for new and existing operations.


----------



## pcplumber (Oct 12, 2008)

*Over-complicated!*



Marven said:


> Introduction: I've been an accountant for 30+ years and I build every summer with Habitat for Humanity.
> 
> You will need more than one spreadsheet to do what you want. You already have the main spreadsheet that does job costing. When you post a sale, you record the income (100%)(accrual basis). when you receive a payment on the invoice, you don't post it to sales again.
> 
> You could create another spreadsheet called "cash flow". When you make a sale, you enter nothing. When they pay you, you enter what they paid. When you pay a bill, you record it. This spreadsheet doesn't show profit or loss, it shows cash flow.


If I am right about what you are saying you have one column on your spread sheet where you enter the amount of a sale and the date the sale was closed. Therefore, you must also have another column that shows the credit (cash in, deposit, or whatever) and you add each payment in this column when the customer makes the first payment and when the customer makes additional payments. 

If I am right you cannot add the total sale amount to the 'Cash In' column because it would eventually show that the customer paid too much money. Therefore, a person may as well not have a column for sales on the spread sheet and may as well make a separate database for sales that does a better job tracking sales data.

Spread sheets are for amateurs. Sorry! Throw the spread sheets and Quick Books in the trash and use the simplest and most accurate system in the world. Use the cash system and take everything from your checking account every month. This way, your records will be accurate to the penny and your accountant and the IRS will be impressed. 

If you want to see a system and the software that handles several million of dollars every year and is accurate to the penny I will post it.


----------

