Online Excel Course:
Business Calculations using Excel
We hope you enjoy our Q&A Online Excel Course /Reference Guide on business calculation situations you might encounter using Microsoft Excel. This short excel online training course will offer possible solutions to many of these calculations like percentage increases, mark ups, percentage variances etc . The aim is to help minimise mistakes when you are making important calculations, especially where any such mistakes might have a serious consequence.
This question and answer online excel training course will be suitable for those who have basic excel knowledge with using functions and building formulas. If some calculations require knowledge of other functions or techniques, these will be clearly outlined as will the difficulty level of each solution. This online excel training course can also be used as a practical examples & solutions complement our online excel course- WorkPlace Excel.
Objectives of this Online Excel Course:
Many Excel users throughout their career will need to use Excel to perform calculations that express the key operational metrics of their organization’s data .We want to be able to do that without embarrassing errors occurring. These will includes calculations like- percentage increases, variance percentage changes, applying discounts, rounding up figures etc.
In this ‘solution type ‘ online excel course , we will show the basic maths of the situation and then how to implement them in Excel accurately and safety , helping you to avoid making costly reputation errors in your analysis work.
Also this online excel course solutions will be here as a future reference point when you come across any of these specific problems in the future.
This online excel course on the Excel Look up situations will take 3-4 hours.
Online Excel Course
- Calculate the performance in percentage terms to budgeted target.
- Percentage Variance calculation for Positive and Negative numbers
- Calculating a Percentage of the Total Amount.
- Calculating and Applying a % Increase
- Calculating and Applying % Discount
- Calculating a Running Total
- Divide by Zero Errors
- Rounding Calculations
- Rounding up and down Calculations
- Rounding Up and Down to the Nearest Cent
- Round up to Millions etc for Financial Reports
Calculate the performance in percentage terms to budgeted target?
How does the actual performance compare to the forecasted or budgeted target in percentage terms. Mathematically to achieve this, divide the budgeted target by the actual. For example, if we budgeted to sell 100 laptops and we sold 60, then your performance percentage is 60% (60/100).
Don’t forget to apply the Percentage format.to your formula cells.
Calculating a Percentage of Total Amount.
We want to measure in percentage terms what each component part contributed to the overall total.
In our screenshot below , we see the sales for each of our products and the total sales but what is, for example, the laptop sales revenue expressed as a percentage of the total sales etc.
To achieve this , we divide each product’s revenue by the total. The Excel formula for laptops would be =C6/C$12
Note cell C12 needs to be a partial absolute reference as we are copying the formula down , so the row element is locked.
Calculating and Applying a % Increase.
To learn how to perform this common calculation ,let’s take the following example. You need to apply a 20% increase to the price of a product as in the screenshot below .
So we multiply the original amount by 1 plus the percentage of the increase. So we add 1 to the 20% , which gives 120%. You then multiply the original price of 200 by 120%. You then multiply the original price of 200 by 120%
The formula is =D6*(1+E6)
Make sure you use parentheses, as we want Excel to do the addition first and then the multiplication. Without parentheses, multiplication would be done first, giving us the wrong answer.
Calculating and Applying % Discount
To apply a discount to a product’s price, for example, applying a discount of 10% to a product costing 500. See screenshot below.
To achieve this , subtract the % discount from 1 and then multiply by the original amount.
So in our example, we subtract 10% from 1 which gives 90% and multiply this by original price of 500.
The Excel formula is =D6*(1-E6)
Percentage Variance calculation for Positive and Negative numbers.
A variance shows the difference between one value and another value.
We sold 200 laptops yesterday and 300 today, so we sold 100 more laptops today , so the difference in sales was 100 by simply subtracting 300 from 200 ( the anchor number), giving a unit variance of +100
To see this as a percentage, we need to do the following calculation: Subtract the anchor number from the new number and then divide the result by the anchor number. In our example, the calculation would be
So the percentage variance tells us that we sold 50% more laptops today compared to yesterday.
Note the order of precedence in Excel. Division and multiplication are performed before addition and subtraction , so we need to use parenthesis to force Excel to subtract first , otherwise we would get an incorrect answer. Putting the first part of the formula in parenthesis means Excel performs this calculation first .
Alternative calculation method:
We could also use this safer method of calculation instead. =E6/D6-1
Another danger occurs if dealing with negative numbers. Let’s say that our benchmark was -200, so the calculation would be 300 minus -200 / -200 which is -250%. This is definitely wrong as we have increased our sales. The maths here is inverting the result.
The solution is to use Excel’s ABS Function. The Excel ABS function returns the absolute value of a number. Negative numbers are converted to positive numbers, and positive numbers are unaffected.
So to get the correct result , the formula would now be
Calculating a Running Total.
Next in the online excel course, we will learn the useful technique of creating a running total of values.
This calculation can be done in Excel by using the SUM function with a partial reference.
The formula would be =SUM(C4:C$4)
The key to the formula is locking the first cell reference so that it remains the same, while the second C4 will change to C5, then C6 , C7 …etc as the formula is copied down.
Divide by Zero Errors.
In Excel as in mathematics, division by zero is impossible.
So in Excel , we don’t want such calculations to occur as it will only result in the #Div/0! error appearing.
We can stop such calculations occurring by wrapping the calculation in an IF Function as in screenshots below.
The Excel formula is =IF(D6=0,0,(“perform calculation”))
The IF function tests the condition. If it’s true i.e. cell D6 contains 0, then the IF function returns 0 but if cell D6 does not contain 0, then the calculation involving D6 takes places.
To round a number is to eliminate the least significant digits, making your numbers simpler and easier to comprehend while keeping close to the original values.
Microsoft Excel provides a number of functions to handle the different rounding situations you might encounter in your career using microsoft Excel.We will now look at some of these in our online excel course.
The Excel ROUND function returns a number rounded to a given number of digits. The ROUND function can round to the right or left of the decimal point.
=ROUND (number, number of digits to round to)
If the second argument is >0, the number is rounded to the specified number of decimal places to the right of the decimal point. If negative, the number is rounded to the left of the decimal point (i.e. to the nearest 10, 100, 1000, etc.).
Let’s see some examples as in screenshots below
Let’s see some examples using negative numbers for the second argument. Think of this as rounding the integer number to the nearest 10, 100, 1000,10,000, 100,000, million etc
So to round a large number to the nearest million , use -6 as the second argument.
If the number is 1,250,000 and the formula used is =Round(1,250,000, -6) it will return a value of 1,000,000. If the number was 1,780,000 , the above formula would return 2,000,000
Let’s see some more examples.
Rounding up and Rounding down Calculations.
Often you need to force rounding in a particular direction. For this we can use the ROUNDUP or ROUNDDOWN functions.
The Excel ROUNDUP function returns a number rounded up to a given number of decimal places but unlike the standard rounding function where numbers less than 5 are rounded down, ROUNDUP always rounds numbers 1-9 up.
So for example with the number 2.13587543, if we use the formula =ROUNDUP(B9,1), it will return the answer 2.2 , while the Round function would have returned 2.1. Also ROUNDUP(67.45,0) returns 68
But on the other hand, the Excel ROUNDDOWN function returns a number rounded down to a given number of decimal places. Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDDOWN rounds all numbers 1-9 down.
So for example with the number 2.18587543, if we use the formula =ROUNDDOWN(B9,1), it will return the answer 2.1 , while the Round function would have returned 2.2. Also ROUNDDOWN(67.45,0) RETURNS 67
Rounding Up and Down to the Nearest Cent
Many times when making calculations in Excel, you will be required to round the amount to the nearest cent or penny. So now in our online excel course, we see we can achieve this calculation in Excel by using the CEILING or FLOOR Functions.
The Excel Ceiling function rounds a supplied number away from zero, up to the nearest multiple of a given number.
The syntax of the function is:
CEILING( number, significance )
The significance is the multiple of significance that the supplied number should be rounded to.
Let’s look at the calculation to round an amount up to the nearest cent.
We have amounts like 72.243 and we need to round them up to the nearest cent, the formula will be
=CEILING( 72.243,0.01) returning a value of 72.25
With 0.01 being the degree of significance for the nearest cent.
See the screenshot below.
The FLOOR Function works the same way except it causes a rounding down to the nearest significance, in this case to the nearest cent as in the screenshot example.
Round up to Millions etc for Financial Reports
Financial reports figures are expressed normally in terms of millions, tens of thousands etc. Now to make the report clearer to use , we don’t need to present the users with superfluous numbers .
For example, if we have a figure like 383,978, you could choose to round the number to one significant digit, thus the number in the report would be displayed as 400,000. Rounding it to 2 significant digits would result in 380,000
But what if some of the numbers in your financial report are in millions, some in hundreds of thousands, you wont want them all rounded up to one significant digit.
Instead of having to create individual formulas for each number, we will build a formula which will automatically choose the correct level of significance.
See the screenshot below.
Remember from our previous rounding calculations, that the second argument in the ROUND function is the digit of significance. But instead of hard coding this figure into our ROUND formula, we can create this figure on the fly.
To do this, we will use the INT function and the ABS function which we learnt about at the beginning of this solution based online excel course.
The formula would be
where we are using cell references for the values.
To see the formula using actual numbers it would be
How does this formula work?.
Firstly, its important to know that Excel always evaluates from the inner function outwards.
( Also note if you select a function and its arguments and press Function F9, Excel will return the value of the function.)
The ABS returns positive value of the number. This is needed if the number was negative. The INT function just returns integer part of the number and the len functions counts it’s length. So this formula would return 6 . This is then multiplied by -1 to make it a negative number , making it -6 and then this is added to the number of significant digits you are looking for , so its
This -4 will then become the second argument in your round function but it was calculated on the fly and not just hardcoded into your ROUND Function.
How to create a basic Markup formula?
How can you create a basic Markup formula? Well that’s what you will learn next in this online excel course.
The basic formula is €Cost + €Markup = €Sell Price.
That is you add the markup amount to the cost of the item to get the selling Price.
See screenshot examples below
How to calculate percentage markup on selling price?
The next calculation in our online excel course on Excel calculations is how can I calculate the Markup percentages on selling prices?
Below we have a markup on selling price calculator.
It is based on the basic formula of Markups which is is €Cost + €Markup = €Selling Price
That is you add the markup amount to the cost of the item to get the selling price but this time the base is the selling price. It is 100% and the relationships are expressed in relation to selling price.
How to calculate the Markup percentages on Cost?
The next calculation in our online excel course on Excel calculations is how can you calculate Markup percentages on Cost?
Below we have a markup on cost calculator.
It is based on the basic formula of Markups which is is €Cost + €Markup = €Sell Price
i.e is you add the markup amount to the cost of the item to get the selling Price.
So for example, the markup in percentage terms is visible as you change the actual Markup amount. As this calculator is based on cost, cost is the base amount.