Sign in to follow this  
AutoJacob

Spreadsheets..

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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. 

 

  • Like 2

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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 :-)

Share this post


Link to post
Share on other sites

I just stick everything in to quickbooks. 

 

  • Like 1

Share this post


Link to post
Share on other sites
3 hours ago, Mark101 said:

That doesn't do your VAT margin does it?

You can do it manually

Share this post


Link to post
Share on other sites

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 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this