BODMAS and why it’s important to understand it in Excel

BODMAS

BODMAS is a way of remembering the order of operation that Excel follows when it evaluates values in a formula.

BODMAS stands for:

B – Brackets
O – Order (Indices such as 22 etc.)
D – Division (in an Excel formula we use a /)
M – Multiplication (in an Excel formula we use a *)
A – Addition
S – Subtraction

Example:

What is 4+6/2*3-1?
If you “read” the formula (left to right) you will get 14
If Excel calculates it for you, the answer is 12

These are two different answers, but only one is correct.

In BODMAS, division & multiplication come before addition & subtraction, so what Excel has done is the following…

  1. Divide (there are no brackets or “orders”): 4+6/2*3-1 ⇒ 4+3*3-1
  2. Multiply: 4+3*3-1 ⇒ 4+9-1
  3. Add: 4+9-1 ⇒ 13-1
  4. Subtract: 13-1 ⇒ 12

Of course, if we needed the answer to be 14, then we could have achieved that by using brackets – Excel is still applying BODMAS, but we’re forcing it to calculate in the order that we need, so…

(((4+6)/2)*3)-1 ⇒ 14 – Excel starts with the “innermost” set of brackets & works outwards, so in this case Red, then Blue, then Green

So why is this important?

Why all of this is important is that Excel will calculate this way unless you force it (by using brackets) to do something different… with numbers it might be obvious what has happened, but if you’re building a spreadsheet creating formulas (especially where you do not, yet, have any data) you might enter a formula that “makes sense” to you, but which Excel may treat differently… which is why I always advise people to build spreadsheets with dummy numbers (3, 4, 5, etc or 10, 20, 30) which will test their calculations and, hopefully, easy to check the answers without a calculator!

When testing, I don’t use 0 (zero) in case addition or subtraction is involved (the numbers won’t change), I don’t use 1 for the same reason but with regard to multiplication & division & as 2+2 = 2×2 (2’s can be dangerous!!)

Case study: DSL Engineering

Summary

Using Microsoft Excel spreadsheets to generate effective management dashboards

The Client’s problem

DSL Engineering - Excel dashboardThe client, an engineering company, produced lots of statistical data about their business: the amount of work invoiced, the number of jobs completed, work in progress, new quotations, etc, but it was all held in separate spreadsheets making analysis time consuming & difficult. This also meant that whilst they had lots of data, they had little information.

How we solved it

We created a single spreadsheet to pull all of the information collated together. From this we then produced a series of charts to show the information in a much more user-friendly way. We then created a series of custom views (driven by macros) to allow them quickly switch from data input to a review of the current month’s information. These charts were then grouped together to form a single dashboard for the company.

The benefits for the client

The data that was collected is now presented quickly and in an informative way via the dashboard allowing a clear picture of activity & progress to be ascertained. In turn, this allows key decisions to be quickly taken based upon accurate & up to date clear information.

Case Study: Ashford & Co – Chartered Accountants

Summary

Using Excel and Word to allow an effective MailMerge saving time, effort and money

The Client’s problem

Ashford and Co - MailmergeOn a regular basis, this accountant sends out a printed newsletter to each of his clients; following each budget, he also sends out a “Tax Facts” card. Periodically during the year, he also sends out specific information to sub-groups of his client base pointing out relevant information.

In order to do this, he would have to go through each of his client’s files to check their contact details & any specific information that they needed to be informed of. This was very time consuming

How we solved it

We created a simple Excel database that not only listed his clients & contact details, but also grouped which information they needed to be aware of, for example “Pensions”, “Rental Income”, “Company Annual Return due”. We then created some standard template letters which we linked to the database via Word’s MailMerge feature allowing him to quickly generate any necessary letters filtering out any as required.

We also showed him how to perform the MailMerge “from scratch” to allow him to create his own new letters where necessary

The benefits for the client

Many hours of manual work was now saved by a much more streamlined process. In addition, he could be certain that the correct letters were sent to the correct clients with a much reduced chance of human error creeping in.

In addition, the Excel spreadsheet can now be used for additional marketing processes and client management.

Case Study: Adrian Cunningham LLB ACA – Chartered Accountant

Summary

Creating a Powerpoint presentation to help with a client rebranding

The Client’s problem

Adrian Cunningham LLB ACA - PowerPoint presentationAccountants have always “suffered” with a perception of being “boring” & “grey” – all courtesy of the Monty Python sketch: “The Vocational Guidance Counsellor”. So, when we were approached by a colleague from that profession, we were pleased to note that the recent rebranding that he had commissioned was bright & vibrant. However, the PowerPoint presentation that he had produced to support his Business Spotlights at networking events was more Python than Panache & certainly didn’t reflect his new branding; we were challenged to help.

How we solved it

The first stage was to obtain from his graphic designer copies of his new images, including details of the colours & fonts used. These were then incorporated into a vibrant presentation, reworking the key points from his original draft to ensure that all of the key points were included and ensuring that the colours, fonts & logos matched his new rebranding to help reinforce this.

The benefits for the client

The client received a presentation that focused on the 5 key areas of his business with sufficient information to engage & inform his audience in expanding on these themes, whilst at the same time being able to be delivered, without rushing, in an 8-10 minute timeframe.

In addition, we produced a “timed” version looped to run continuously to support attendance at seminars and exhibitions.

Case Study: Steel Stockholder

Summary

Using Excel spreadsheets more efficiently to reduce manual input and speed up the generation of information

The Client’s problem

Client had a number of spreadsheets with totals needing to be copied from one to another to allow input of rebates from suppliers & calculation of final Gross margin. During the existing processes, sales significantly above or below expected Gross Margins were manually checked for errors & then adjusted to account for rebates which may have brought the margin into “acceptable” levels.

The analysis work didn’t start until the end of the month when sales reports were available as it was a time consuming process to get the information from the system reports into their Excel spreadsheets.

This meant that much manual effort was required to arrive at an accurate gross margin figure and any correcting invoices or credit notes were not processed until the following month

How we solved it

First of all, we ascertained that it was possible to run the system reports at any time during the month and that it could be saved to a text file (csv would have been better, but that wasn’t possible). We then developed a macro to split the text file across a number of columns and strip out the unnecessary (repeated header) information from the text file when opened in Excel.

We then built a number of lookup tables in a “master spreadsheet” for the amount of rebate available by product type from each supplier and also for customer/rep/area information together with the necessary formulas to allow the data from the latest report to be recalculated to generate both revised (and accurate) gross margins. This was then further analysed using filters and Pivot Tables to allow margins outside of agreed parameters to be quickly checked & sales & margin performance by customer, rep & area quickly identified

The benefits for the client

As the detailed information could be generated from the system at any time & much of the processing was automatic through macros, pre-populated formulas, filters & Pivot Tables, it was possible to perform the analysis on a weekly, rather than monthly basis.

This had a positive impact on cash flow as any necessary supplementary invoices or credit notes could be issued during the relevant month, rather than, as previously, in the following one; customers then had no excuse not to pay on time.

In addition, sales & margin analysis was much more timely allowing for management decisions to be made more quickly with more accurate information.

Testimonial from GEM Business Support

Excel database for Mailchimp

I had a client who urgently needed an excel database putting into MailChimp for a campaign. There was 4,000 people on the list. The names had been logged in a single column which meant that I wouldn’t be able to personalise the campaign. I emailed Colin and asked if it was even possible to split a single excel cell with a first and last name into two columns. Within minutes Colin had sorted this for me. I can’t thank Colin enough, and neither can my client. What looked like a long job was done in seconds.  It truly pays to know Colin, his knowledge around excel and word is invaluable.  Thank you

Gill Arnott: Gem Business Support

Testimonial from the Museum of London

Intermediate Excel Training

Colin provided an absolutely excellent and well-organised Intermediate Excel training course, which has helped me immensely in the day-to-day management and administration of my role. The course was thoroughly explained in a clear and understandable manner, and I felt at ease to ask questions throughout the day.  Colin has also been on hand since the course, answering further questions very promptly and saving me hours of time. Whenever he provides help, he also gives a broken down explanation of the formula, which has been very useful. Colin was very approachable and personable, both during and after the course. Thank you!

Felicity Crimes: School visits Fund Administrator – Museum of London 

Further testimonial from Airbus Defence and Space

Relevant PowerPoint training

All aspects of this PowerPoint training were relevant and will be useful to me going forward in my day to day work. Of particular benefit – the training was tailored to my specific PowerPoint needs, contained useful examples/practice and delivered in such a way that I can immediately benefit from the topics covered. I estimate the training will save me some 4 hours per slide deck (half a day each) in terms of preparation time and will result in higher quality presentations being presented to my customers. The PowerPoint training was very worthwhile from a business perspective and my time was very well spent attending this days training.

This course was very useful to my day to day work and what we set out to cover in the training was very well covered/addressed. I wish to thank US4B for their very good/tailored preparation and delivery of this course. Colin, the Trainer went to great lengths to ensure the course itself was prepared to focus on my specific PowerPoint capability gaps and in delivery all topics were covered in good detail, pace and exercised/exampled. In follow up Colin has further assisted me with some follow up examples to close some questions I had regarding some specific current PowerPoint challenges I have which is real added value. I have no hesitation in recommending US4B Ltd for consideration for the delivery of similar training across the wider business (sales/business development/ marketing)

Paul Gray: Strategic Business Development – Geo-Intelligence – Airbus Defence and Space

Testimonial from Airbus Defence and Space

Feedback from delegates has been really positive

Colin Foster has been our new trainer for our soft skills requirements. As with any new supplier we were not sure what to expect, however, feedback from delegates has been really positive and based on this we would not hesitate to book Colin again.

Some feedback from our staff below;

  • “Colin established what we needed and spent a lot of time working with us to help us learn the most relevant and useful parts of the course in depth then we covered the more simple things quickly using the course material”
  • “Colin adapted the course to our needs which he asked for at the start of the day. The training manuals are very professional and comprehensive in their content”
  • “Excellent at keeping me interested in the course”

Helen Allen – Executive Assistant | Geo-Intelligence Airbus Defence and Space

Testimonial from Tracey Booth – 80:20

Advanced Excel training

Colin provided some Advanced Excel training for me. The skills I have learned from Colin have enabled me to improve the quality of the service I offer to my clients and significantly reduced the time taken to produce my monthly Management Reports. I found the export of data from Sage into Excel particularly useful and I am now able to produce much more sophisticated detailed reports in a fraction of the time it would have taken me before. All in all, well worth the investment and highly recommended ! Thanks Colin.

Tracey Booth 80:20 Financial Management