About Each Workbook

Summarise and reconcile any bank, credit card or loan account.  First year 2021.

Calculations/Year

Use this worksheet to reconcile the profit/(loss) shown in your client’s accounting system (xero, myob, quickbooks etc) with the profit in the financial report prepared by your practice.  Managers and partners can use this worksheet prepared by their staff to help review a completed job.

You can give your client a copy of this calculation to help them understand the changes made to the profit calculated in their own accounting system.

Calculations/Year

Calculate adjusted taxable income (ATI) to determine –

• if your client can claim a tax offset for –
  • zone or overseas forces
  • net medical expenses for disability aids, attendant care or aged care
  • invalid and invalid carer
• whether your client’s child is considered a dependant for Medicare levy purposes
• whether your client is entitled to government super contributions

Calculations/Year

Record the acquisition of an asset, calculating funds required, funds borrowed after any deposit and/or trade-in, depreciable cost, GST paid on acquisition, profit/loss on sale of trade-in and GST collected on trade-in. If the asset is a car, the car limit is applied to limit the depreciable cost and input tax credit.

Calculations/Year

Using ATO methodology for audits of returns, there are two worksheets to self-assess your client’s audit risk.  They are –

1. Using a “T” account, calculate funds available and funds used to compare the result with your client’s return; and
2. Calculate annual movement in assets, add back private expenses, non-deductible losses and tax adjustments and deduct non-assessable receipts to arrive at an estimated taxable income to compare with your client’s return.

Calculations/Year/ATO Audit – Asset Betterment
Calculations/Year/ATO Audit – Funds Available

Simple bank reconciliation.

Calculations/Year

This workbook comes in two versions to cater for either monthly or quarterly BAS lodgement.

Calculate the June (month or quarter) BAS figures as the “balancing figures” for the year. Ensures that where a client has made changes to their own accounting software data after you prepared their prior activity statements each month or quarter, the annual total will be correct.

Calculations/Year

Reconcile GST on supplies and acquisitions in a BAS for a period with amounts shown in the client’s accounting records for that period to highlight any coding errors.

Calculations/Year

Summarise activity statements lodged and payments made for the financial year (or substituted accounting period) and reconcile with GST collected, paid and owing in the accounts.  A separate sheet reconciles the FBT expense and liability shown in the accounts with instalments and final tax paid/refunded and the FBT return itself.

This workbook also includes GST and PAYG Instalment worksheets for those practices which lodge activity statements using the ATO Portal.  Worksheet totals can optionally be transferred to the summary sheet.

Calculations/Year

BAS working papers are available in several versions to suit the needs of your practice and the particular demands of each job.  Within each version there is a separate workbook for monthly, quarterly or annual lodgers.   All versions below produce a summary of GST collected and paid for the preparation of Business Activity Statements, along with an annual summary of transactions for the preparation of the client’s income tax return. The comments below provide guidance on when to use each version of these workbooks.

Summary
Use where the client provides a summary of transactions for the period and no further detail is needed for your working papers.  Enter the client’s totals to this workbook to quickly produce a summary for both the BAS and income tax return.

Standard
Use where the client provides records showing individual amounts for each transaction and you need to calculate totals for the BAS and no further detail is needed for your working papers. This version has worksheets for each period which can be used to add the figures and keep a record of your calculations. Additional information can be entered for capital acquisitions (asset description and date of acquisition).

Detailed
Use where a much greater level of detail is required for the job. A separate transaction screen allows entry of Month, Date, Reference, Account Name, GST Type, Narration and Total Amount for each transaction. All of these columns can be sorted and filtered for maximum reporting capability.

Cashbook (quarterly)
Same as the Standard version above but with bank/credit card reconciliations.  Use where the client provides bank and credit card account statements from which you enter data and reconcile with closing balances for up to six separate accounts and you need to calculate totals for the BAS and no further detail is needed for your working papers. This version has worksheets for each period which can be used to add the figures and keep a record of your calculations. Additional information can be entered for capital acquisitions (asset description and date of acquisition).

Taxi Drivers
There are monthly and quarterly versions of this workbook which will calculate the figures for a BAS and income tax return as well as compare the figures with ATO benchmarks.

Calculations/Year

These workbooks follow the format in the ATO form titled “Taxi Driver Takings & Expenses Worksheet”.  There are two ways you can use these workbooks –

• Give the client a copy of the paper form referred to above to complete and at the end of each period your practice can enter those details into the workbook; or
• Give the client a copy of the workbook where appropriate for the client to complete and send to your practice at the end of each period for completion of their BAS.

Each workbook produces a summary of GST collected and paid for the preparation of Business Activity Statements, along with an annual summary of transactions for the preparation of the client’s income tax return.  Also, driver takings are compared with the Small Business Benchmark for Taxi Drivers, which is calculated by multiplying the number of kilometres entered to the worksheets (or your own estimate) by the rate per kilometre set by the ATO.

There are two workbooks covering monthly and quarterly lodgers.

Calculations/Year

Our ATO Small Business Benchmarks workbook contains the very latest performance benchmarks released by the ATO for around 100 different industries.  Practitioners should check their client’s performance against ATO benchmarks before lodging their return and have appropriate discussions where the client’s figures are outside the key performance ratio specified for that industry to avoid potential audit activity.

It will only take a few minutes to enter details from your client’s income tax return and activity statements (where applicable) and the workbook will perform the following calculations –

• Cost of Sales/Turnover;
• Labour/Turnover;
• Motor Vehicle Expenses/Turnover;
• Rent/Turnover;
• Total Expenses/Turnover;
• Non-Capital Purchases/Sales; and
• GST-Free Sales/Total Sales.

These ratios are then compared to the benchmark for that industry and the following is reported –

• Client Ratio vs benchmark ratio range;
• Expected range of amounts using ATO benchmarks;
• The variation from those ranges;
• A status for each ratio (OK or Fail);
• The turnover range that the ATO will expect to see on the return based on the key benchmark ratio and any variance therefrom;
• The turnover range that the ATO will expect to see on the return based on the total expenses/turnover and any variance therefrom where businesses do not report the key ratio or only report a small amount; and
• The variation of client activity statement ratios compared to ATO benchmarks.

Calculations/Year

Use this workbook to write off over 5 years certain business capital expenditure (“blackhole expenditure”) not denied by some other provision. See immediate deductions for small & medium businesses below.

Eligible Costs
1. Establishing a company or other business structure
2. Converting your business structure to a different structure
3. Raising equity for your business
4. Defending your business against a takeover
5. Unsuccessfully attempting a takeover
6. Stopping carrying on business (including liquidating a company).

Small & Medium Businesses
Note that from 1 July 2015, a start-up company, trust or partnership can immediately deduct a range of professional expenses associated with starting a new business, such as professional, legal and accounting advice.

Add and/or subtract an unlimited quantity of numbers, deduct any private component and print or save the calculations for subsequent retrieval or updating.  A much better option than using a calculator.  Keep this workbook open all day while you work.

Calculations/Financial Calculations

Calculate the annual amounts to claim for up-front borrowing expenses.  Features include –

• Amortise expenses for up to 10 loans;
• Show unclaimed balances at year-end over the write-off period; and
• Amortise unclaimed borrowing expenses brought forward from the previous year.

Calculations/Year

Calculate the revenue required from either the sale of goods or services to break even.  Features include –

• Enter gross profit ratio and fixed costs to an expandable table to calculate sales or fees required to break even;
• Enter the average sale amount to calculate the number of sales required to break even;
• Enter the average hourly charge-out rate to calculate the number of hours to charge to break even;
• Enter desired profit to calculate revenue and the number of sales or billed hours to achieve that profit; and
• Enter notes about your calculations.

Calculations/Budgets & Projections/Break-even Analysis

This is a multi-purpose budget workbook with monthly analysis designed for easy use and speedy data entry.  Here are some of the many ways you can use this valuable practice resource –

Standard Budget
For an existing business, enter current and forecast data to produce an annual budget with monthly analysis by activity levels.

Result Budget
For a new or existing business, enter the Desired Net Profit and the budget will be calculated from all other parameters.

New Business
For either the establishment of a new business or acquisition of an existing business, enter an estimate of the income and expenses to evaluate the profitability or otherwise of the venture.

Break-Even Analysis
All budgets will display the break-even position in dollars and quantities by week, month or year, based on forecast figures.

Sensitivity Analysis
Use Sensitivity Factors to enter positive or negative changes to prices, turnover, expenses, other income and conversion and productivity rates to observe the effect on the budget.

Revenue Sources
The budget will separately account for the sale of goods and the sale of services.  This will allow forecasting and monitoring of the different activities that produce this revenue.

Activity Analysis & Targets
Analyse business activity including quantities based on the average sale amount for the sale of goods, hours billed and productivity based on the average job income for the sale of services and conversion rates for leads for both the sale of goods and services.  These indicators are produced as monthly targets which can be monitored against actual performance.

Income Allocation
An income allocation table will enable you to spread forecast annual income across the 12 months on whatever basis you choose.  This will allow you to provide for monthly variations to revenue and to account for periods such as Christmas/New Year when there may be a significant variation to the monthly cycle.

Compare Actuals with Budget
Enter actual figures to the Monthly Forecast to compare with budget figures.

Calculations/Budgets & Projections/Budget – Quick

This is a non-expiring monthly budget for use by your clients in managing their personal finances.  The workbook has been protected but without a password.  We recommend that you review this budget, make any changes to accommodate your preferences, including adding your practice name and/or logo and then use a password to protect the budget worksheet before giving it to your clients.

The features of this budget include –

• A flowchart demonstrating best practice in managing personal finances;
• Inbuilt formulas for payment patterns will enable automatic allocation of income and expenses across the twelve months;
• Income or expenses with erratic frequency can be entered directly to the months involved;
• Separate tracking of bills and personal spending;
• Calculation of a weekly spend amount to help contain out of control spending;
• Projection of the balance of the “Bills” account to reveal any shortfalls in monthly funds;
• The actual balance of the “Bills” account for any month can be entered to make projections for future months more meaningful;
• The ability to add a buffer (based on a multiple of your monthly provision) to provide for any interruption to your income or to cover individual monthly shortfalls;
• A completed annual budget can be copied within the workbook for future reference and the current budget can then be updated with details for the new year; and
• When customising this workbook for your practice, you can change the colour theme (Page Layout/Themes) to match your logo or practice colours.  You will need to unprotect both worksheets to change the colour theme and it is important that both sheets are then protected again with a password.

Calculations/Budgets & Projections/Budget – Personal

Prepare figures for up to 5 separate businesses with consolidated figures to input to the business income schedule on a personal return.  Allows separation of non-commercial losses into deferred and non-deferred (and identification of primary producer and non primary producer amounts).

Designed for practices using tax return software which does not provide separate schedules for multiple businesses.

Calculations/Year

The most commonly used methods to calculate business value are –

1. Discounted Cash Flow, where a stream of future cash flow is discounted to today’s dollars to arrive at a value;
2. Capitalisation of Earnings, using future maintainable earnings and a required rate of return;
3. Rules of Thumb for micro businesses; and
4. Asset based valuations.

Accountants Desktop provides Excel workbooks using the first two methods, which are the most widely accepted, and we have provided guidance below on the suitability of each method to various situations.

It is important to understand the business and the key risks it faces before you attempt to value it.  Value is what a willing buyer will pay and what a willing seller will accept when dealing with each other in an orderly market at arms length and where both parties are under no compulsion to act and where the buyer and seller are aware of the relevant facts.

The following records will be needed to carry out a business valuation –

• Financial Reports for at least three, but preferably five years;
• A list of material assets and liabilities including contingents; and
• Forecasts for future year(s) if available.

Which method should you use?

Discounted Cash Flow

This is the most theoretically sound and widely accepted method to value a business.  It is more complicated than the Capitalisation of Earnings method and will generally suit larger businesses. The presence of one or more of the following factors may indicate that the Discounted Cash Flow method should be used to value the business –

• The business has a finite life (e.g. a quarry or a licence);
• The business is a high growth business;
• The business is moving from losses to profit or has declining profits; and
• Forecasts of cash can be made confidently.

The process involves a 10 year forecast of free cash flow.  Free cash flow is cash available to the business after expenses and reinvestment to support growth.  Earnings need to be “normalised” by excluding non-recurring income and expenses specific to the owner and adjusting for expenses not shown in the accounts at market value.  A terminal value is added to the cash flows to account for the long term value of the business beyond the forecast period.  Both the 10 year cash flows and terminal value are then discounted back to present value.  You can exclude a terminal value from your calculations.  See more information at the “Instructions” sheet.

Examples of normalising earnings include –

• Exclude income and expenses not specific to the business;
• Exclude owner-specific income and expenses;
• Exclude additional super for owners; and
• Ensure salaries and super for owners are market value.

Capitalisation of Earnings

This method of valuing a business is easy to understand and widely accepted but can undervalue high-growth businesses.  Our worksheet uses a simplified approach by using a required rate of return to capitalise future maintainable earnings.  Because the valuation method is based on a required return on investment (ROI), the value arrived at includes stock, property plant & equipment and goodwill.  Presence of one or more of the following factors may indicate that the Capitalisation of Earnings method should be used to value the business –

• Simpler method which will suit smaller businesses (SMEs);
• Uses simplified earnings capitalisation based on a required rate of return on investment;
• The business is stable;
• The business is currently profitable; and
• Forecasts of cash cannot be made confidently.

Warning: the capitalisation of earnings method can under-value high growth businesses.

The process involves an analysis of historical data of the business to arrive at a future maintainable earnings figure.  As with the Discounted Cash Flow method, earnings must be “normalised” by excluding non-recurring income and expenses specific to the owner and adjusting for expenses not shown in the accounts at market value.  Those earnings are then capitalised at the buyer’s required rate of return (ROI) to arrive at the business value.

Calculations/Business Valuations

The cash book workbook is available in two separate versions to suit the needs of your practice and the particular demands of the job.  Each version below produces a monthly record of cash receipts and payments and closing balances for reconciliation with the bank account as well as an annual summary of transactions for the preparation of the client’s income tax return.  These workbooks also produce a summary of GST collected and paid for the preparation of either monthly or quarterly Business Activity Statements.

Summary
Use where the client provides a summary of transactions for each month, such as their own unreconciled cash book, and no further detail is needed for your working papers.  Enter the client’s monthly totals to this workbook.

Standard
Use where the client provides records showing individual amounts for each transaction and you need to calculate totals for each category of receipt or payment each month and reconcile with the bank account.  Enter amounts to worksheets for each month to maintain a record of your calculations.  Additional information can be entered for capital acquisitions (asset description and date of acquisition).

Calculations/Year

This is a monthly cashflow forecast workbook to project receipts and payments from cash-based records or reports. Features include –

• Commence the forecast in any month based on user input;
• Automatic calculation of GST and payment to the ATO using cash accounting;
• Minimum cash balance warning;
• Input totals for actual receipts and payments each month to automatically update forecasts of closing cash balances in future months;
• Handy graphs illustrating Net Cash Flow and Cash Balance for each month; and
• Worksheet protection has been relaxed to allow the use of preferred colours, fonts and numerical formats.

Useful for Payment Arrangement requests where the ATO require a cashflow forecast.

Recently updated to include a comments box at the end of the forecast.

Calculations/Cashflow Forecast

Calculate the profit or loss and capital gain or loss on multiple disposals of cryptocurrency.  Enter unlimited purchases for each sale and select which parcels to sell based on the desired outcome.  Calculate the quantity and cost base of the remaining currencies where there was a partial disposal.

Calculations/Year

Calculate or estimate the capital gain/(loss) on disposal of a property, including adjustments for unlimited periods of private use and/or business use of main residence.  Calculate or estimate the capital gains tax payable.

Can be used to calculate actual figures for the client’s tax return or to provide a client with an estimate of the tax payable on the proposed sale of an asset subject to CGT.

Calculations/Year

Calculate the profit or loss and capital gain or loss on multiple disposals of shares.  Enter unlimited purchases for each sale and any return of capital to calculate the cost base and select which parcels to sell based on the desired outcome.  Calculate the quantity and cost base of the remaining shares where there was a partial disposal.

Calculations/Year

Determine eligibility for the CGT small business concessions and calculate the gain.  This Q & A style workbook includes the following –

• Answer questions to test basic eligibility as well as eligibility for each of the 4 concessions
• Calculate aggregated turnover
• Calculate net asset values
• Identify CGT concession stakeholders
• Show results for the significant individual and 90% tests
• Calculate the gain
• Use your own order for claiming each concession
• Handy tracing calculator

Use this calculator either before the event for tax planning purposes or after the event to complete the client’s income tax return.

Calculations/2021 and later

• Calculate the charge-out rate for a small business service provider.

  Calculations/Budgets & Projections/Charge-out Rate Calculator

Determine the company tax rate by analysing base rate entity passive income (BREPI) and aggregated turnover.  Trace passive income received as part of multiple distributions from partnerships or trusts.

Calculations/Year

Summarise the transactions on a credit card statement and reconcile with opening and closing balances.  Calculate GST and private use components.

To use this file for separate periods during the same financial year, set up the first period with descriptions, private use and GST codes and enter the transactions.  For subsequent periods in the same year, copy and paste the first file, change period from and to dates and delete figures entered at the worksheet.

Calculations/Year

This workbook will calculate the assessable or deductible balancing adjustment, and if applicable the capital gain/(loss), on disposal of an asset depreciated under the uniform capital allowance system.  It will also calculate the reductions to be made to the cost and termination value if the asset is a car and its cost exceeded the car limit and calculate the reductions to the balancing adjustment and capital gain/(loss) to reflect private use of the asset.  Notional calculations are also shown for use in client accounting if applicable.

You can use this workbook to calculate the required figures when preparing a client’s tax return should your software not perform these calculations.  You can also use the workbook before the financial year has ended to advise your client on the tax implications of disposing of the asset.

Calculate the opening written down value of an asset acquired in a previous year using the diminishing value, prime cost and pooling methods.  This workbook has been designed for use where an asset, which was acquired during a previous financial year and used for private purposes only, commences to be used for income producing purposes at some time during the current year.

Calculations/Year

Calculate unfranked and franked dividend amounts and imputation credits from the number of shares held, the dividend rate expressed as cents per share and the franking percentage.  Apportion these amounts based on ownership interest.

Calculations/Year

Prepare a dividend statement with automatic calculation of imputation credits and TFN tax where applicable.

Calculations/Year

Loan Calculator & Agreement

This calculator will help you manage the Division 7A obligations of a client who takes out an unsecured loan with a private company after 1 July 2006 under a written agreement for a maximum payment period of 7 years.  Within the one Excel file saved on your hard drive, you will be able to perform the following tasks for the life of the Division 7A loan –

1. Automatically produce a written agreement, prepared by our legal advisers, MCW Lawyers, from loan details entered;
2. Calculate the minimum yearly repayment each year. You will need to enter benchmark rates for future years into the worksheet;
3. Calculate the amount owing at the end of each year;
4. Calculate any deemed dividends where there is a minimum repayment shortfall;
5. Forgive any part of the debt over the life of the loan; and
6. Calculate the payout amount at any point during the loan.

Our calculator cannot be used for private companies who use a substituted accounting period.

Loan Register 

If you have a company with multiple loans in the same financial year and want a summary to reconcile with your general ledger, use this register to paste the relevant figures in the amortisation summary from each file and all loans will be displayed in an interactive dashboard.  This will allow you to slice and dice the data to display any combination(s) needed.

Distributable Surplus

The amount that can be treated as a dividend under Division 7A is limited to the company’s distributable surplus for its income year. There is a separate workbook below that will calculate the distributable surplus and it incorporates ATO instructions on what to include with the various components.

Important Information from Our Legal Advisers

• The constitution of the company and any shareholder agreement(s) should be reviewed to ensure the Division 7A agreement complies with those agreements;
• The agreement may be liable for stamp duty – check the relevant State Revenue authority;
• The agreement covers advances made before the date of the agreement noting that the term operates from the date of the advance. It also contemplates further advances being made; and
• The agreement does not accommodate guarantors which might be required when the borrower is a company, noting that a guarantee is not security sufficient to extend the term of the loan to 25 years but may otherwise be a prudent inclusion in the agreement.

Legislative and ATO Policy Changes

The methodology used to calculate interest, minimum yearly repayments, closing balances, deemed dividends and new loans arising from unpaid interest under Division 7A is correct at the time you download this file.  Given that our calculator is designed to be used for several years after download, users should be aware that any legislative or ATO policy changes in future years may affect the calculations.  Accountants Desktop will notify current members of any changes that will affect previously downloaded versions of this calculator.

This is a stand-alone version of the worksheet for calculating a car fringe benefit contained in our FBT Jobpaper file.

Calculations/Year/FBT – Cars

This is a stand-alone version of the worksheet for calculating an FBT instalment variation contained in our BAS Jobpaper file. Easily calculate a variation to the ATO FBT instalment amount for any period during the year.  All variations for the financial year are kept in the same sheet in adjacent columns for easy subsequent variations and there is a blank sheet for entering any data or pasting copies of supporting records.

Calculations/Year/FBT – Instalment Variation

Calculate the reportable fringe benefits for payment summaries.  Use where you know the taxable value of fringe benefits and have no need to use FBT – Full Calculator.

Calculations/Year

Shown as a separate menu item under Calculations are these Financial Calculations which include the following –

• Age calculator
• Apportion by date & area
• Apportion by value
• Blank worksheet (for simple maths)
• Days between two dates
• Future value
• GST worksheet (to calculate GST and net from GST inclusive figures)
• Interest – effective rate
• Interest – paid between dates
• Leave calculator
• Loan affordability
• Loan repayment & amortisation
• Present value
• Return on investment
• Return on investment – irregular
• Savings plan
• Spending analysis
• Time for future value

NB: These calculations use advanced Excel features and require Add Ins to be installed. If you receive an error message when using one of these worksheets, go to the About Calculations page and follow the instructions for installing add-ins.

Calculate fees and periodic payments for a client entering into a fixed price agreement with your practice.

This workbook provides for multiple jobs spread across multiple related client entities in the one agreement and includes automatic calculation of fees based on the prior year fee and a markup or enter your own fee.  Payments are calculated on either a weekly, fortnightly or monthly basis.

Calculations/Year

Calculate Australian dollar equivalents of overseas currency using inbuilt monthly, average annual or 30 June exchange rates from the ATO website.  The worksheet can be used as a profit & loss statement by using positive and negative figures for income and expenses.

Calculations/Year

Convert the foreign currency value of investments held by a self-managed superannuation fund at 30 June to AUD.

Calculations/Year

For a company tax return, convert excess non-refundable franking offsets to an equivalent tax loss.

Calculations/Year

Calculate figures for a franking credit refund application.

Calculations/Year

This is a stand-alone version of the worksheet for calculating a GST instalment variation contained in our BAS Jobpaper file. Easily calculate a variation to the ATO GST instalment amount for any period during the year.  All variations for the financial year are kept in the same sheet in adjacent columns for easy subsequent variations and there is a blank sheet for entering any data or pasting copies of supporting records.

Calculations/Year/GST – Instalment Variation

Calculate hire purchase interest deductible in the year of early repayment of a loan.

Calculations/Year

Calculate interest, borrowing expenses and end of year balances on a hire purchase contract.

Variations include payments which are –

• Weekly
• Fortnightly
• Monthly
• Monthly structured

Calculations/Year

For general ledger clients with multiple hire purchase borrowings, calculate the journal for consolidated figures for interest, current liability, non-current liability, current unexpired interest and non-current unexpired interest.  The summary also includes other details about each borrowing, including date acquired, financier, term in months, client general ledger account number and the practice general ledger account number.  All columns can be sorted and filtered for flexible data management.  If you have completed this summary for a prior year, copy and paste that data to this workbook and amend as necessary to speed up data entry.

Calculations/Year

Calculate deductible expenses when a client’s home is used as either a home office or place of business.

Calculations/Year

Enter a summary of monthly transactions to calculate interest and closing balances on the components of a single loan used for up to five separate purposes.  Reconcile closing balances with loan statements.  Interest and regular repayments are apportioned on the basis of the component loan’s proportionate use of capital each month.  Allocate lump sum repayments from the sale of an asset against the component loan used to acquire that asset.

Calculations/Year

Estimate fees for a job for quoting or fixed price agreement purposes.  This workbook comes pre-filled with a comprehensive list of activities and job categories which are fully customisable, allowing you to change existing, or enter your own, descriptions to suit the individual needs of your practice or the particular job.  Use this workbook to set up a practice template, including names and hourly rates of fee earners, which will automatically fill the budget when selected from a list. Enter actual hours and fees to compare with the budget and calculate variances.

Calculations/Year

This practice management resource will maintain a record of the status of jobs in your practice and the fee to be billed on completion (not value of work in progress).  One workbook can be maintained by each fee earner and then copied into a master register for practice reporting or one workbook can be used to monitor the entire practice.  The workbook also includes a weekly productivity budget, which extracts information from the job register and reports on budget fees YTD, actual fees YTD and variance.

Job statuses can be changed to suit the individual needs of each practice.  The default job statuses are –

• Awaiting records
• Records requested
• Records received
• Work commenced
• Queries to client
• Queries reminder
• Queries answered
• Awaiting review
• Work completed

All of the above columns, along with the additional columns which show Client Name, Partner, Manager, Job Year or Description, Estimated or Actual Fee, Date to be Invoiced, Lodgment Due Date and Comments can be sorted and filtered using standard Excel functions for comprehensive data analysis.  Worksheet protection has been relaxed to allow cell formatting, such as cell and font colouring, to enable easy identification of specific jobs.

As an example, an active Job Register might show the following information –

• 10 Jobs under Records Requested with estimated fees of $35,000
• 3 Jobs under Work Commenced with estimated fees of $15,200
• 1 Job under Queries to Client with estimated fees of $2,500
• 5 Jobs under Awaiting Review with estimated fees of $26,250
• 29 Jobs under Work Completed with actual fees of $121,900

To roll the previous workbook forward to 2016 and retain unfinished jobs, filter that workbook to show unfinished jobs only, and copy and paste into this new workbook.

Calculations/Year

This worksheet can be used to quickly locate and calculate changes made by your client to their previously supplied accounting data.  Typically, the client will provide a backup of data for the latest year and opening balances will be different to closing balances shown in the data file given to you in the prior year.  You can paste trial balance details such as account name, account number and account balance, extracted from your client’s accounting software, directly into this worksheet to speed up data entry.

Calculations/Year

Prepare a journal entry to update your client’s own accounting software balances with balances in the end of year financial report prepared by your practice.

Calculations/Year

Calculate tax profit on the forced disposal or death of livestock.  Show profit to be spread across 5 years under option 1 and alternatively allow entry of replacement livestock for automatic calculation of reduction to those costs to absorb the deferred profit under option 2.

Calculations/Year

There are two versions of this workbook which will summarise and reconcile transactions on a loan statement either on a monthly or annual basis.

Calculations/Year

Download an ATO not lodged activity statement report for your practice and import it into this workbook for an in-depth analysis of your BAS lodgement obligations.  The features include –

• Once downloaded, it will take you just a few minutes to import the ATO report into this workbook;
• The Dashboard has a summary of the number of outstanding activity statements on your client list by form type, client type and period end date;
• Activity statement statistics are shown in two groups, being those lodged by your practice and those lodged by your client.  You will need to identify this latter group in the data extracted from your practice management software.  Instructions on how to identify this group are incorporated with the workbook;
• The Dashboard also has a list of clients with more than one activity statement outstanding;
• An optional Manager Dashboard will allow you to import BAS managers from your practice software and analyse manager performance by period end date, client type and form type;
• The Manager Dashboard also displays a list of clients with more than one activity statement outstanding;
• Along with BAS managers, you can also optionally import each client’s email address and first name to enable emailing directly from the workbook;
• A further option allows you to import client mobile phone numbers with practice data to enable texting directly from the workbook.  You will need a service provider to use this feature with Microsoft Outlook; and
• You can choose any category of activity statement for email or texting by simply double clicking on any number in either dashboard.  A new sheet will be created with full details of the activity statements included with that number.  Send emails or text messages to these clients from the workbook.

This workbook will quickly put you in control of your ATO lodgement requirements.

Download an ATO lodgement status report for your practice and import it into this workbook for an in-depth analysis of your practice lodgement program.  The features include –

• Once downloaded, it will take you just a few minutes to import the ATO report into this workbook;
• The Dashboard has a summary of the number of current year returns on your client list by lodgement status, client type and due date;
• See the percentage of returns lodged by your practice to the date of the ATO report;
• View your 85% on-time lodgement targets for each of the due dates;
• The Dashboard also has a last year lodged analysis, revealing how far behind some clients are in their lodgement obligations;
• An optional Manager Dashboard will allow you to import client managers from your practice software and analyse client manager performance by lodgement status, client type and due date;
• The Manager Dashboard also displays weekly lodgement targets for each manager and due date as well as showing a last year lodged and return not necessary analysis by manager;
• Along with client managers, you can also optionally import each client’s email address and first name to enable emailing directly from the workbook;
• A further option allows you to import client mobile phone numbers with practice data to enable texting directly from the workbook.  You will need a service provider to use this feature with Microsoft Outlook; and
• You can choose any category of return for email or texting by simply double clicking on any number in either dashboard.  A new sheet will be created with full details of the returns included with that number.  Send emails or text messages to these clients from the workbook.

This workbook will quickly put you in control of your ATO lodgement requirements.

Calculate net medical expenses and the tax offset. Sort and/or filter all data entry columns.

Calculations/Year

Estimate motor vehicle fuel costs from odometer readings and consumption rate.

Calculations/Year

Estimate kilometres travelled and calculate the deduction for motor vehicle expenses using the rate per kilometre method.

Calculations/Year

Summarise business & private use of a vehicle from your client’s log book.

Calculations/Year

Calculate the assessable distributions for partnerships with partners’ salaries in accordance with TR 2005/7.  This worksheet provides for situations where the total partners’ salaries exceed the net income available for distribution.

Calculations/Year

Calculate PAYG instalment income for activity statements for partners or discretionary beneficiaries.

Calculations/Year

Estimate the annual or quarterly PAYG instalment amount or quarterly rate for a variation for an individual.  Retain a full record of your client’s PAYG instalment variations for a year in the one file.  Features include –

• Estimate gross income and deductions related thereto for each category of income.
• Account for reconciliation items on the return to convert profit to taxable income.
• Enter details of student debts (HELP, TSL, SFSS) for automatic calculation of repayments.
• Estimate income tax, medicare levy and medicare levy surcharge on taxable income and account for tax offsets and other credits.
• Automatic calculation of PAYG withholding on wages entered.
• A separate “Projections” worksheet enables estimates of income and expenses for any category of income.

Calculations/2018 and later

There are four versions of this workbook (weekly, fortnightly, monthly, quarterly) to calculate the amount of gross wages and PAYG withholding from variable net wages.

Calculations/Year

There are two versions of this workbook (monthly & quarterly) to calculate the amount of PAYG withholding from personal services income (PSI) as well as the amounts to be shown on Individual Non-Business and Personal Services Attributed Income Payment Summaries.

Calculations/Year

There are four versions of this workbook to estimate the following deductions for a PAYG Withholding variation –

• Depreciation
• Interest
• Motor Vehicle Expenses
• Rent

Calculations/Year

Calculate the relative amounts of personal services income for a period to test the 80% rule and calculate the wages that must be paid from that income.

Calculations/Year

For companies, partnerships & trusts, calculate the amount of attributed personal services income generated by up to five individuals and not promptly paid as wages.

Calculations/Year

Calculate deductions for prepayments in current or prior year(s) over their eligible service periods.

Calculations/Year

For those discretionary trust ledgers which have not separated the present entitlements of a private company beneficiary each year into sub-accounts, this workbook will track those entitlements and automatically allocate payments and offsets against the appropriate years.  It will also identify any UPEs which will be subject to the deemed dividend rules under Division 7A.  To speed up data entry, export data from the ledger into a spreadsheet and copy and paste into this workbook.

Calculations/Year

Prepare trading account worksheets for a primary producer, including separate worksheets for Livestock (with quantities), Produce and Other income.  A separate sheet summarises the figures for the client’s tax return.

The Livestock worksheet provides for entry of quantities and amounts for each type of closing stock valuation method per class of livestock as well as automatic calculation of average cost and prescribed cost for natural increase.

Calculations/Year

Analyse your client’s Trading/Profit & Loss Statement and explore the various options for improving their bottom line.  Enter changes (up or down) to the following performance indicators for clients with either trading or services income (or both) and view the effect on their profit –

• Average sale or fee amount
• Repeat business
• Turnover or productivity
• Sales prices or charge-out rates
• Cost of sales
• Direct costs
• Operating expenses

It will only take a few minutes to enter the summarised data from your client’s most recent financial report and you can then consider the various options in a meeting with your client.

Calculations/Budgets & Projections/Profit Manager

Analyse the financial and taxation outcomes arising from a proposed investment in a rental property over a ten year period. This workbook calculates the following –

• Pre-tax cash flow;
• After-tax cash flow to determine affordability;
• Gross and net rental yield;
• Projected investment equity after 10 years;
• Internal Rate of Return over the 10 year period if the property is not sold ;
• Internal Rate of Return over the 10 year period if the property is sold at the end of the 10th year, using the projected property value less selling costs and capital gains tax;
• Ownership types include Individual, Company and Self-Managed Superannuation Fund either in accumulation phase or in pension phase commencing during any of the 10 years covered by the projection;
• Calculations allow for one change to the current income of each owner over the ten year period and take into account any revenue and/or capital losses available at the beginning of the investment;
• Calculations allow for one refinance over the 10 year period;
• You can stress test the investment by making changes to any or all of the initial investment (deposit), interest rate and capital growth rate to check affordability in various scenarios and to observe the overall effect on the investment return; and
• Reports include Investment Analysis, Income Tax, Capital Gains Tax and Loan Schedule.

Note:  This calculation uses advanced Excel features and requires “Add Ins” to be installed. If you receive an error message when using this worksheet, go to the Calculations main page under Advanced Features and follow the instructions for installing add-ins.

Calculations/Budgets & Projections/Property Investment Analysis

Calculate rebate income to check against lodgement thresholds for clients who are eligible for the Senior Australians and Pensioners Tax Offset.

Calculations/Year

There are three calculators for rental income and deductions as follows –

Rental Income – Standard
Summarise rental income and expenses for up to 10 properties for the client’s return.  Use to summarise monthly statements from real estate agents and add any expenses paid directly by the client.

Rental Income – Private Use
Summarise rental income and expenses and calculate tax deductible amounts for a property with private use (e.g. Airbnb).  Enter the floor areas of the property and income producing area along with the period or total nights used for private and income producing purposes for automatic calculation of the deductible amounts.

Rental Income – Foreign
Summarise rental income and expenses in a foreign currency and convert to AUD.  Add your own exchange rates for any country not listed.

Calculations/Year

Calculate the deduction allowable for self-education expenses, including home office expenses and travel.

Calculations/Year

Calculate service fees payable by a professional firm to a related service entity using either Comparable Market Rates or ATO Indicative Rates contained in the ATO guide to service entity arrangements. The maximum service fee based on ATO recommendations for low audit risk arrangements is automatically calculated but users can determine and use a fee which they consider to be more appropriate to their client’s circumstances.

Calculations/Year

Calculate the bonus deductions claimable under the Small Business Technology Investment Boost (2023 only) and the Small Business Skills & Training Boost (2023 and 2024).

Calculate the minimum and maximum account-based pensions payable by a self-managed superannuation fund, including transition to retirement income streams, and compare with actual pension payments made during the year.

Calculations/Year

Stock Estimate

Enter trading details from one or more prior year financial reports to estimate closing stock from sales, opening stock and purchases by using one of the following cost of sales to turnover ratios –

1. The immediate prior year;
2. The average of all prior years entered;
3. Your own ratio;
4. The lower ATO business benchmark ratio; or
5. The higher ATO business benchmark  ratio.

The workbook comes preloaded with the latest ATO business benchmarks but they are not available for all business types.

Calculations/Year

This workbook calculates the amounts that the ATO will accept as estimates of the value of goods taken from trading stock for private use by taxpayers in named industries.

Calculations/Year

Use this workbook for your individual or company clients to estimate and provide for the following expected future liabilities –

• GST
• PAYG Withholding
• PAYG Instalments or Annual Income Tax
• Superannuation Guarantee
• Fringe Benefits Tax Instalments

For income tax calculations, separate worksheets are included for each quarter to allow entry of estimates or revised estimates of annual income and expenses, reconciliation of that income or profit with taxable income and automatic calculation of the client’s annual income tax liability and average rate of tax.  Calculations in worksheets for quarters 2, 3 & 4 adjust the amount of tax to be set aside to account for over or under provisions of income tax in earlier quarter(s).

If you are maintaining a separate bank account for these future liabilities, the workbook allows entry of deposits to and withdrawals from that account to reconcile the running balance with bank statements.

This is an essential resource for your small business clients or investors who are struggling to pay their tax and superannuation debts.  Use this workbook as a consulting tool in your practice (or even for your practice) or give it to your client after calculating and entering a PAYG instalment rate for each quarter.

Calculations/Year

For any entity type –

• Project annual profit from part-year accounts
• Adjust profit for tax reconciliation items and calculate taxable/net income
• Deduct tax planning measures
• Add names and other income of all related parties
• Calculate marginal tax rates (MTR) and income available at that rate for both the client and related parties
• Allocate taxable/net income and/or make deductible payments to related parties tax effectively using MTRs and income available at that rate

Calculations/year

Reconcile provision for tax in accounts and franking account in tax return. Maintain a running balance of franking credits.

Calculations/Year

Two versions of this workbook (company and non-company) will reconcile accounting profit with taxable income.

Calculations/Year

Record details of taxable payments to payees and produce a summary of those payments for reporting to the ATO.  Can be completed by your practice from source records or send the workbook to your client for completion.

Calculations/Year

Calculate the “reasonable amount” for accommodation, food and drink and incidental expenses for domestic travel by employees who have received an allowance and are required to sleep away from home.

Calculations/Year

Calculate the “reasonable amount” for meals and incidental expenses for overseas travel by employees who have received an allowance.

Calculations/Year

Calculate the “reasonable amount” for food and drink expenses for employee truck drivers who have received an allowance and are required to sleep away from home.

Calculations/Year

For either non-streamed or streamed discretionary trust income, estimate trust accounting distributions required for the trustee resolution by 30 June to achieve a desired taxable distribution for each beneficiary and prepare the resolution itself which forms part of the workbook.

Calculations/Year

For the statement of distribution on the tax return of a discretionary trust, calculate the assessable distributions and related credits from both streamed and non-streamed distributions of trust income in the accounts.  The workbook includes ATO instructions and identification of the various labels at which the distributions should be declared on the trust’s return.

Calculations/Year

Reconcile amounts on payment summaries with totals shown in activity statements and the general ledger for the year. Separately identify amounts paid to associated persons for the tax return.  Test actual superannuation payments against the employer’s Superannuation Guarantee obligations for the year.

Calculations/Year