You may be asking WHY it has taken us so long to create this training course COMBINATION package considering that Xero and Excel go hand in hand for all things financial and accounting?!
Xero is fantastic for keeping track of all of your financial transactions for a business and Microsoft Excel is still the preferred choice by most bookkeepers and accountants for producing reports and working with the data that comes out of Xero.
The versatility of Excel as a spreadsheet means that you can do so much more with it than just make sense of the financial information from your Accounting software. One case study in our Microsoft Excel Courses comes from our own experience about scheduling courses, trainers and training rooms for enrolments when we had a training centre in Dee Why NSW.
Are you only using 10% of Microsoft Office? Are you taking 2 hours to do something that can actually be done in 30 minutes? Microsoft Office software had a feature that could identify your productivity score in reports to managers.
The technology was designed as a sales tool for Microsoft to help identify software that could be utilised better at Microsoft corporate clients but it unveiled a whole privacy issue.
It seems that we Aussies always get on the retail marketing bandwagon so here we go! For a limited time we’re making our full library of Microsoft Excel training course videos available for $1
To be fair, this gives us an opportunity to explore different ways of making our training available (digital marketing) and enables us to use some fantastic new technology.
A lot of the time, however, people enrol in an Excel online training course or Xero online training course because they need to refresh a specific set of skills for their job, which means they don’t have the time to focus on other areas that don’t have an immediate relevance for their work.
Since then, however, a number of cloud-based accounting applications have entered the market — QuickBooks Online (now distributed by their US-based parent company Intuit), Reckon One, Saasu, Zoho, and so on.
Even though Xero was hailed as a breath of fresh air when it first entered the market, it is still a little more complicated to use when compared with other cloud accounting apps, like QuickBooks and Reckon One. For instance, the purchase orders feature is still hidden behind bills, when it could easily be access via a dropdown menu. But it’s not a major quibble.
Xero’s contact profile misses a beat
Although Xero allows you to assign customer numbers for your suppliers or customers in the contact profile, it doesn’t have the functionality to record customer numbers assigned by supplier or customer.
In MYOB, you must upload your bills to your in-tray, and then link them to your transactions. This is annoying, because it requires you to leave the transaction window and open the in-tray one.
***
Remember that when you select a cloud accounting package for your business, do not choose solely based on price. Make a list of the needs of your business and the functions you’ll require, and then select the accounting package that suits your needs the most. Ask your bookkeeper and other business friends for their recommendations as well.
Announcement: Microsoft Excel 2016 Beginners’ to Advanced courses now available!
At EzyLearn we include all versions of a software program in our training courses. That means when you enrol into Excel you get 2016 — AND — the older Excel courses which include version 2013, 2010, 2007 and even 2003 (if you really need it).
When you enrol in Lifetime Course Accessyou get access to ALL versions and ALL FUTURE VERSIONS as we continually update them — at no extra cost!
Our courses cover how to import from Excel into Xero and visa versa, for purposes such as working out depreciation on a business premises.
THERE ARE MANY UPSIDES to buying a commercial property for your business and if you’re able to buy an industrial unit like a Cubbyhole, it can also be a lot cheaper than renting premises.
Buying outright
If you’re in the financial position to buy your business premises outright, it may seem like a no-brainer to do this instead of getting a mortgage. However, there are some things you need to consider:
You’ll lose liquidity on the assets in your property, which means you won’t be able to tap into any equity in the property, unless you take out an equity loan against the property.
You’re tying all your cash to one asset class, which may limit your ability to make other investments and prevent your business from expanding. This could run counter to your reasons for making the property purchase in the first place.
You’re spreading the payments over many years, which ties you to paying down that asset for the foreseeable future.
You’re paying interest, which although it’s a tax deduction, will significantly inflate the price of the property.
Work out the best way in Excel
Using the data from your Xero accounting software package, Microsoft Excel can help you determine whether your business will be financially better off buying its premises outright or getting a mortgage.
The sales spiels of many of the notable online accounting software packages like QuickBooks, Wave Accounting, Outright, Kashoo, LessAccounting, Clearbooks and even Xero, claim that this feature will save you time and effort as it imports your bank transactions. The truth is, this is not foolproof and won’t work 100 percent of the time (even if it’s just a matter of not being able to get your software and your bank to “connect” just as your mobile phone connection inexplicably doesn’t work sometimes).
Therefore, always double check your bank transaction data has been imported accurately. This said, importing your bank statement into Xero (or whatever accounting software you use) is a really important step in the bookkeeping process that a lot of business owners forget or don’t know how to do. And the technology is only going to get better!
Using the correct format
To import your bank statement into Xero, you must ensure it’s in the correct format. Xero can only work with a CSV file of your bank statement. Depending on your bank, you might be able to download your bank statement as a CSV file from your internet banking, or you will have to create one from scratch.
Creating one from scratch isn’t too difficult. If your bank doesn’t give you the option of downloading a bank statement as a CSV file, you can create one yourself in Microsoft Excel.
You can download an Excel template from Xero. It includes the recommended fields and is already set up as a CSV file, so all you need to do is add in your data.
Set transaction rules
Once you’ve created and uploaded your bank statement to Xero, you’ll need to set up transaction rules for recurring expenses. You’ll learn how to do this in our Cash Flow Reporting, Budgets and ROI Xero Course.
Setting rules for recurring transactions helps speed up the reconciliation process, which depending on the type of business you operate and how often you reconcile your account, can be the most time-consuming part of the process.
Importing your bank statement and creating rules for transactions that occur each week, month fortnight, year, etc, greatly speeds up this process.
No CSV? Use bank feeds
If your business has lots of expenses every week, and your bank doesn’t let you download your bank statement in a CSV format, you may find that manually creating one in Excel each month is too time consuming.
Set up bank feeds instead. Bank feeds is the process of linking all of your business accounts, whether they’re credit cards or bank accounts, to your accounting software, so that each time you make an electronic purchase, it’s automatically imported into your accounting software.
This will allow you to reconcile your account each fortnight, week or more frequently, if you desire, than once a month when your bank statement comes in.
Learn Microsoft Excel from scratch or brush up your Excel skills, at your own pace, with our affordable Excel online training courses — where you get THE LOT (that’s 9 courses in total) for ONE LOW PRICE — everything included! Volume corporate discounts are available and our courses count towards CPD Points. NOW is the time to learn to use Excel, one of the most-used software applications in the world.
Excel isn’t just for budding bookkeepers; it’s a great tool for all business owners to know.
MICROSOFT EXCEL IS THE most widely used spreadsheet application in modern computing. That said, it’s also one of the more difficult programs of the Microsoft Office Suite to learn, which is why we recently updated the content of our Excel training courses.
A lot of people do our Excel training courses to help them “skill up” to find a job, find a position better suited to them, or develop their career path. However, Excel is a fantastic tool for small business owners as well.
But whether you use Excel to create a pivot table or a database, there are a few things you should do each time you open an Excel document. Here we present you with three:
1. Vertical align: always centre
Always align the text in the cells of your Excel spreadsheet to the centre, or the top in certain circumstances. But never, ever align it to the bottom. It’s hard on the eyes and, when you’re looking at lots and lots of data in lots and lots of cells, it becomes difficult to know which row, column, etc, you’re looking in. Centre alignment, always.
2. Build error-checking into formulas
There should never be an instance where one of your workbooks is showing a #DIV/0, #N/A, #REF, #NAME?, #NUM!, or #NULL! error. This is especially true if you’re sharing these workbooks with your business partners or accountant or whomever.
Seeing an error in a financial report may cause the reader to doubt the accuracy of the entire workbook, so ensure your workbooks remain error free by using the simple IFERROR() error-checking function in Excel.
3. Print preview your work
Again, if you intend to share workbooks with other people, you should always ensure that your Excel workbooks can be printed nicely and easily, even if you don’t intend to ever print the document yourself. This is easy enough to do via File > Print Preview and adjusting the print margins before sharing (or printing) the document.
However, judging by the number of times I’ve printed an Excel document only to collect 87 sheets of paper off my printer to read the contents one 4×4 table, the function is seldom used by anyone else but me!
***
For more Excel formatting tips and tricks, download our FREE Beginners’ Guide to Excel, or enrol in our intermediate or advanced online Excel training courses to learn how to create databases, pivot tables, charts, graphs, and much more…
Cash flow is THE key indicator of your business’ health and happiness so it pays to know the tools that will help you run regular cash flow reports.
AS WE’VE WRITTEN NUMEROUS times before, cash flow is the best indicator of financial health. A cash flow report takes into account the money you have in the bank after you’ve paid all your suppliers, employees, made your loan repayments.
But supposing, for whatever reason, you don’t want to use an Excel database as your pivot table’s data source? Well, there are some other options to create a pivot table without manually entering the information into Excel first. Here are a few more data sources that you can use to create a pivot table in Excel.
Office data connection files
The office data connection (ODC) file extension was created by Microsoft and contains properties to connect to and retrieve data from an external data source. It contains a connection string, data queries, authentication information and other settings. Microsoft recommends that you retrieve external data for your pivot tables and reports using ODC files.
External relational databases
If, for instance, you’re using another relational database program, like Microsoft Access or Filemaker Pro, you can also import data directly from these programs into your pivot table, rather than manually entering the data into an Excel worksheet. In the case of connecting data from an MS Access database, you can do this quite simply by selecting Access from the ‘data source’ dialog box. For all other external databases, you would select the ‘from other sources’ dialog box and follow the steps in the data connection wizard.
Using another pivot table
Each time that you create a new pivot table, Excel stores a copy of the data for the report in memory, and saves this storage area as part of the workbook file. To use one pivot table as the source for another, both must be in the same workbook. If the source pivot table is in a different workbook, copy the source to the workbook location where you want the new one to appear. Keep in mind that when you refresh the data in the new pivot table, Excel also updates the data in the source pivot table, and vice versa. When you group or un-group items, or create calculated fields or calculated items in one, both are affected.
Create a database in Excel first
The easiest and most efficient way to create a pivot table is to create a database in Excel first. Here, you can update and manage as much information about your business — including customer data and financial data — and then use that as a data source for a pivot table.
***
Creating databases and pivot tables are part of our advanced Microsoft Excel training course, but you can start your Excel journey with our FREE beginners’ Excel course. Read more about our beginners, intermediate and advanced Excel training courses on our website, or enrol to start learning by 5pm tomorrow!
No amount of data is too big for Excel’s pivot tables
Go You Excel Pivot Table! Excel’s signature function, the pivot table, is still as useful for making sense of large amounts of data as it ever was.
WE’VE RECENTLY BEEN UPDATING the content for our Excel training courses and were reminded of just how useful Excel is for small businesses. In Excel, you can easily create and manage client databases and then export part or all of that data into a Word document, your accounting software, an email marketing service, or use it in other Excel documents, such as a pivot table.
A pivot table is Excel’s signature, and most powerful, feature — Microsoft trademarked the words ‘pivot’ and ‘table’ in their compound form PivotTable back in the 1990s. So if you intend to use Excel in any meaningful way for your business, knowing how to create and work with pivot tables is an essential skill, one which we cover in our newly-updated, advanced Excel online training courses.
What are pivot tables used for?
A pivot table is a way to quickly summarise and analyse large amounts of data, and the pivot tables you can create in Excel are especially designed for:
Subtotalling and aggregating numeric data
Summarising data by categories and subcategories
Creating custom calculations and formulas
Expanding and collapsing levels of data
Drilling down on details from summary data
Filtering, sorting, grouping and conditionally sorting data
Presenting concise, attractive, and annotated reports
Moving rows to columns and vice versa (‘pivoting’) to see different summaries of source data.
Pivot table data sources
There are a few ways that you can create a pivot table, though the most common way is to use an existing Excel worksheet — a database, for example — as a data source. Here are a few ways to create a pivot table in Excel:
Excel tables: Excel tables are already in list format and are good candidates for pivot table source data. When you refresh the pivot table report, new and updated data from the Excel table is automatically included in the refresh operation.
Using a dynamic named range: To make a pivot table easier to update, you can create a dynamic named range, and use that name as the pivot table’s data source. If the named range expands to include more data, refreshing the pivot table will include the new data.
Create a database in Excel first
The most efficient way to create a pivot table is to create a database in Excel first. Here, you can update and manage as much information about your business — including customer data and financial data — and then use that as a data source for a pivot table.
Managing large amounts of client and business data is not only possible, but also something you can learn fast and with ease with the help of our comprehensive range of online Excel training courses – covering all skills levels.
MICROSOFT EXCEL IS THE most widely-used spreadsheet application in modern computing. It’s ubiquity means most people use Excel on a regular basis, despite never having had any formal training in its many, many, MANY functions.
With its 2013 release, Excel got a serious update, which made it the perfect application to create and manage client and customer databases. Although there are many CRMs available on a subscription that provide the same functions of a database created in Excel, just in a more visually appealing format, they often lack reporting and analysis functions, requiring you to export your data in a Excel sheet anyway.
Flat file databases
Excel’s original ‘flat file’ database still remains the easiest and most basic database to set up and manage, and depending on your business and how you’ll use your database, a flat file database may be all you’ll ever need. If set up correctly, a flat file database will allow you to easily import your customer data into Word, your accounting software, an email marketing service, and so forth.
Relational databases
A relational database is a database that’s structured to recognise relations among the information stored in them. Microsoft offers a relational database program, called Access, which is available with Microsoft Office Professional or higher, or can be purchased separately.
Alternatively, you can create your own relational database in Microsoft Excel, providing you have the 2013 version or newer. When Excel got its update in 2013, it became easier to link charts and cells and to perform searches — all essential features if you’re working with large amounts of business data.
Correct Excel set up is crucial
Once Excel has been set up, it’s as easy as it is powerful to use. Of course, the key is to set it up correctly, so you can avoid errors or having to re-enter large amounts of data to make the format suit another third party software application.
It can be easy to lose track of separate income streams; Excel is a great tool for monitoring which work your income is coming from.
IF YOU’RE AN INDEPENDENT contractor, or you’re a full-time employee about to start up a side business, then you need to be able to keep a good track of all your income streams. There are a couple of reasons for this and both of them relate to tax. Continue reading What to Do When You Have More than One Income Stream
Case Study: Costs for starting up a second, related business
Contemplating starting a second business, related in some way to your first? Excel can help you forecast start up costs.
A LOT OF BUSINESS OWNERS branch out into related fields when their flagship business becomes successful enough (just look at Jim’s Mowing). However, this can be a bit dicey if the business owner doesn’t properly forecast all the start up costs. Not doing so can not only have an adverse impact on the new venture, but also on the existing business.
In this case study, we’re going to look at the start up costs associated with starting a real estate sales business. With real estate licencing laws changing and digital marketing available to everyone the ability to start your own business and work at home is now very realistic.Continue reading Thinking of Starting a Second Business? Introducing Jerry
Repayments on a business loan may be less than super and PAYG combined
It’s not uncommon for small businesses to take out a business loan to meet their super and PAYG obligations – but this should never be a knee-jerk reaction to lean times.
Depending on how many employees work for you, the repayments on a business loan are typically smaller than all of your payroll obligations — this includes superannuation and PAYG — combined. If you get a loan to fund 12 months of your business, payable over a 24 or 26 month period, the repayments will be far easier to manage each month.
Interest is usually a tax deduction
Businesses are able to claim the interest from any business loan as a tax deduction, so even if the annual percentage rate (APR) adds a few additional thousands of dollars to your capital amount over the period it takes to pay the loan back, the interest will still go towards reducing your taxable income.
This is a more favourable option to delaying payment to your employees (illegal) and delaying payment of PAYG and superannuation withholdings, which could incur a Failure To Lodge (FTL) penalty, plus a general interest charge (GIC). Note: Fines and penalties cannot be claimed as a tax deduction and are therefore dead money.
Do your sums first
Don’t forget that, while a business loan to cover payroll for 12 months will be easy to repay initially, your business’s profits will need to improve substantially over the next year so that you can continue to meet your loan repayments AND your payroll obligations for that year.
***
You can easily work this out using Microsoft Excel. Our Intermediate Microsoft Excel training courses show you how to determine if you can afford to take out a mortgage, but because all of our fields remain “unlocked”, you can easily modify them to suit a business loan scenario. Visit our website for more information on all of our Excel training courses.
Our online training courses feature real-life case studies to make our learning more relevant and true to life.
Using Excel to work out your PAYG and super obligations is a great way for small businesses, with a small number of employees, to save money. It saves you having to purchase this extra module in MYOB or Xero, for instance, when you may rarely use it. Saving money for small business is crucial as often it’s these same small businesses that have trouble making payroll payments each week, fortnight or month — and then wind up incurring further fees from the ATO when they’re late with their reporting and payments. It’s a vicious cycle.
Get financing. There are lots of ways to do this, but a common method, particularly if you need access to funds quickly, is to get a short-term business loan. Many short-term business loans don’t require businesses to have a great credit score, and will offer funding of as little as $5,000 right up to $500,000.
You’d have between 3 and 36 months to pay back the loan, but you need to be aware — the annual percentage rates (APR) are usually high. Most lenders require the business to have been active for a minimum of 9 months, and have revenue of more than $75,000 per annum. However, if paid off quickly, these can be an alternative to incurring penalties — it will obviously depend on your business’ individual circumstances.
Keep on top of bookkeeping
If you stay on top of your bookkeeping, you’ll either reduce the likelihood that you won’t make payroll, or as a worst case scenario, be able to foresee the periods when you won’t be able to, and be able to arrange finance in time to cover it.
***
Use the Ad Hoc Payroll Guide included in our Intermediate Microsoft Excel training courses to determine the rate of PAYG tax to withhold — and the required super contribution amounts in Excel. Visit our website for more information on our entire suite of Excel training courses.
Type the first 3 characters to discover courses, up-skilling programs and CPD articles.
EzyLearn's Career Academy
Enrolled into an EzyLearn course since 2013? Get access to new & updated course content and support by joining the EzyLearn Course Refresher Access membership Program. See how to extend your course life & support.
Xero is a great bookkeeping program for tradies who are on the go and using their phones (or a tablet) all the time. From receipts scanning to creating quotes and invoices, receiving payments and keeping track of project costs.
bookkeepercourse.com.au/produ…
Don’t forget that, while a business loan to cover payroll for 12 months will be easy to repay initially, your business’s profits will need to improve substantially over the next year so that you can continue to meet your loan repayments AND your payroll obligations for that year. 
