Spread sheet accounting system
Where a business has very few transactions and management are able to gauge the operating income and solvency by viewing the cash book running balances and expenses and income as analysed in the cash book. Preparation of trial balances and financial statements may only be required once or twice a year. Then spread sheets can be used to ascertain the accurate financial position as and when required.
The cash book can be prepared on a spread sheet with the bank column on the left-hand side and the income and expense columns spread across the page.
The general ledger can also be prepared using a spread sheet with the ledger accounts reflected on the left-hand side of the page and the balances forward in the first column and the current year transactions in the columns to the right.
The year end totals will then be reflected in the right-hand column by cross casting the figures reflected in the line on which each ledger account name is recorded. This can be done manually or electronically by using electronically designed spread sheets.
The electronic spread sheets provide functions for adding across the page or down the columns. The spread sheets are also designed so that there are columns spread across the page which one can adjust the width of to accommodate the figures and totals which are entered or generated in those columns.
When designing your general ledger spread sheet it is ideal to reflect the income and expenditure accounts in the top section of the spread sheet so that one can total the top section of the spread sheet to establish the profit or loss for the year, or period if the time frame is less than a year. One then adds from the subtotal to the end of the column to balance the general ledger, the asset and liability accounts being in the bottom section of the spread sheet.
When using the electronic spread sheet, one uses positive and negative numbers. If one uses the positive cash book balance as a positive number then one must use a negative sign when recording the payments and a positive sign for deposits. When recording the total of these transactions in the general ledger spread sheet one has to transpose the signs. Because the cash book forms part of the general ledger one is
required to cross post to the expense and revenue accounts.
Also, it must be remembered to only record the movement in the bank account balance. Often it is a good idea to just journalise the cash book movements for the period especially where there are a lot of entries in the sundries column. If one uses too many columns in the cash book, then there is a problem printing the cash book as most printers are limited to A4 and A3 paper sizes.
When using spread sheets, one may either work on a payment basis recording expenses and sales as and when payment take place or raise debtors and creditors via sales journals and purchase journals, and then record receipts from debtors as such in the cash book spread sheet and payments to creditors in the creditors column in the cash book spread sheet. If one works on the receipts and payments basis, then one must raise the outstanding sales invoices at the year end date by journal entry and similarly the amounts owing to creditors are raised by journal entry.
Example 1
Below is the cash book of ABC Electronics Ltd, a company supplying and installing sophisticated manufacturing plant which it sources from a local importation agent. This company relies on its expertise in installing complicated electronic equipment and work is sourced by word of mouth and referrals from importing agents of electronic equipment. The cash book has been prepared for the first two months of the year.
Below are the journal entries of ABC Electronics Ltd. The cash book has been journalised in order to change the signs to accommodate the cross posting and to limit the cash book to an A 3 width for the purpose of printing the working papers. The journal credit entries have a minus in front of them which accommodates linked entries between work sheets in the spread sheets if this facility is provided.
The general ledger of ABC Electronics Ltd is as below
The above general ledger spread sheet has been prepared using an electronic spread sheet package. The benefits being that the additions both across the page and down the page are performed automatically once the addition functions are activated. This also allows for easy changes when entries need to be changed due to entries being overlooked or management salaries being adjusted after the initial profit
has been determined.
Spread sheets are often used by management of companies with sophisticated accounting packages during the year to avoid raising and reversing certain provisions which may alter during the year and some of these provisions are only determined with absolute accuracy at the year-end date. Today trial balances can be electronically imported into a spread sheet at the touch of a button, estimates of bad debts, closing stock figures, provisions for court actions, can be made which may alter significantly by the year-end and are therefore made outside the general ledger until such time as the final figures are available.
Spreadsheets
Spreadsheets are ideal for fixed asset registers. Limited companies are obliged to maintain a fixed asset register and below is an example of the fixed asset register of ABC Ltd
In the above example the residual value is estimated to not drop below twenty percent of the purchase price and is immediately deducted from the cost of the asset. The balance remaining depreciated at fifteen percent of the reduced value on a fixed line basis per annum
Using the same assets but not providing for the initial residual value and calculating depreciation on the original cost the fixed asset register would be as below: –
In the second example the rate of depreciation is higher, but depreciation is a notional exercise as one chair will have a lifetime different to that of another chair of the same make and design. In this example it is necessary to determine if the assets have a value of not less than the value in the register at the end of each year.
Spread sheets are also ideal for calculating interest on loan accounts.
Especially where there are multiple entries in the loan accounts and interest rates fluctuate during the year or period. In small private companies the main shareholders provide bridging finance from time to time by depositing money into their loan accounts. Once the need for the finance has passed, they withdraw the excess funds. They often live off their loan accounts until such time as their annual salaries have been determined. Loans are also made between group companies and interest paid on those loans.
The above spread sheet has been prepared for the month of January 2020.
In practice one would prepare the loan account for the whole year and the above working would be extended by dragging down the formula in each column in the above example. The totals would be moved down to the bottom of the spread sheet.
The interest charged in the above example can be altered from any date reflected in the spread sheet by merely changing the rate of interest in the formula in the interest column.