About Each Workbook

The link to each annual calculation (2016, 2015, etc) is contained in a table for that year along with a brief description of the workbook.  Links to other specialised calculations (e.g. ATO Benchmarks, Budgets, Business Valuations, Division 7A Loans, etc), which have their own individual menu listing, are found within their own dedicated page which contains a full description of the workbook and suggestions for its use.

The following detailed description and suggestion for use of each workbook applies to annual calculations only.

Accounts Reconciliation

Use this worksheet to reconcile the profit/(loss) shown in your client’s accounting system (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.

Adjusted Taxable Income

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
Asset Acquisition

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.

Asset Acquisition

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.

Audit Worksheet

Using ATO methodology for audits of personal returns, calculate funds available and funds used to self-assess your client’s audit risk.  The workbook comes with a blank worksheet for supporting calculations and a client questionnaire to assist you in gathering information.

Bank Reconciliation

Simple bank reconciliation.

BAS for June

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.

BAS Reconciliation

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.

BAS Summary & Recon

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.

BAS Workpapers

BAS working papers are available in three separate 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.

BAS Workpapers – Taxi Drivers

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.

Blank Worksheet

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.

Borrowing Expenses

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

Business Income Schedule

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.

Cashbook

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).

CGT – Property

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

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

CGT – Shares

Calculate the capital gain/(loss) on disposal of identical shares acquired in multiple parcels.  Automatic allocation of individual shares between the indexation and discount methods where available to minimise the gain and take maximum advantage of prior or current year capital losses.   Calculate the quantity and cost base of the remaining shares, where there was not a complete disposal.

Credit Card

Two separate workbooks to summarise the transactions on credit card statements for the month or year and reconcile with opening and closing balances.

Depreciation – OWDV

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.

Dividend Calculation

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.

Dividend Statement

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

FBT – Full Calculator

Whether you need to prepare an FBT Return or calculate an employee’s contribution required to reduce the value of a benefit to zero, this workbook will calculate the taxable value of the full range of fringe benefits. It also includes a separate summary for the FBT return for all employer types and a summary of benefits by employee, including calculation of reportable fringe benefits to be shown on payment summaries. Worksheets include –

  • Cars (incorporating base value calculations)
  • Loans
  • Debt Waiver
  • Expense Payments
  • Housing
  • Living Away from Home Allowances
  • Board
  • Property
  • Residual
  • Car Parking
  • Meal Entertainment
FBT – Reportable Benefits

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.

Fixed Price Agreement

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.

Foreign Currency

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.

Foreign Currency – SMSF

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

Franking Credit Refund

Calculate figures for a franking credit refund application.

GST Worksheet

Add a list of GST-inclusive figures to arrive at the GST-inclusive total, the GST component & the GST-exclusive total. Deduct any private use component.

Hire Purchase Fortnightly

Calculate interest, borrowing expenses and end of year balances on a standard hire purchase contract with regular fortnightly payments. 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 About Calculations page and under Advanced Features, follow the instructions for installing add-ins.

Hire Purchase Payout

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

Hire Purchase Monthly Standard

Calculate interest, borrowing expenses and end of year balances on a standard hire purchase contract with regular monthly payments. 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 About Calculations page and under Advanced Features, follow the instructions for installing add-ins.

Hire Purchase Monthly Structured

Calculate interest, borrowing expenses and end of year balances on a structured hire purchase contract with up to five different repayment amounts during the term. 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 About Calculations page and under Advanced Features, follow the instructions for installing add-ins.

Hire Purchase Summary

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.

Home Office

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

Interest – Mixed Loans

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.

Job Budget

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.

Job Register

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.

Journal for Changes

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.

Journal for Client

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.

Loan

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

Medical Expenses

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

Motor Vehicle Fuel Estimate

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

Motor Vehicle Kilometres Estimate

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

Motor Vehicle Log Book Summary

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

Partners’ Salaries

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.

PAYG Instalment Income

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

PAYGW – Pay Estimate

There are three versions of this workbook (weekly, monthly, quarterly) to calculate the amount of gross wages and PAYG withholding from variable net wages paid to business owners and their associates at random.

PAYGW – PSI

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.

PAYGW Variation

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

  • Depreciation
  • Interest
  • Motor Vehicle Expenses
  • Rent
Personal Services Income – 80%

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.

Personal Services Income – Attribution

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

Present Entitlement Register

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.

Primary Producer Trading

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.

Profit & Tax Projection – Company

For companies, project annual profit from part-year accounts (practice software or client accounting systems such as Xero, MYOB, QuickBooks, etc), estimate taxable income and tax payable by the company and calculate tax savings using tax planning measures.

Profit & Tax Projection – Other

For sole traders, partnerships and trusts, project annual profit from part-year accounts (practice software or client accounting systems such as Xero, MYOB, QuickBooks, etc), estimate taxable income and tax payable by the individual owner, partners or beneficiaries and calculate tax savings using tax planning measures.

Rebate Income

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

Rental Income Summary

Summarise rental income and expenses for the client’s income tax return.  Use to summarise monthly statements from real estate agents and add any expenses paid directly by the client.

Self-Education Expenses

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

Service Fees

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.

SMSF Pension Payments

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.

Stock Estimate

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.

Tax & Super Debt Management – Individual or Company

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.

Tax Provision & Franking Account

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

Tax Reconciliation

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

Taxable Payments Reporting – Building

Record details of taxable payments to payees in the Building & Construction Industry 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.

Travel – Domestic

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.

Travel – Overseas

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

Truck Drivers

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.

Trust Distributions – Resolution

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.

Trust Distributions – Return

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.

Wages & Super Reconciliation

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.

 

Copyright 2017 Accountants Desktop, Designed and Developed by Web Bird Digital