1. Speak to our customer support team live today
    Dismiss Notice
Dismiss Notice
Thanks for visiting The Wholesale Forums! Register for FREE today and gain access to more information. Sign up with Facebook, Twitter, or Google.

Working Capital: What is it and how to monitor it effectively (sample excel doc included)

Discussion in 'Money, Accounts & Finance' started by Gary, Apr 5, 2013.

  1. Gary

    Gary

    Joined:
    Aug 22, 2009
    Messages:
    16,358
    Working capital is basically the difference between your incomings and outgoings, simply put it's income, minus expenses, equals to what you have left to work with, many people measure this as a working capital ratio and the average target ratio is to work at is between 1.5 and 2 times your outgoings, but some work on as little as 1.2 and some much more, so what ratio is best for you really depends on your business.

    So, how to work out the ratio:

    To work out your working capital ratio, instead of deducting your expenditure from your income and coming up with the total figure, you simply divide your income by your expenditure (ie. an income of 10,000 with expenses of 6,000 will give you a ratio of approximately 1.66 (10000 / 6000 = 1.66).

    Why measure it?

    When done correctly, by monitoring your working capital ratio, you can quickly and easily measure your targets and see if action needs to be taken, you can do this on just one business, or with a little extra work (duplicating the sample file and editing to suit), it can be done with multiple businesses or outlets in order to see what is working and what isn't, and then take appropriate action on each sector of your business where necessary, take me for example me, I have ProDemon and BayDemon, it could be used to monitor one or the other or both, so I could see if something is underperforming and take action if needed.

    So, what does it mean and what can you do?

    if your working capital ratio is too low then this could result in one day finding that you can no longer sustain your business, so it's time to a, reduce outgoings if possible, b, look at ways to increase turnover / profit with existing stock / services, c, clear out anything that is holding you back if necessary, and d, offer a new product or service to improve cash flow in order to help balance things up.

    However, having a ratio which is too high could be a sign of not putting your money to work properly for your business, therefore, you could decrease your working capital by increasing marketing campaigns, researching new products and ideas, making general improvements to how you operate to ensure that everything is as streamlined as possible, basically put your / your companies money to work, the returns will far exceed anything it could possibly achieve in the bank if done correctly and you properly calculate your risks and research your returns.

    One thing to remember with effective working capital management is to know your market, don't understock or overstock too much, by understocking you may find yourself unable to meet demand, but by overstocking you may find your stock devaluing in front of your eyes, so either know what sells and in what quantities, or have a supplier on hand that can get stock to you quickly should you need it or get an influx in custom.

    What is the easiest way to monitor it?

    The simplest way that I found to do it was to add a 'for records' section at the bottom of the income sheet (as obviously it has no resemblance to the accounts, just a personal record), then simply set a sum to divide the income by the expenses (overall working capital)

    However, I also like to ensure that any personal withdrawals don't take the business lower than I want it to be, therefore, I add a secondary "remaining working capital" row, which is just to add a row where you can input what you withdraw (either personal withdrawal if sole trader or dividend if a company), then use another sum below this to total expenditure including withdrawals, then below this, have your remaining working capital (which is again a sum, but this time it's income divided by expenditure inc. withdrawals).

    You do not need to use my sample excel sheets, you can simply add the above formulas to your own

    Personally I try to keep my working capital ratio between 1.5 and 3 (the higher to build up for a marketing campaign for example without dropping below the lower figure if possible), but even if you do drop below 1.5 that still means that you are getting in 50% more than you're paying out, so all is not lost, but that to me would be a sign to try to take action, as again, a low working capital followed by a slow period could mean the end of your business or needing to further invest or look for loans (if you can get one).

    Is it necessary to monitor it?

    If you're established and your income and outgoings are regularly around the same, then no, it's not quite as important, but in an ever changing market, especially for newer, smaller, or struggling businesses, then it could prove invaluable.

    Sample Excel Doc:

    I have added a sample excel sheet below for your convenience, this is mainly based on the majority of income coming via PayPal, but does have an "other expenses" column, so this would be all you need for your bookkeeping if using a separate bank and PayPal account for your business, simply input the total incomings from your monthly PayPal Statement (and print or download a copy for your records), obviously this may need editing to suit your business (as it was made to suit mine).

    It includes:

    Income page: Simply enter the values and it does the calculations for you (it also includes a refunds column and fee rebates for PayPal refunds), on the income page you will also see my "own records" section, this is where I display the value of remaining stock, the working capital ratio, total withdrawals and the remaining working capital ratio.

    It is also set to show green for profit or red for loss, the remaining working capital ratio is set to show red for under 1.5, yellow for 1.5 - 2, and green for 2 - 3 and red above 3, this can be edited under 'conditional formatting'

    Expenditure page: This obviously shows all the expenses, many of which are again automatically calculated or added in the correct location, there is also a section for 'packaging / stationary', 'other', and 'marketing' below this to keep track of what's being spent, simply add a row and input details as necessary, the totals will then be included in the above calculation.

    Ledger: This is obviously your record for items purchased, simply input date, item purchased, reference, total cost, and quantity, it will then auto calculate the 'cost per unit', then once sold, simply enter the quantity sold, this will then auto calculate the remaining quantity, cost of goods sold, and the value of the remaining stock, and send those numbers to the relevant sections of the income and expenditure pages.

    This is an annual doc and not monthly, however, this can be edited to suit.

    Please note: Just be aware that sometimes in excel when adding a new row (for example in the expenses or ledger), it doesn't always update the SUM function accordingly, so every now and then just double click the total figure and ensure the box covers everything in the column, if not, simply drag it from the bottom right corner until it does and click enter, same for a column, if the calculation isn't done properly click the box above and drag down to cover the row you just entered (sorry I'm not an excel expert and this is just something that I have done myself that may come in handy to some of you).

    Attached Files:

  2. beads

    beads

    Joined:
    Oct 5, 2011
    Messages:
    12
    This is a fantastic read. Thank you for putting this on.

    Tracey
    Gary likes this.
  3. Gary

    Gary

    Joined:
    Aug 22, 2009
    Messages:
    16,358
    You're welcome Tracey, glad you found it helpful.
  4. aprilflower

    aprilflower

    Joined:
    Jun 20, 2017
    Messages:
    13
    Very useful information. Sometimes it is really hard to find something similar to this guide. Thanks for sharing
  5. Mike Hills

    Mike Hills

    Joined:
    Aug 19, 2017
    Messages:
    5
    I found this post very helpful too.
Back to top

Share This Page