Hands on: Managing your cashflow

Glancing at this month’s headline, don’t assume that this column is solely for small business managers.

It is just as important for family households to manage their savings and, anyway, the example given here is just one illustration of how to use the valuable Solver Add-In provided with Excel.

Nor, to try it out, do you have to enter all the formulas used in the example illustrated.

If you have Excel you will already have the template. With a normal installation you’ll find the file SOLVSAMP.XLS in the path, C:\Program Files\Microsoft Office\Office 9, 10, 11 or 12\Samples.

Open the file and click on the tab ‘Maximising Income’. The worksheet heading is ‘Working Capital Management’.

On a company Balance Sheet, Working Capital is the difference between Current Assets and Current Liabilities. For a family, this Solver example helps manage cashflow.

Every month you’ll want to put money aside in interest-earning savings for future big expenditures such as holidays, Christmas or a new conservatory.

But you also need to keep sufficient cash to pay the regular bills as they come in.

Some months you’ll need more cash than others. This worksheet will help you work out how much to invest each month to earn maximum interest and how much to keep in hand.

The illustrated example shows the template in SOLVSAMP.XLS with minor variations.

The dollars are changed to pounds. The formatting is more legible for magazine reproduction.

The objective in this example is to help a company earn maximum income from spare cash invested in certificates of deposit while keeping enough in hand to pay bills. Incidentally, the entries in the range B6:B8 are not annual interest rates.

They are estimates of the yields returned by CDs (Certificates of Deposit) from their purchase to redemption.

Row 11 shows the estimates of cash the company will have at the start of each month (except for G11 which gives the amount of cash at the end of the sixth month).

The starting amount is the same as the ending amount of the previous month so =B18 is entered in cell C11, =C18 in D11 and so on.

Before the Solver gets to work, the plan is to put £100,000 in one-month CDs at the start of each month, B14:G14. These CDs mature and feed back into the equation in C12:H12. The interest they earn is recorded in C13:H13.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!