Cash Flow Accounting Excel Spreadsheets

Cash Accounting.xls provides:

100 accounts payable and receivable in Cash Accounting.
100 receivable accounts in Ageing.
85 Cash Flow item rows in 150 Days Cash Flow Forecasting
An expansion edition is available "off the shelf" supplying 250 accounts payable and receivable (1 unit).
An expansion edition is available which provides 250 receivable accounts in ageing (2 units).
An expansion edition is available which provides 500 item rows in the 150 Days Cash Flow Forecasting plus the Print Summary page (1 unit).
Expansion units cost $40.00 each. If more than one expansion is purchased, the expanded workbook contains all upgrades.
Special Offer:
Amazing Savings. Double expansion units for $40.00.
Save $40.00 with each upgrade any time over five years.
For our Gold Edition customers, if you find that you need to upgrade your workbook(s) any time over the next five years,
you can lock in* double “Off the shelf” expansion units for the price of one, saving $40.00 with each double upgrade.
* You will need to select the free Extended Download Service when you order to lock in five years expansion discounts.
We can also adjust the look and feel or want extra features for you.
The cost of adding extra features is $125.00 per unit of development work.

Excel Dates Synchronization with User's System Clock

All our three Management Accounting workbooks use Excels' date and time functions to link the program to the current (today's) date and the current month end period.

When the User's system clock changes date and month, both the Cash Accounting and Aged Receivable spreadsheets update values to a new date and month end.

To ensure that date synchronization works properly the user needs to check that (a) their system clock (on the tray at the bottom right of your screen) is set to the correct time and date, and also that they have Excel's ToolPak VBA switched on as shown below.

caccount1.gif (30134 bytes)

caccount2.gif (70098 bytes)

caccount3.gif (75173 bytes)

Date formatting

If the dates you see in this demo are out of range and do not display properly, you need to adjust the Long date settings in your Control Panel.

Follow these steps:

Click Start, bottom left hand corner and then Control Panel.

Select Date, Time, Language and Regional Option.

Select Change the format of numbers, dates and time.

Select Customize in the second image.

Select the Date tab and change the Long date format.

Instructions

These instructions and the others which follow are for users working with the Program Version. This is a demonstration only.

You can set up your Cash accounting quickly in four easy steps.

1. First, create the accounting categories that fit your business in column C of the Cash Budget spreadsheet.

   (Use the corresponding accounting codes provided in column B in the Budget page to post each account to the correct category in the Current Month table).

   (Cash Budget is an input table).

2. Enter receivable and payable accounts into the Current Month table together with the due dates and category assignments.

    (Use the Days Clear Bank to forecast the transaction date through your bank account if you wish, or just enter 0 days to skip this nicety!)

3.  When an account is settled (you pay or receive a check or cash) tick the appropriate Transacted box.

   (Ticks sort your accounts into due or settled).

4. When the month changes, switch the budget in the Cash Accounting page to the new month so that you can track how your finances are unfolding compared with your new budget.

The Cash Accounting table sorts accounts within the Current Month table into individual Categories and either due (payable or receivable) or settled,

so that you have a complete updated picture of your finances every day.

Your Cash Accounting can be updated any time throughout any monthly period, giving you a real time overview of your business finances on a daily basis.

Changing Month Ends.

The Month End date in cell G5 of the Current Month changes in step with your system clock. If you need to "freeze" the month end to the previous

month, use the Days adjustment cell E19 in Set Up and enter sufficient minus days to bring the date back to the required month end.

For speed entry of receivable and payable accounts into the Current Month list, drag your mouse along the column headers..

Pull down the Data menu and select Form.

caccount4.gif (40106 bytes)

Setup

caccount5.gif (5534 bytes)

Note: If you change the Budget start date and the dates in row 3 of Budgets does not change in your Excel, press Cntrl+Alt+Return and force the date recalculation. To wind the month end backwards, change the apparent "Todays" date by entering a minus days value into E19.

Cash Flow Budget

caccount6.gif (12577 bytes)

Account codes (G/L Ledger) codes define the income/expense category as each account is posted.

You can change the title of any Category, but remember to use 1-5 for income and the remainder for cash out.

Use the Filter at the top of the Accounting Categories column for a quick focus on the budget figures for any Category.

Current Month

Use this table to schedule and transact receivable and payable accounts, capital, lease payments etc for the current month.

Click "Transacted" when an account is settled.

To reduce the table size adjust the zoom %.

At the start of each month, enter all outstanding expense commitments and accounts receivable. Enter new payable accounts and due dates as they arise. Enter settlements using the "Transacted" tick boxes. Allocate each payable and receivable with their Category code number in the Category Code column.The Late column displays items past their scheduled due dates. Use the filter button to sort all Lates together as explained to the right of the table.

caccount9.gif (40357 bytes)

Cash Flow Accounting

Screen image of the Cash Accounting report - plus budget variances - month to date.

Part 2

CONTINUOUSLY AGED RECEIVABLE SPREADSHEET

Simply enter receivable accounts into the next spreadsheet and the table ages each account as your system clock advances daily, and switches overdue accounts into advancing aged columns.

INPUTS:

Enter current receivable accounts into columns D-F of the Aged Receivable spreadsheet.

Each time you open the Aged Receivable spreadsheet Excel calculates the number of days between each invoice date and your system clock date.

The spreadsheet switches receivable accounts to their correct aged column.

Tick when a receivable is paid. This removes the account from the Currently Outstanding column.

You can change the reference date to match any exact month end date.

You can filter receivables to display those in excess of a selected age from the date of invoice.

The spreadsheet uses the date setting of your system tray clock as the reference date (Today's date) to calculate the number of Days Outstanding.

Make sure that your system clock date is correct (!), otherwise the table will calculate incorrect aged values.

The reference date is today's date unless you adjust the date.

To change the reference date (to adjust to a month end), enter the adjustment +/- days into E19 within Set Up.

To filter the table to display only receivables aged more than a defined number of days, use G5.

To reduce the table size, reduce the Zoom factor to 75%.

Aged Receivable

This spreadsheet ages accounts receivable as your system clock advances. In the program edition, Days Outstanding increment each day for each account. In the program edition, Days Outstanding increment each day for each account. Receivables can be entered in any date sequence. When an account has been paid, tick the appropriate Paid box. To filter receivable accounts enter the target days outstanding into G5. The single download file provides 100 rows for receivable accounts. The number of rows can be expanded with the All Programs Gold edition.

This spreadsheet uses the current date setting of your system tray clock as the reference date (Todays date) to calculate Days Outstanding. The reference date is today's date unless you adjust the date in Set Up. The table updates aged days calculations automatically each time you open this worksheet in the program edition.

Part 3

CASH FORECASTING - CASH BURN FORECASTING SPREADSHEET.

When you need to forecast cash burn over the next N days, use the 150 Days Cash Forecasting spreadsheet.

(N = any value up to 150 days).

INPUTS

Enter Cash In & Cash Out and forecast dates into columns C, D & F. List amounts in any date sequence.

The Due days column makes the table calculate working day dates for credits and debits (Skips weekends).

The spreadsheet calculates daily cash balances and helps you summarizes balances after any number of days.

To check cash balances at any number of days after the starting date, enter the values into the Look up Days columns.

No data is required to be entered directly into any part of the spreadsheet from column G and to the right.

The payable or receivable amount enters the cash flow table under control of its scheduled (settlement) date and bank clearing.

The screen image from the program displays the Precedents.

150 Days Cash Forecasting

caccount_1.gif (25383 bytes) caccount_2.gif (22000 bytes)

. . . . . . . . .

caccount_3.gif (16676 bytes)

Print Summary Option

 

PRINT SUMMARY EXPANSION.

Several customers have requested a condensed Print Summary of the 150 Days Cash spreadsheet, which is now available as an expansion upgrade.

Enter the number of days ahead of the start date you want to summarize and print, into cell D6, and the table sorts the 150 Days spreadsheet by Clearing Dates.

You can filter the presentation to hide all non appearing rows (Clearing date < Chosen End Date).

caccount_end.gif (29168 bytes)

Cash Flow Accounting Excel Spreadsheets are supplied as as part of Cash Flow Excel Spreadsheets

Business Software

MS Excel Add-ins, Spreadsheets, Templates