Pivot tables or SUMIFS in reporting

/in /by

Like many people I both love and hate pivot tables with a passion. They are so useful in analysing data on a one off basis, but when the requirement is to get a series of complex reports out every month with basically the same structure from one set of data, a number of pivot tables that each require updating for every change is both slow and if each one is not updated, can result in incorrect information.

I was recently working with an NGO in Cambodia. They had six projects in different parts of the country and each project had five different activities. These were set up in Xero as separate tracking items. There was a requirement to provide each project with the ability to analyse the spend for each activity, within their project month and year to date, actual against budget.

The data is easily downloaded from Xero. The reports can be produced in Xero but this would not give the managers the ability to get to the individual transactions and is very slow.

I saw two basic options. Firstly a series of pivot tables that would need updating each time data was changed. These would give the user the ability to look into the detailed transactions, but it would be difficult to make the pivot tables look good in a report.

The second option was to produce a single report using the SUMIFS on a standard template. These reports looked good and were easy to manipulate, but suffered from two problems. Firstly it was not easy for the user to interrogate the database to see the individual transaction and secondly if a new account code was entered and the template was not changed, the report would be incorrect.

I used the SUMIIFS to produce the reports and checked the results against a pivot table. The word ERROR appeared if the two did not agree and this could be checked prior to sending out the file to the individual users.

I also provided a separate tab with a pivot table that could be used to analyse the results.

This resulted in a easy to set up, good looking report where the project and activities could be changed with the report updating automatically, as well as providing the benefits of pivot tables to the user.

Business Planning

/in /by

Well the 2018 financial year has begun. At this point you should know your profit for 2017, and also have an idea of the profit that you will earn in 2018. If you do not have these figures in mind, then you need to work it out.

We cannot emphasise enough how important it is to know where you are and where you are going with your business. Even if you feel that you are snowed under by the pressure of running your business, you must make time to sit back and look to your future.

You cannot allow your business to control your life, you must control your business so that you can have a life!

Plan your work, plan your people, plan your holidays and plan your money. There are no easy rides in business these days. Your business will not be efficient and successful unless you do you planning, follow that plan and be prepared to adapt to changes quickly.

You do not have to re-invent the wheel to do a business plan, there are plenty of websites that will give you ideas and guidance, try these for starters: –

https://www.business.gov.au/info/plan-and-start/develop-your-business-plans/writing-a-business-plan/why-do-i-need-a-business-plan

https://www.business.gov.au/Info/Plan-and-Start/Templates-and-tools/Business-Plan-Template-and-Guide

Look through our people profiles, are there people there who might be of help, if so call us now, but otherwise find someone who can help you to focus on development of your business.

Get a business mentor, even if they are just a sounding board for your ideas. Just sharing these ideas can focus your attitude and attention towards making 2018 a more efficient and thus more profitable year. No-one knows more about your business than you do, but an independent person may well bring in ideas that are useful to you in going forward. Choose a friend or family member to listen. Do not worry that they are not business minded, you will be surprised at how just sounding out and sharing your ideas can help you to develop them.

If you need specific business development advice, then make the investment and call in a professional. Your business deserves it!

 

Tax – Year End Savers

/in /by

With 30 June looming, this article offers some contemporary tax planning tips to optimize your 2015/2016 tax position:

Flu Shots!

As we move into Winter and flu season, have you considered paying for your employees to have flu vaccinations? As well as decreasing workforce absences (which in the case of the flu can be anything up to a week per employee which can dramatically impact overall productivity), flu vaccinations receive generous tax treatment as follows:

  •  Fringe Benefits Tax – If an employer provides an employee with a flu vaccination by paying for the cost of the vaccination this is an exempt benefit which will not attract FBT. Although not every employee may take up the offer of free flu vaccinations, it must be offered to all employees in order to be exempt from FBT.
  • Income Tax – Flu vaccinations, although generally not deductible when paid for by employees, are deductible when paid for by employers on behalf of their workers. As we near flu season and financial year-end, by offering flu vaccinations and having them done and paid for/invoiced by 30 June, you can optimise your 2015/2016 tax position – as well as providing a benefit that is typically much appreciated by employees.

Small Business Instant Asset Write-Off

The Small Business Instant Asset Write-Off is a great way for your business to reduce its 2015/2016 tax payable as we near 30 June. SBEs (businesses with a turnover of less than $2 million, including the turnover of connected and affiliated entities) can claim an immediate deduction (total write-off) for depreciating assets that cost less than $20 000 provided the asset is first acquired on or after 7.30pm EST on 12 May 2015, and first used or installed ready for use on or before 30 June 2017.

To get the benefit for this 2015/2016 financial year, the asset will need to be purchased and installed ready for use on or before 30 June 2016. Basically, all depreciable assets (including second-hand assets) used in a business are eligible for the $20 000 write-off – including motor vehicles, furniture, computer equipment, machinery etc. The Small Business Instant Asset Write-Off is a great way for your business to reduce its 2015/2016 tax payable as we near 30 June.

Super Contributions

Although not due until 28 July, some employers bring forward their compulsory April-June Superannuation Guarantee payments to before 1 July. By doing so, they improve their year-end tax position by claiming a deduction in 2015/2016. To claim a deduction in 2015/2016, the contribution must be received by the superannuation fund before 1 July.

However, if you use the ATO’s Small Business Superannuation Clearing House, the Superannuation Guarantee payment for deduction purposes is taken to be made when received by the Clearing House. Payments other than Superannuation Guarantee contributions such as ‘salary sacrifice’ payments are not considered to have been made until the date the superannuation fund receives the payment, even if you use the Small Business Superannuation Clearing House. In order for these payments to reach the superannuation fund by 30 June 2016, they must be received by the Small Business Superannuation Clearing House no later than 6:00pm (AEST) Thursday 23 June 2016.

Tax – FBT Checklist

/in /by

31 March signals the end of the Fringe Benefits Tax (FBT) year. One of the major causes of FBT non-compliance is the failure of employers to identity benefits that have been provided throughout the year to employees and their associates (e.g. spouses).

In identifying taxable or exempt benefits that have been provided, the best place to start is by reviewing your accounts and ledgers as well as employee staff packages and staff policies. Having done this, proceed to use the following non-exhaustive checklist to further ascertain whether benefits have been
provided in which case an FBT liability may arise:

Motor Vehicle Expenses

  • Have you reimbursed an employee’s motor vehicle expenses during the year?
  • Have you made available a company car to an employee? Note that even where the vehicle is a utility or other ‘work-horse’ vehicle, a residual fringe benefit may arise

Loans

  • Have you provided a loan to an employee during the year or released them from a debt owing to you?

Housing

  • Have you provided an employee with a right to use accommodation which they treat as their usual home?

Living Away From Home Allowance

  • Have you paid an allowance to an employee to compensate them for the cost of expenses incurred because they are required to live away from their usual home for work purposes?

Travel and Entertainment

  • Have you paid or reimbursed an employee for their travel expenses?
  • Did you pay for or reimburse a taxi fare for an employee?
  • Did you provide or reimburse an employee for the cost of meal entertainment or accommodation or travel in respect of that entertainment?
  • Did you provide recreation (e.g. tickets to sporting events) or accommodation or travel in respect of that recreation, or hire or lease entertainment facilities for an employee’s use?

Expense Payments

  • Did you pay for or reimburse an employee for a private expense of theirs (e.g. school fees, home mortgage or rental expenses, health insurance premiums, gym membership etc.)?

Property

  • Did you provide an employee with property (either in-house or external) e.g. goods, services etc.

 

TAKE HOME MESSAGE

If you answered yes to any of the above questions, a fringe benefit may have been provided. FBT exemptions and reductions in value may apply in some circumstances but often rely on your record keeping. You may wish to discuss the implications of this with your accountant as we now near the end of the FBT year.