1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
Dismiss Notice
Have you checked if your business appears in our directory? Add your basic information for FREE!

Click Here to know more!!

If you would like links to source leads, upgrade from £12.50 a month!

Click Here to subscribe!
Dismiss Notice

Hi Guest!

Our website stays online because of the support of our advertisers. A huge part of them are from banner ads that appear on our site. While some of them seems to be intrusive for some, these ads are needed to keep our community running and continue providing free membership service for buyers.

In light to this, we request that you disable ad blocking programs or add our website to your ad blocker's whitelist. This keeps us from offering our basic membership to everyone for free and help with maintenance costs of our website.

If you have already disabled ad blocking programs or added us into the whitelist, please ignore this message, this message will disappear in a few seconds!

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


    Aug 22, 2009
    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


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

    Gary likes this.
  3. Gary


    Aug 22, 2009
    You're welcome Tracey, glad you found it helpful.
  4. aprilflower


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

    Mike Hills

    Aug 19, 2017
    I found this post very helpful too.
  6. Rita Berg

    Rita Berg

    Mar 17, 2019
    Every business needs to have a written business plan. Whether it’s to provide direction or attract investors, a business plan is vital for the success for your organization. But, how do you write a business plan?
Back to top

Share This Page