COGS Query

[Marko2002]

Hi again guys, another query regarding my accounts. I resigned myself to using a spreadsheet, primarily because I am having difficulty working with any of the accounting packages and as I progress with my spreadsheet I am finding I'm picking up things better along the way and I am able to adjust the spreadsheet according to where I know I've went wrong.

My confusion is with the COGS at the moment. My understanding is if I buy 100 books for £100, then my cost per book is £1, I sell 50 and my COGS is £50. This, I get. However, my understanding is also that at the turn of the new financial year any remaining stock (i.e. £50 worth) has to be transferred across to the new period as it isn't yet sold.

As I utilise a spreadsheet, I have a book per line along with cost to purchase the book, and all the other costs like ebay fees, postage to customer, etc. This let's me see easily how to set my selling price because I can see the potential profit in a simple way. All sales figures and costs are automatically transferred to another sheet to give me a month by month summary of what's going on which is great so far, however, as I use a line by line approach in my spreadsheet I already have the cost of the books in each individual row on the sheet which calculates the total cost of the books to me along with a running total of the COGS for books sold. I have integrated this into the summary sheet but should the actual purchase cost of these books be marked elsewhere as a purchase because if I physically pay £100 from the business for stock, it has to show somewhere right? And if so, how does it balance out if I then enter the COGS and what happens at the turn of the financial year when I have to transfer the goods not sold to the new sheet?
Hope this all makes sense
Mark

 

[Chris Ashdown]

If you have the intelligence to be able to use Excel then you should be using a simple accounting product like Cashbook or similar

Accounting software does so much more than just entering invoices and also cuts your accountants bill probably in half

Get Real

 

[Marko2002]

So I can use a simple accounting product like Cashbook to show me 2,500 books and have it automatically show me, from the sale price, how much the ebay fee and paypal fee would be, book by book, together with cost of purchase, postage cost for that book, and profit for each book before I actually sell the book? If it does, then I'm definitely missing something, or as I suspect, do I have to input manually, and separately, the cost of everything after the sale?.

I use Libre Calc by the way, and I've yet to find a simpler, foolproof accounting program that can perform the way my spreadsheet does for my particular needs, I'm certainly no expert in accounting or using spreadsheets but I've created a system whereby I enter my book details on one sheet, it tells me exactly what profit I'll make, totals everything for me, and if I make the sale I update a column associated with that particular book and my summary sheet for that month is automatically updated with all the information I've already put in the row for the book. Expenses are another sheet, but automatically get inserted also into the summary sheet for that month, it works, and works well and probably has far more potential than any simple accounting program could ever have - the only thing I needed answered was regarding COGS and how I need to implement that within the sheet - if it's not something you can answer, let's not waste each other's time.

 

[GraemeL]

if it's not something you can answer, let's not waste each other's time.

That was uncalled for and unnecessary. You are writing relatively long posts which require more than a cursory skim to understand what you are getting at. You should be thanking anyone who has taken the time to bother to read and respond.

My answer to your question is that you are mixing up a P+L account with a Balance Sheet. They are distinct but affect one another.

I used to use a spreadsheet. They are are great. But prone to error and can get very complicated, relying on the users memory. Vat returns are a huge pain. They will give you much more detailed relevant information on individual product profitability than any accounts package can. They will also absorb more and more time.

An accounts pack will provide a lot more than your spreadsheet can on everything except individual product profitability, including an onging balance sheet .

 

[Marko2002]

Graeme, thank you for your response, I found the last posters response to be abrupt and off topic to an extent but to then finish with "get real" was plain rude hence my response, but I don't wish to digress.

I guess my question is mainly about accounting rather than accounting software and I'm struggling to work out how I account for, for example, a purchase of books for £100 and then the COGS associated with those books once they have sold. If I say each book cost £1, once sold the COGS is (approx) £1 but if I've already entered the purchase for £100, how does this balance - is the COGS offset or the initial purchase offset or is it something totally different that I'm overlooking.

Thanks
Mark

 

[Scalloway]

a purchase of books for £100 and then the COGS associated with those books once they have sold. If I say each book cost £1, once sold the COGS is (approx) £1 but if I've already entered the purchase for £100, how does this balance - is the COGS offset or the initial purchase offset or is it something totally different that I'm overlooking.

£1 goes to COGS, £99 remains in stock on the balance sheet until those books are sold.

You should be able to get a not too expensive accounting system that deals with stock.

 

[GraemeL]

I guess my question is mainly about accounting rather than accounting software k

@Scalloway is in effect saying the same as I did, you are mixing up a balance sheet with a P+L.

Yes, I tried accounting for stock (ie keeping a balance sheet) using a spreadsheet and that was a nightmare!

 

[Marko2002]

Thank you for the replies guys, and I appreciate the concern over using spreadsheets but it tells me everything I need to know at the moment, of course in time and hopefully as the business grows I will have to invest in an accounting program, particularly with inventory functionality, and I reckon if it's going to happen it'll be next year anyway as my line of business is currently seeing it's busiest period just now but will likely slow up after around Jan/Feb which will give me more time to concentrate on replacing stock and finally get round to figuring which accounting program to go for.
Thanks again
Mark