Links 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. Budgets, Business Valuations, Division 7A Loans, etc) have their own individual menu listing and dedicated page which contains a full description of the workbook and suggestions for its use.
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.
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
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.
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.
Simple bank reconciliation.
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.
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.
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 working papers are available in four 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.
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.
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).
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.
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).
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.
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;
- Motor Vehicle Expenses/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.
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.
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.
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 –
For an existing business, enter current and forecast data to produce an annual budget with monthly analysis by activity levels.
For a new or existing business, enter the Desired Net Profit and the budget will be calculated from all other parameters.
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.
All budgets will display the break-even position in dollars and quantities by week, month or year, based on forecast figures.
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.
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.
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
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.
The most commonly used methods to calculate business value are –
- Discounted Cash Flow, where a stream of future cash flow is discounted to today’s dollars to arrive at a value;
- Capitalisation of Earnings, using future maintainable earnings and a required rate of return;
- Rules of Thumb for micro businesses; and
- 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.
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.
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.
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).
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.
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.
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.
Calculate the charge-out rate for a small business service provider.
Calculations/Budgets & Projections/Charge-out Rate Calculator
Two separate workbooks to summarise the transactions on credit card statements for the month or year and reconcile with opening and closing balances.
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.
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.
Prepare a dividend statement with automatic calculation of imputation credits and TFN tax where applicable.
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 –
- Automatically produce a written agreement, prepared by our legal advisers, MCW Lawyers, from loan details entered;
- Calculate the minimum yearly repayment each year. You will need to enter benchmark rates for future years into the worksheet;
- Calculate the amount owing at the end of each year;
- Calculate any deemed dividends where there is a minimum repayment shortfall;
- Forgive any part of the debt over the life of the loan; and
- Calculate the payout amount at any point during the loan.
Our calculator cannot be used for private companies who use a substituted accounting period.
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 calculator makes use of macros and advanced Excel features. Accordingly, you will not be able to use the calculator if your settings for Macros and Excel Add-ins are not appropriate. Refer to the About Calculations page under “Advanced Features” for information on how to change these settings.
Calculations/Division 7A Loans
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)
- Debt Waiver
- Expense Payments
- Living Away from Home Allowances
- Car Parking
- Meal Entertainment
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.
Shown as a separate menu item under Calculations are these Financial Calculations which include the following –
- Age Calculator
- Blank Worksheet (for simple maths)
- Date Apportionment (to calculate business use on a time basis)
- Days Between Two Dates
- Future Value
- GST Calculator (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
- 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.
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.
Convert the foreign currency value of investments held by a self-managed superannuation fund at 30 June to AUD.
Calculate figures for a franking credit refund application.
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.
Calculate hire purchase interest deductible in the year of early repayment of a loan.
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.
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.
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.
Calculate deductible expenses when a client’s home is used as either a home office or place of business.
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.
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.
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.
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.
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.
There are two versions of this workbook which will summarise and reconcile transactions on a loan statement either on a monthly or annual basis.
Calculate net medical expenses and the tax offset. Sort and/or filter all data entry columns.
Estimate motor vehicle fuel costs from odometer readings and consumption rate.
Estimate kilometres travelled and calculate the deduction for motor vehicle expenses using the rate per kilometre method.
Summarise business & private use of a vehicle from your client’s log book.
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.
Calculate PAYG instalment income for activity statements for partners or discretionary beneficiaries.
Estimate the quarterly PAYG instalment amount or rate for a variation for an individual. Prepare up to 4 variations for a client and retain a full record of their PAYG instalment variations 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.
- Separate “Projections” worksheet to allow estimates of income and expenses for any category of income.
Calculations/2018 and later
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.
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.
There are four versions of this workbook to estimate the following deductions for a PAYG Withholding variation –
- Motor Vehicle Expenses
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.
For companies, partnerships & trusts, calculate the amount of attributed personal services income generated by up to five individuals and not promptly paid as wages.
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.
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.
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;
- Our Solver Macro will automatically calculate ownership proportions for co-owners, taking into account any current income changes during the ten year period and revenue and/or capital losses available at the beginning of the investment, to maximise the overall return; (see important information below)
- 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.
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.
Use this workbook to help your clients plan for their retirement by projecting growth in their investments and superannuation balances to determine whether the accumulated funds will provide them with a comfortable retirement.
These retirement projections are based on estimates and assumptions about the future. They should be used as a guide only to planning for your client’s retirement. The longer the projection, the less reliable will be the outcome. We recommend that you update your client’s projections each year to take into account any change in their personal circumstances as well as changes to tax and age pension rates and rules.
The figures in these projections are in “today’s” dollars to make the information more meaningful. Therefore it is important to use an inflation adjusted figure when entering rates of return on superannuation and growth on investments as well as wages growth rates, if any. For example, if you expect wages to grow at 3% and inflation to average 2%, you should enter 1% as the wages growth rate. Warning: Failing to account for inflation in your rates of return or wages growth will significantly overstate the relevant outcomes.
This workbook has the following features –
- Enable projections for a single person or each member of a couple;
- Allow a change in earnings during the projection period to account for scaling back in the years prior to retirement;
- Allow a change in living costs on retirement;
- Calculate personal income tax payable and tax payable on superannuation earnings and contributions;
- Calculate age pension entitlements under both the income and assets tests;
- Calculate minimum pensions receivable with automatic top-ups from the fund to cover any shortfalls in living expenses;
- Calculate after tax income in the first year of retirement;
- Calculate accumulated investments and superannuation balances on retirement;
- Calculate age when all investments and superannuation balances have been exhausted;
- Separate worksheets show full details of all calculations;
- Allow testing of an alternative scenario by changing age to retire, age to commence superannuation pension, wages growth rate, earnings rate on super investments, net yield and growth rate on non-super investments, regular concessional and non-concessional superannuation contributions, lump sum contributions to super and lump sums received and paid outside superannuation; and
- A handy worksheet enables calculation of yields and growth rates on super and non-super investments.
Calculations/Budgets & Projections/Retirement Planner
Calculate the deduction allowable for self-education expenses, including home office expenses and travel.
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.
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.
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 –
- The immediate prior year;
- The average of all prior years entered;
- Your own ratio;
- The lower ATO business benchmark ratio; or
- 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.
Use this workbook for your individual or company clients to estimate and provide for the following expected future liabilities –
- 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.
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.
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.
Reconcile provision for tax in accounts and franking account in tax return. Maintain a running balance of franking credits.
Two versions of this workbook (company and non-company) will reconcile accounting profit with taxable income.
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.
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.
Calculate the “reasonable amount” for meals and incidental expenses for overseas travel by employees who have received an allowance.
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.
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.
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.
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.