We’ve added some new training materials in the Advanced Excel course relating to getting and transforming data and using the Power Query. It’s very relevant in our current data driven environment where we have many sources of data that we need to make sense of. Employers are looking for good staff with Microsoft Excel Advanced Certificate skills.
Power Query enables you to query large datasets from multiple sources and derive meaningful information and insights. Turn Excel into a Business Intelligence system or data analysis tool for research data! This NEW Excel Advanced Training Course material is available for FREE if you are a current student and for a very low cost if you’ve enrolled into Excel with us in the past.
We made the move online a long time ago and with it came great benefits but it wasn’t a simple case of doing the same thing we did in our Sydney training centres! We had to deal with the loneliness and disconnection associated with not being in an office with others!
Our new, updated online business courses are designed to give you tools to be confident doing your work online.
There’s new content in our Advanced Microsoft Excel Course about Excel Macros that automate the reformatting of database information, exported from Xero Accounting so it can easily be imported into MailChimp for email marketing.
If any of these words don’t make sense to you, you need to upskill to get up to speed because this terminology is becoming common in most workplaces.
So you want to learn Excel quickly and you’re on a budget? Buy our Excel Beginners’ to Intermediate training workbooks (with exercise files) and start getting a handle on how to master the world’s number one business productivity software program.
When it comes to Excel skills, a needs analysis can cost money and take a huge amount of time, when in reality, staff can simply do their own needs analysis when they do their courses.
A lot of EzyLearn’s students are re-skilling or up-skilling because they are sent from rehab and retraining companies. This may be because they’ve been injured at work and need to move to a different area of the workforce. In these situations, many aspects of the person’s needs have to be assessed to make sure they can work in an office, as opposed to out in the field where they use to work.
In this situation a needs analysis and learning plan are very important; however, when it comes to ensuring all your staff are simply up to speed at an advanced or intermediate level in Microsoft Excel can be much simpler.
Enrol into all Excel Training Courses with EzyLearn for the same price (but usually much less) than going to a physical training centre. You receive ALL skill levels and students can start at the level they “think” they are at and simply do the beginner levels if they realise they need it.
After we shifted to delivering online courses, we discovered that by bundling all our Excel courses together for one low price, companies could forgo spending many hours and many hundreds of dollars in conducting needs analysis for their staff, only to find that their employees’ skills weren’t quite at the level stated.
Give staff comprehensive training
We’ve bundled all of our Excel training courses into 9 half-day short courses — each taking 4 hours to complete — so that, regardless of a student’s skill level, they can access all of the course content from beginner right through to advanced.
Companies can let their staff do their own needs analysis, while giving them comprehensive Excel training at the same time.
Many students who enrol in our Excel training courses for the intermediate and advanced modules often report that they’ve learned lots of new skills by also taking the beginner modules, while beginners usually always continue training right through to the advanced stage.
The great bonus of giving intermediate Excel users access to beginners courses is that most of them go through these and find they have a much more rounded level of skills using Excel.
Excel has real-world case studies and scenarios
Our Excel training courses use real-world examples so students can apply their new skills to tasks and projects you mightn’t ordinarily consider using Excel for — working out how much money a property investor can borrow, for example.
Even if your staff only require rudimentary Excel skills, by providing them with comprehensive Excel training for a fraction of the cost of in-person beginner training, you’re creating a workforce of highly skilled employees for your business.
When you choose the lifelong learning approach for your staff, they’ll have access to all Excel courses for life so they can access them whenever they want or need them.
Lifetime Access lets students up-skill at their own pace
When you enrol your staff in our Excel training courses, choosing the Lifetime Access option means your employees can up-skill at their own pace. This is especially useful for employees who may only require rudimentary Excel skills in their current role, but who are later promoted or give more responsibility, requiring great Excel knowledge.
Rather than enrolling them again, your staff will still have access to all the Excel course content so they can go back and refresh their skills as they need to. And that’s all for just one low price!
IN LARGER COMPANIES a Needs Analysis can be a powerful tool for delving into a problem and coming up with a solution.
However, when it comes to Microsoft Excel Training, asking students to complete a needs analysis can be a complete waste of time.
Why? Because most students overestimate their skills — not because they’re being dishonest, but because they will have come across many terms before and, because they can use certain jargon in conversation, they tend to think they know how to do the associated skill.
When we put our training courses online, we realised that instead of wasting time doing needs analyses — which aren’t accurate most of the time, anyway — we could bundle all of our courses together and cover every skill level, for one low price.
Typical Excel Intermediate Course Scenario
As a Microsoft Excel Intermediate Trainer, one of the most awkward moments I remember when doing face-to-face training was when I started going through one of the very first exercises with a new group of students, only to realise that 40% of the people in the room had no idea what I was talking about.
It’s worse in a face-to-face group training environment because everyone else in the group will now be disadvantaged. When we used to conduct these classroom style Excel classes, we would ask people to go through and supplement their lack of knowledge by reading the Excel Beginners workbooks.
Online Excel courses are even better — you can get everyone to start from scratch and they can all be on the same page.
Bundling all pf our Excel courses together means businesses can save many hours of needs analysis and hundreds of dollars, by enrolling staff in our Excel training courses.
Besides, most students report that, even though they might do our training courses for the intermediate or advanced modules, they learned lots of skills from the beginner courses, too.
Everyone Learns Something in an Excel Beginners Course
Basic / Beginners’ Excel courses take students through the different tasks that are possible with spreadsheets, introduce commonly used terms and set the foundation for more advanced features, covered in the intermediate and advanced courses.
If you’re subscribed to this blog, then you’ll know that EzyLearn use to operate physical training centres before we moved all of our content online in 2006. In those early days, we did a lot of needs analysis for the Commonwealth Rehabilitation Service (now called Disability Employment Services).
Through this organisation we received Workcover students, who needed to brush up on their Excel skills before returning to work, and who often they had to change careers from physical work to more office type jobs.
Many students enrol in our courses only requiring a beginners’ level of training but proceed to the advanced modules by choice. This is because our online courses are built in a logical progression from basic to more advanced, building constantly on the new skills taught. Students also find that the Excel course content helps them in their everyday tasks and work, no matter what their field.
Thinking of investing in property? “Wannabe property investors” will find our Excel courses very helpful because they contain exercises to help you:
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.
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!
No amount of data is too big for Excel’s pivot tables
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.
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.
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.
WHEN YOU’RE SELF EMPLOYED you are responsible for managing your taxes and your superannuation — the latter of which many business owners let go by the wayside. It’s almost always because they don’t have the cash reserves to contribute to their super fund regularly enough.
Just as you would create a budget to make a business investment or asset purchase, you can use Xero and Excel to determine how much super you should contribute on your behalf, and then make the payments.
Run a cashflow report
You’ll learn how to run a cashflow report in our Xero training courses. This report will show you the periods when cashflow is liquid and when it isn’t. Run a cashflow report for a couple of different periods, and export them into Excel. This will give you a better idea of trends and cycles in your business.
At time of writing, the superannuation guarantee is 9.5 percent of your gross revenue, before taxes, expenses, etc. If you set your prices correctly, you should have already factored this 9.5 percent into your prices or hourly rate. If you haven’t, you ought to consider revising what you charge customers and clients.
If you were an employee of a business, your employer would be required to make super contributions on your behalf, at least each quarter. Because you’re self-employed and self-managing your super contributions, you can make them as frequently or infrequently as you like, so long as you’re contributing the correct amounts. (Speak to your accountant or financial advisor, however, if you’re salary sacrificing above the minimum amount — this may affect your tax.)
Make super contributions
Once you’ve determined how much you should contribute to your super fund each quarter, refer back to your cashflow report and to the periods where your cashflow is especially liquid. Are you able to make your contributions each quarter easily, and without compromising your business’s liquidity? Would it be easier to make smaller, more regular contributions?
The decision is yours.
Use Xero to make your super contributions. Xero is connected to a superannuation clearing house, and if you’ve been using to Xero to pay yourself a wage, it’s the easiest way to do so. If you’re not using your accounting software to pay yourself a wage, you can make the payment directly out of your bank account, however, you’ll need to track this in Xero for taxation purposes.
We mentioned that Jerry should use his accounting software to determine whether his he’ll have the start-up capital required to fund his new venture for the next 12 months. The best way to do this is to create a cash flow forecast, and we’re going to show you how.
Cash flow is a better indicator of available funds
If you’re wondering why you wouldn’t create a profit forecast, it’s pretty simple. Cash flow represents money in the bank, after you’ve paid all your suppliers and staff and loan repayments and so forth, while profit just shows how much the business earned but doesn’t take into account any cash outlays.
Profit just shows how much the business earned but doesn’t take into account any cash outlays.
It’s important to understand that it’s not uncommon for businesses to be profitable; however due to cash outlays, these same businesses may not actually have enough money in the bank to fund investment, or in this case, a new venture.
Generating a cash flow report in Xero
Follow these steps in Xero to generate a cash flow report for your business:
Go to Reports, then click All Reports.
Under Financial, select Cash Summary.
Enter the following report settings:
Date — The latest finalised month
Period — 1 month
Compare With — Previous 11 Periods
Select the Include GST and Show YTD filters
Click Update to generate the report in Xero
At the bottom of the report, click Export and select Excel to download the report in Microsoft Excel format.
Set up formulas to forecast 12 months ahead
In Excel, you’ll need to create formulas that will show you the average cashflow of your business across the previous 12 month period, so you can then forecast ahead for the next 12 months.
The feedback we receive from our students helps us to understand what’s working and what isn’t, which is why it’s invaluable to us that you get in touch and let us know what you think.
It was based on the feedback we received from our students that we decided to develop our Reach Accounting and Xero training courses. It is also based on the feedback we received that we changed the way we issued our certificates so that students received them faster and could use them to find work.
Here’s just some of the feedback we’ve received from our students recently:
“I found the workbooks the most helpful for giving practical experience.” — Karen Dimitri, Glengowrie SA 5044
“The best part is that you can do it on your own time and pace.” — Juliana van Wyk, Hilton WA 6163
“Short, easily digestable videos. Can fit in easily with a busy lifestyle.” — Korina Power, North Shore, Auckland 0630
“I could learn at my own pace.” — Jackie Smith, Sheidow Park, SA 5158
“Doing the workbooks and watching the videos at my own pace has helped me a lot in pursing the current workforce requirements.” — Merritt Ray, Loganholme QLD 4129
“I was able to finish the whole course in just a couple of weeks.” — YoonOck Lee, Atwell, WA 6164
“By watching videos on one particular topic and doing a test straight after relating to those videos, you don’t become too overwhelmed with too much information.” — Michelle Bankstown, NSW 2200
“I am happy as I could completely the course at my own pace. It was easy n simple to understand. As a mother I felt the course was very time efficient. Looking forward to putting my knowledge into action.” — Kimberline Francis, St James, WA 6102
“This course is best for me because I can access any time from home, I can replay any video provided when I didn’t understand.” — Mika Humphreys, Innisfail, QLD 4860
“Everything within the course itself was great. I liked the most how easy it was to understand and navigate through.” — Katie Davis, Whyalla, SA 5608
“Being able to actually move around the sample company file to get a feeling of how the software is structured, made me have more confident.” — Joy Khoo, Mudgee, NSW 2850
“The best parts of this course is that we get freedom to learn and complete this course in your own suitable time. There is not so much pressure that you have to complete in certain time limit. I would advise and recommend this course from EzyLearn to international students who want to further their career in bookkeeping and accounting. It was a great privilege to be part of your institute.” — Prabin Gurung, Auburn, NSW 2144
We’d love to hear from you too. Get in touch via our course evaluation page.
As part of our MYOB Training Course, we’re constantly adding new content and helping our students win work and improve their employability. That’s why we always emphasise the importance of continuing to educate yourself — for the obvious skills advantages you bring to any company, but also for your own confidence.
For many people, it can be a little nerve racking when new blood enters the workplace and in today’s job market, it’s not at all uncommon to see a new face appear. Sometimes it’s in the form of a new employee there to share your workload and other times it’s because your organisation has employed someone to help restructure the business so they operate more efficiently.
The Many and Varied Threat
For many employees, this new face around the office can make them feel threatened — threatened about the future of their jobs and threatened by the prospect of the office dynamic changing. Fortunately, however, there’s an antidote for this rather common reaction to change: taking a course or continuing professional development.
The thing about feeling threatened is that it’s entirely to do with your confidence. You may feel like you’re not quite as educated or as skilled as this other person and as a result, you think your employer won’t need you any more. The truth is, your employer probably has no intention of letting you go, but nevertheless, the job market is changing and up-skilling to increase your knowledge base is invaluable — to you and your employer.
Confidence in Upskilling and Continuing Professional Development
Aside from the tangible knowledge and experience a highly-skilled employee brings to an organisation, they also bring with them confidence. The more secure you feel with your ability to do your job and do it well, the more confident you’ll feel as a result. For this reason, EzyLearn is a big believer in continuing professional development.
Our MYOB, and Microsoft Word and Excel training courses each come with lifetime access to the course content, so that you can review the course material and even access updated content, whenever you need it.
A truly confident person would embrace the idea of working with someone new and potentially learning and sharing knowledge, rather than feeling threatened by them. But the key to achieving this confidence is being able to feel secure in your own knowledge base and that’s what EzyLearn’s Continuing Professional Development Program aims to provide bookkeepers with: knowledge.
So stop worrying, stop feeling threatened, and feel more confident in your skills and knowledge by taking one of our online courses today!