Spreadsheets..

[AutoJacob 34]

Good evening all

I'm drowning in paperwork and working solely from paper just isn't manageable anymore.

I would like to use spreadsheet(s) rather than use a bloated DMS and wondering what you guys use to keep all the accounting as straight forward and manageable as possible. 

If anyone can share examples of their spreadsheets and layouts then that would be greatly appreciated.

[EPV 631]

Single tab for each live car. 

Costs plus car purchase cost (SIV) less sale value is gross profit, carried forward to a summary sheet. 

That can then be used to calculate VAT and corporation tax or whatever you need to pay. 

When a car is sold, hide the tab, leaving the live cars and summary sheet only. 

Single tab for each live car. 

Costs plus car purchase cost (SIV) less sale value is gross profit, carried forward to a summary sheet. 

That can then be used to calculate VAT and corporation tax or whatever you need to pay. 

When a car is sold, hide the tab, leaving the live cars and summary sheet only. 

[AutoJacob 34]

9 hours ago, EPV said:

Single tab for each live car. 

Costs plus car purchase cost (SIV) less sale value is gross profit, carried forward to a summary sheet. 

That can then be used to calculate VAT and corporation tax or whatever you need to pay. 

When a car is sold, hide the tab, leaving the live cars and summary sheet only. 

Single tab for each live car. 

Costs plus car purchase cost (SIV) less sale value is gross profit, carried forward to a summary sheet. 

That can then be used to calculate VAT and corporation tax or whatever you need to pay. 

When a car is sold, hide the tab, leaving the live cars and summary sheet only. 

Thanks EPV, my background is IT, Systems engineer but I've never been excel savvy.. I shall do it this way.

Is your summary also your stock book or is this maintained separately?

Cheers

[EPV 631]

45 minutes ago, AutoJacob said:

Thanks EPV, my background is IT, Systems engineer but I've never been excel savvy.. I shall do it this way.

Is your summary also your stock book or is this maintained separately?

Cheers

I have a separate sheet for stock book mate. 

[Mark101 536]

I maybe Excel's biggest fan.

Everything in Excel from PDI, Invoices, Stock Book, Forecast, Turnover but the MOST useful to me (an evolution of the stock book tab) is the "Rolling Live Stock"

On this sheet I have columns for every conceivable expense on each stock car - at a glance I can see what I have spent, what is worth spending vs. likely sales price = gross, nett margin in £ and % ROI + VAT margin.

 

[EPV 631]

How do you deal with website and advertising costs Mark? I have a few items I call overheads as they are non Car specific. 

[Mark101 536]

15 minutes ago, EPV said:

How do you deal with website and advertising costs Mark? I have a few items I call overheads as they are non Car specific. 

I'm a sad old man James.

Like you (I think) I pay AT about £700 notes per month for about 7 cars (maybe 8 with a cheapie).  That's £100 per car per month (approx).  I then divide 100/28 = cost per day per car and multiply that by the number of days it took to sell that car = ad costs for that car.

EG £3.47 per day per car.

Stock 1 - days to sell 45 = £160.65

Stock 2 - days to sell 3 = £10.41

Not an exact science but it does help understand the true ROI per car.  Insurance is just a cost which cannot really be split per car unless I go really stupidly in depth.

i.e - Insurance £1500/12 = £125 per month/number of cars (12 for me usually) = £10 per car per month - you get the picture.

[Nick M.K. 574]

I used to have all these. Costs, costs, costs, you name it it was all there. Every penny. Horrible read. 

Then I deleted them all. 

Now I only have my stock book in Excel and I have to say: Ignorance is a bliss. I genuinely don't know what I am earning per month or year or per "unit sold". 

I know it may not be an obvious decision from a management point of view but my every year is better than the last. Just don't ask me exactly how much better. 

 

[EPV 631]

I'm kind of with Nick on this one, I don't really have a desire to know exactly how much I made per car, it will be somewhere between x and y. I like to know bottom line over a period of a few months, this business isn't linear. That said I do keep a close eye on costs and how I can reduce them.

[AutoJacob 34]

James, cheers for that I will do a separate spreadsheet for my stock book then.

Just to summarize, we've got a spreadsheet with each individual workings out for all the cars, a stock book spreadsheet and what else do you guys have?

I just want to cover all the bases and then I will confirm everything with our accountant and see what she says.

[Nick M.K. 574]

8 minutes ago, EPV said:

That said I do keep a close eye on costs and how I can reduce them.

Oh, this, absolutely. 

But because I run my business completely by myself I see every penny as I personally spend it and always try to pay bottom dollar for anything. Which used to make the spreadsheets even more painful :-)

[MrC 142]

I just stick everything in to quickbooks. 

 

[Mistermuttley 19]

6 hours ago, MrC said:

I just stick everything in to quickbooks. 

 

Snap!

 

[Mark101 536]

That doesn't do your VAT margin does it?

[MrC 142]

3 hours ago, Mark101 said:

That doesn't do your VAT margin does it?

You can do it manually

[justina3 518]

What’s everyone plans to submit vat when the new changes roll out as good as excel is it won’t interface with hmrc portal 

[MSP Motors 39]

I use Autosales, can't really fault it, although I have no idea how that would work with the new legislation. I know it syncs to sage, although don't really fancy using sage. Hopefully, they roll something out to make it compatible.