powerpoint presentation · excel –working with vat the amount you need in r = the amount you need...
Post on 26-May-2020
3 Views
Preview:
TRANSCRIPT
AIN2601
2018
Excel
EXCEL – Basic Principles
Signs/operators
Multiplication: X = *
Division: ÷ = /
Exponents(“to the power”): a2
= ^
Not equal: = = <>
EXCEL – Basic Principles
Order of operation (Sequence of computation)
Similar to BODMAS rules (Brackets, Of, Division,
Multiplication, Addition, Subtraction)
1. Brackets ( )
2. Exponents ^ , e.g., =2^3=8
3. Multiplication (*) and Division (/) from left to right
4. Addition (+) and Subtraction (-) from left to right
Example: 100 + 100 * 2 = ???
(100 + 100) * 2 = ???
EXCEL – Basic Principles
Order of operation (Sequence of computation)
Example: 100 + 100 * 2 = 300
EXCEL – Basic Principles
Order of operation (Sequence of computation)
(100 + 100) * 2 = 400
EXCEL – Basic PrinciplesChanging the sign of an amount
To change a sign of an amount multiply by -1 or add a – before the
cell reference i.e. –B40 or function i.e. –IF
+100 * -1 = -100
-100 * -1 = +100
Percentages
A percentage = a fraction i.e. 100% = 1.
Cell formatted as %Microsoft Office Excel
underlying value in cell
7%
14%
72.5%
100%
First format cells as a %, then type
0.07
EXCEL – Basic PrinciplesChanging the sign of an amount
To change a sign of an amount multiply by -1 or add a – before the
cell reference i.e. –B40 or function i.e. –IF
+100 * -1 = -100
-100 * -1 = +100
Percentages
A percentage = a fraction i.e. 100% = 1.
Cell formatted as %Microsoft Office Excel
underlying value in cell
7% 0.07
14%
72.5%
100%
EXCEL – Basic PrinciplesChanging the sign of an amount
To change a sign of an amount multiply by -1 or add a – before the
cell reference i.e. –B40 or function i.e. –IF
+100 * -1 = -100
-100 * -1 = +100
Percentages
A percentage = a fraction i.e. 100% = 1.
Cell formatted as %Microsoft Office Excel
underlying value in cell
7% 0.07
14% 0.14
72.5%
100%
EXCEL – Basic PrinciplesChanging the sign of an amount
To change a sign of an amount multiply by -1 or add a – before the
cell reference i.e. –B40 or function i.e. –IF
+100 * -1 = -100
-100 * -1 = +100
Percentages
A percentage = a fraction i.e. 100% = 1.
Cell formatted as %Microsoft Office Excel
underlying value in cell
7% 0.07
14% 0.14
72.5% 0.725
100%
EXCEL – Basic PrinciplesChanging the sign of an amount
To change a sign of an amount multiply by -1 or add a – before the
cell reference i.e. –B40 or function i.e. –IF
+100 * -1 = -100
-100 * -1 = +100
Percentages
A percentage = a fraction i.e. 100% = 1.
Cell formatted as %Microsoft Office Excel
underlying value in cell
7% 0.07
14% 0.14
72.5% 0.725
100% 1
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT 100%
VAT 14%
Amount including VAT 114%
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100%
VAT 14%
Amount including VAT R1 140 114%
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100% 1
VAT 14%
Amount including VAT R1 140 114%
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100% 1
VAT 14% 0.14
Amount including VAT R1 140 114%
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100% 1
VAT 14% 0.14
Amount including VAT R1 140 114% 1.14
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
FORMULA : Amount excluding VAT=
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100% 1
VAT 14% 0.14
Amount including VAT R1 140 114% 1.14
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
FORMULA : Amount excluding VAT= 1
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100% 1
VAT 14% 0.14
Amount including VAT R1 140 114% 1.14
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
FORMULA : Amount excluding VAT= 1/1.14
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100% 1
VAT 14% 0.14
Amount including VAT R1 140 114% 1.14
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
FORMULA : Amount excluding VAT= 1/1.14*1 140
Can think of it as a Maths problem
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100% 1
VAT 14% 0.14
Amount including VAT R1 140 114% 1.14
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R???
FORMULA : Amount excluding VAT = 1/1.14*1 140
Alternative formula = 1 140/1.14
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT ? 100% 1
VAT 14% 0.14
Amount including VAT R1 140 114% 1.14
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R1 000
FORMULA : Amount excluding VAT= 1/1.14*1 140
Alternative formula = 1 140/1.14
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT R1 000 100% 1
VAT 14% 0.14
Amount including VAT R1 140 114% 1.14
EXCEL – Working with VAT
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Amount including VAT = R1 140
VAT% = 14%
Amount excluding VAT = R1 000
Test your answer:1 000 * 0.14 = 140 (VAT)
1 000 + 140 = 1 140 (amount including VAT)
Or
1 000 * 1.14 = 1 140
R %
Microsoft Office
Excel underlying
value
Amount excluding VAT R1 000 100% 1
VAT R140 14% 0.14
Amount including VAT R1 140 114% 1.14
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
R %
Microsoft Office
Excel underlying
value
Cost of sales
Gross profit
Sales
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
R %
Microsoft Office
Excel underlying
value
Cost of sales 100%
Gross profit 25%
Sales 125%
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
R %
Microsoft Office
Excel underlying
value
Cost of sales 100%
Gross profit ? 25%
Sales R4 000 125%
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
R %
Microsoft Office
Excel underlying
value
Cost of sales 100% 1
Gross profit ? 25%
Sales R4 000 125%
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
R %
Microsoft Office
Excel underlying
value
Cost of sales 100% 1
Gross profit ? 25% 0.25
Sales R4 000 125%
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
R %
Microsoft Office
Excel underlying
value
Cost of sales 100% 1
Gross profit ? 25% 0.25
Sales R4 000 125% 1.25
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
Formula: Gross profit R =
R %
Microsoft Office
Excel underlying
value
Cost of sales 100% 1
Gross profit ? 25% 0.25
Sales R4 000 125% 1.25
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
Formula: Gross profit R = 0.25
R %
Microsoft Office
Excel underlying
value
Cost of sales 100% 1
Gross profit ? 25% 0.25
Sales R4 000 125% 1.25
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
Formula: Gross profit R = 0.25/1.25
R %
Microsoft Office
Excel underlying
value
Cost of sales 100% 1
Gross profit ? 25% 0.25
Sales R4 000 125% 1.25
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = ?
Formula: Gross profit R = 0.25/1.25 * 4 000
R %
Microsoft Office
Excel underlying
value
Cost of sales 100% 1
Gross profit ? 25% 0.25
Sales R4 000 125% 1.25
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = R800
Formula: Gross profit R = 0.25/1.25 * 4 000
R %
Microsoft Office
Excel underlying
value
Cost of sales 100% 1
Gross profit R800 25% 0.25
Sales R4 000 125% 1.25
EXCEL – Working with Gross Profit
The amount you need in R = the amount you need in % / the amount you have in % X the amount you have in R
Sales = R4 000
Gross profit % based on cost price= 25%
Gross profit R = R800
Test your answer:
4 000-800= 3 200 (cost of sales)
3 200*0.25= 800(gross profit)
R %
Microsoft Office
Excel underlying
value
Cost of sales R3 200 100% 1
Gross profit R800 25% 0.25
Sales R4 000 125% 1.25
EXCELRELATIVE REFERENCES
RELATIVE REFERENCES
Copy down rows
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1
8
9
10
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1
8 =A2*
9
10
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1
8 =A2*D2
9
10
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1
8 =A2*D2
9 =A3*
10
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1
8 =A2*D2
9 =A3*D3
10
EXCEL – Relative reference
Copy down rows
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1 "=C2/D3
8 =A2*D2 "
9 =A3*D3
10
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1 "=C2/D3
8 =A2*D2 "=C3/
9 =A3*D3
10
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1 "=C2/D3
8 =A2*D2 "=C3/D4
9 =A3*D3
10
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1 "=C2/D3
8 =A2*D2 "=C3/D4
9 =A3*D3 "=C4/
10
EXCEL – Relative reference
Copy down rows
A B C D
1 500 130 98 5
2 600 220 57 7
3 700 990 15 9
4 900 380 37 6
5 200 870 42 3
6
7 =A1*D1 "=C2/D3
8 =A2*D2 "=C3/D4
9 =A3*D3 "=C4/D5
10
RELATIVE REFERENCES
Copy across columns
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1
8
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*
8
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*C1
8
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*C1 =C1*
8
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*C1 =C1*D1
8
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*C1 =C1*D1
8 =C4/A2
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*C1 =C1*D1
8 =C4/A2 =D4/
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*C1 =C1*D1
8 =C4/A2 =D4/B2
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*C1 =C1*D1
8 =C4/A2 =D4/B2 =E4/
EXCEL – Relative reference
Copy across columns
A B C D E
1 500 130 98 5 5
2 600 220 57 7 7
3 700 990 15 9 9
4 900 380 37 6 6
5 200 870 42 3 3
6
7 =A1*B1 =B1*C1 =C1*D1
8 =C4/A2 =D4/B2 =E4/C2
EXCELABSOLUTE REFERENCES
(vs RELATIVE REFERENCES)
EXCEL – Absolute reference ($)
Like glue!!
Locks the row and/or column reference in a cell reference
• Lock the row reference only: Insert $ in front of row reference i.e. A$1
• Lock the column reference only: Insert $ in front of column reference i.e. $A1
• Lock both the row and column reference: Insert $ in front of both the row and column reference i.e. $A$1
ABSOLUTE REFERENCES
Copy down rows
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range B9:B10.
Reference to cell A1 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A1*C4
9 COPY
10 COPY
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range B9:B10.
Reference to cell A1 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4
9
10
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range B9:B10.
Reference to cell A1 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4
9 =A$1*
10
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range B9:B10.
Reference to cell A1 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4
9 =A$1*C5
10
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range B9:B10.
Reference to cell A1 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4
9 =A$1*C5
10 =A$1*
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range B9:B10.
Reference to cell A1 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4
9 =A$1*C5
10 =A$1*C6
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range C9:C10.
Reference to cell B2 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4 "=B4/B2
9 =A$1*C5 COPY
10 =A$1*C6 COPY
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range C9:C10.
Reference to cell B2 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4 "=B4/B$2
9 =A$1*C5
10 =A$1*C6
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range C9:C10.
Reference to cell B2 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4 "=B4/B$2
9 =A$1*C5 "=B5/
10 =A$1*C6
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range C9:C10.
Reference to cell B2 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4 "=B4/B$2
9 =A$1*C5 "=B5/B$2
10 =A$1*C6
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range C9:C10.
Reference to cell B2 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4 "=B4/B$2
9 =A$1*C5 "=B5/B$2
10 =A$1*C6 "=B6/
EXCEL – Absolute reference ($)
Copy down rows
Formula will be copied to range C9:C10.
Reference to cell B2 must not change
A B C1 14% R 15.00 2 33% R 22.00 34 500 130 55 600 220 76 700 990 97
8 =A$1*C4 "=B4/B$2
9 =A$1*C5 "=B5/B$2
10 =A$1*C6 "=B6/B$2
ABSOLUTE REFERENCES
Copy across columns
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B8:C8.
Reference to cell B1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =B1*C5 copy copy
9
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B8:C8.
Reference to cell B1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 copy copy
9
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B8:C8.
Reference to cell B1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*
9
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B8:C8.
Reference to cell B1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5
9
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B8:C8.
Reference to cell B1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5 =$B1*
9
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B8:C8.
Reference to cell B1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5 =$B1*E5
9
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B9:C9.
Reference to cell A2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5 =$B1*E5
9 =B6/A2
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B9:C9.
Reference to cell A2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5 =$B1*E5
9 =B6/$A2
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B9:C9.
Reference to cell A2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5 =$B1*E5
9 =B6/$A2 =C6/
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B9:C9.
Reference to cell A2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5 =$B1*E5
9 =B6/$A2 =C6/$A2
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B9:C9.
Reference to cell A2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5 =$B1*E5
9 =B6/$A2 =C6/$A2 =D6/
EXCEL – Absolute reference ($)
Copy across columns
Formula will be copied to range B9:C9.
Reference to cell A2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$B1*C5 =$B1*D5 =$B1*E5
9 =B6/$A2 =C6/$A2 =D6/$A2
ABSOLUTE REFERENCES
Copy down rows and across columns
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells B9,C10.
Reference to cell A1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =A1*B4
9
10
EXCEL – Absolute reference ($)
Copy down rows and across columns
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9
10
Formula will be copied to cells B9,C10.
Reference to cell A1 must not change
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells B9,C10.
Reference to cell A1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9 =$A$1*
10
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells B9,C10.
Reference to cell A1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9 =$A$1*C5
10
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells B9,C10.
Reference to cell A1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9 =$A$1*C5
10 =$A$1*
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells B9,C10.
Reference to cell A1 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9 =$A$1*C5
10 =$A$1*D6
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells C9,D8.
Reference to cell B2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9 =$A$1*C5
10 =B6/B2 =$A$1*D6
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells C9,D8.
Reference to cell B2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9 =$A$1*C5
10 =B6/$B$2 =$A$1*D6
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells C9,D8.
Reference to cell B2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9 =$A$1*C5 =D5/
10 =B6/$B$2 =$A$1*D6
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells C9,D8.
Reference to cell B2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4
9 =$A$1*C5 =D5/$B$2
10 =B6/$B$2 =$A$1*D6
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells C9,D8.
Reference to cell B2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4 =E4/
9 =$A$1*C5 =D5/$B$2
10 =B6/$B$2 =$A$1*D6
EXCEL – Absolute reference ($)
Copy down rows and across columns
Formula will be copied to cells C9,D8.
Reference to cell B2 must not change
A B C D E1 14% R 15.00 2 33% R 22.00 34 500 130 5 150 515 600 220 7 411 456 700 990 9 153 117
8 =$A$1*B4 =E4/$B$2
9 =$A$1*C5 =D5/$B$2
10 =B6/$B$2 =$A$1*D6
Randomly pick any cell irt fixed cell
HOW TO ANSWER AN EXCEL QUESTION
Write down the structure of the Excel functions you must know out of your head
Example =IF(logical_test,value_if_true,value_if_false)
READ all the provided information and underline/highlight the important information
READ through the spreadsheet (left to right & top to bottom)
If an amount or a percentage(%) is given in a cell you MUST use that cell reference to use it in your formula/function
Writing a formula: Write the formula using amounts and replace the amounts with the applicable cell reference
Negative marks in exam – Refer to MO001 document at the end of Study Unit 5
Have a look at the bigger pictureExamples of exam questions
Some examples of negative marks
• Not starting a formula with = -1
• When referring to a range of cells using a ; to
indicate the range i.e. A10:D10 (correct)
• A10;D10 (-1 mark) -1
• Logical issue in a formula e.g. multiply where
we should divide, subtract where we should
add, etc. -1
• Incorrect use of ( ) or not using it where
necessary or not closing brackets -1
Some examples of negative marks
• Using [ ] instead of ( ) -1
• Using SUM in a formula where it is not
applicable i.e.
• =sum(H18 - H23) -1
• Using a formula when a function is available
i.e. = B5+B6+B7 instead of =sum(B5:B7) -1
• Add an argument which is not applicable to
the end of the function i.e. =Right(A11,1,1) -1
Some examples of negative marks
• Students can use either the , or ; as an
argument separator but it must be used
consistently throughout the question. -1
• Using x instead of * or ÷ instead of / -1
• For marks deducted at a function: The part
in brackets can however not be
EXCEL
QUESTION 1 – TL102 S1 2017
QUESTION 2 – TL102 S1 2016
EXCEL
QUESTION 1
EXCEL QUESTIONSTHE FOLLOWING IS APPLICABLE TO EXCEL QUESTIONS 1 AND 2
Please leave a line open after each individual question.
You used the Excel Help function to obtain the following formula structures which
might come in handy when answering questions 1 and 2:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
=PMT(rate,nper,pv,fv,type)
=FV(rate,nper,pmt,pv,type)
=PV(rate,nper,pmt,fv,type)
EXCEL QUESTION 1Having just completed her Accounting degree, Jane Smith was appointed as Assistant Inventory Clerk
of Read-A-Lot Bookshop (RaL), a medium sized book shop co-owned by Mr and Mrs Pages. The
bookshop prides itself with stocking all the latest and best-selling books across various genres, eg
academic, fiction, non-fiction, sci-fi, and the Pages’ personal favourite romantic novels. RaL is a
registered VAT vendor.
During the interview with Jane, the Pages said: “We have been experiencing some serious challenges
with managing our inventory (stock), and we urgently need someone that will be extremely hands-on
from day one. The first task will be to fix/improve our inventory management system.”
In line with the Pages’ request, Jane’s first assignment was to develop a detailed inventory
management schedule “IMS” (see the Excel spreadsheet on the following page) for monthly inventory
management. The IMS will perform the following functions amongst others.
1) Illustrate the book’s physical location in the store,
2) Indicate book authors,
3) Indicate the book’s source; ie whether the book is “locally” bought/sourced or “Imported”, and
4) Project the total sales income (excluding VAT) generated per book title.
RaL’s current standalone inventory system uses a twelve (12) character stock reference number (SRN)
as inventory code. Jane rather wants to use the accounting information system’s (AIS) integrated
inventory function to manage the inventory. The AIS’s stock code format is however limited to seven
(7) characters and Jane therefore needs to create new SRNs to be used as inventory codes.
EXCEL QUESTION 1 A B C D E F G H
1 Read-A-Lot Bookshop: Inventory Management Schedule (IMS)
2 Authors Table
3 VAT percentage 14% Author code Initials
4 HashimL2 HL
5 Book source and related mark-up margin GuguB012 GB
6 Source Mark-up % GroblerO GO
7 Imported Books (IM) 25% ButlerP1 BP
8 Local Books (LS) 15%
9
10 Book Title Author code
Current SRN Location
Code Author Initials
New SRN Source Code
Mark-up %
11 Book-Worm ButlerP1 2005R4S2BPIM R4S2 BP R4S2*BP IM 25%
12 Silent Killer GuguB012 1998R1S1GBIM R1S1 GB R1S1*GB IM 25%
13 New World GroblerO 2007R2S4GOLS R2S4 GO R2S4*GO LS 15%
14 Love Portion HashimL2 2010R1S4HLIM R1S4 HL R1S4*HL IM 25%
15
16 New SRN Mark-up% Unit Cost Price
Incl. VAT
Unit Cost Price Excl.
VAT
Unit Selling
Price Incl. VAT
Unit Selling
Price Excl. VAT
Units sold Total Sales
Income Excl. VAT
17 R2S4*GO 15.00% R 98.76 R 86.63 R 113.57 R 99.63 14 R 1,394.77
18 R1S4*HL 25.00% R 290.56 R 254.88 R 363.20 R 318.60 38 R 12,106.67
19 R4S2*BP 25.00% R 198.25 R 173.90 R 247.81 R 217.38 22 R 4,782.35
20 R1S1*GB 25.00% R 458.98 R 402.61 R 573.73 R 503.27 9 R 4,529.41
21 Grand Total Sales Income Excl VAT R 22,813
22 Lowest Unit Cost Price Incl VAT R 98.76
23 Highest Unit Selling Price Excl VAT R 503.27
EXCEL QUESTION 1a. Value Added Tax (VAT) percentage is 14% (refer to cell B3).
b. Book source and related mark-up margin (refer to range A5:B8).
Each book’s mark-up margin (mark-up %) is determined based on whether the book is imported
(IM) or locally sourced (LS). Imported books (IM) attract a 25% mark-up margin (refer to cell B7)
and locally sourced books (LS) attract a 15% mark-up margin (refer to cell B8).
c. Authors Table (refer to range G2:H7) indicates the unique 8 character author code and the
corresponding authors’ initials.
d. Current stock reference number (SRN) - (refer to range C11:C14)
The current SRN structure is as follows:
• Characters 1 to 4: Refer to the year the book was published.
• Characters 5 to 8: Refer to the book’s location in the store.
• Characters 9 to 10: Refer to the initials of the author of the book.
• Characters 11 to 12: Indicate the source of the book as either imported (IM) or locally sourced
(LS).
1 2 3 4 5 6 7 8 9 10 11 12
Year published Location Code Author
Initials
Source code
EXCEL QUESTION 1e. The New stock reference number (SRN) (refer to range A16:A20) is created by combining the
location code, an asterisk (the * sign) and the author initials.
The new SRN structure is as follows:
• Characters 1 to 4: Refer to the book’s location in the store.
• Character 5: * (Asterisk sign)
• Characters 6 to 7: Refer to the initials of the author of the book.
f. The Source Code (refer to range G11:G14) is extracted from the current SRN (refer to point d)\
g. The Unit Cost Price Including VAT (refer to range C17:C20) was obtained from the inventory
system and is the cost price (including VAT) of one book.
h. The Unit Selling Price Including VAT (refer to range E17:E20) is based on the applicable Mark-up
% (refer to point b.) and the Unit Cost Price Including VAT.
i. The Total Sales Income Excluding VAT (refer to range H17:H20) is calculated by multiplying the
given units sold for every SRN (refer range G17:G20) and the Unit Selling Price Excluding VAT.
1 2 3 4 5 6 7
Location Code * Author Initials
EXCEL QUESTION 1REQUIRED:
Use the case study information and the spreadsheet to answer the following questions.
Note: Where it is indicated that your formula will be copied to other cells, your formula must
take absolute and relative cell references into account.
5.1. Which spreadsheet formula was entered into cell D11 to extract Book-Worm’s Location Code from
the current Stock Reference Number (SRN) in cell C11?
Note: Your formula will be copied to range D11:D14. (2.5)
5.2. Which spreadsheet formula was entered into cell E11 to obtain Book-Worm’s Author Initials from
the Authors Table in starting in cell G2?
Note: Your formula will be copied to range E12:E14. (3.5)
5.3. Which spreadsheet formula was entered into cell F11 to determine Book-Worm’s New Stock
Reference Number (SRN)?
Note: Your formula will be copied to range F12:F14. (3)
5.4. Which spreadsheet formula was entered into cell G11 to extract Book-Worm’s Source Code from
the current Stock Reference Number (SRN) in cell C11?
Note: Your formula will be copied to range G12:G14. (2)
EXCEL QUESTION 15.5. Which spreadsheet formula was entered into cell H11 to determine the Book-Worm’s Mark-up
margin?
Note: Your formula will be copied to range H12:H14. (3.5)
5.6. Which spreadsheet formula was entered into cell B17 to obtain R2S4*GO’s Mark-up margin from
the table in range F10:H14?
Note: Your formula will be copied to range B18:B20. (3.5)
5.7. Which spreadsheet formula was entered into cell D17 to calculate R2S4*GO’s Unit Cost Price
Excluding VAT?
Note: Your formula will be copied to range D18:D20. (2)
5.8. Which spreadsheet formula was entered into cell E17 to calculate R2S4*GO’s Unit Selling Price
Including VAT?
Note: Your formula will be copied to range E18:E20. (2)
5.9. Which spreadsheet formula was entered into cell H21 to calculate the Grand Total Sales Income
Excluding VAT? The formula should round your answer to zero decimal digits. (3)
5.10.Which spreadsheet formula was entered into cell H22 to determine the lowest unit cost price
including VAT? (1.5)
EXCEL QUESTION 1
5.11.Which spreadsheet formula was entered into cell H23 to determine the highest unit selling price
excluding VAT? (1.5)
[28]
EXCEL
QUESTION 2
EXCEL QUESTION 2You are the financial manager for GlasBot Pty Ltd, a registered VAT vendor. GlasBot manufactures a
range of glass perfume bottles.
GlasBot wants to invest in a new glass bottle-manufacturing machine. The production manager, Mr
Mashile has short-listed four machines. He asked your help in creating a spreadsheet calculating the
monthly instalment amount as well as extracting the downtime days and error margins from the given
information.
Mr Mashile provided you with the following information:
a) The Value Added Tax (VAT) percentage is 14% (refer to cell B3).
b) All the machines will be financed using the same loan finance terms:
a) Interest is compounded monthly at the end of each month at an annual interest rate of 8%
(refer to cell B4).
b) The finance period is five (5) years (refer to cell B5)
c) The purchase price including VAT for each machine (refer to range B15:B19).
d) The residual value of each machine at the end of the five (5) year term (refer to range C15:C19).
e) Each machine has a specific UMDG scale code. A machine’s UMDG scale code will fall within a
specific scale bracket (refer to cell range A7:C13), which indicates the error margin percentage (%)
at which each machine operates. For example a machine with a UMDG code of 120 will fall in the
scale bracket 100 to 199 and the machine will therefore have an error margin of 2.5%.
f) Each machine’s name (refer to range A15:A19) has a specific structure. The machine name
contains the UMDG scale code as well as the downtime code
EXCEL QUESTION 2(g) Each machine has either a B or a C downtime code. The downtime code
indicates the number of downtime days i.e. the number of days per month a
machine will not be operational due to routine maintenance. Two (2) days
downtime is indicated by a B and a C downtime code will be equal to one (1) day
downtime
The machine name structure is as follows:
– Characters 1-3: MAN
– Character 4: Space
– Character 5: X (machine configuration)
– Character 6: - (hyphen/dash)
– Characters 7-9: UMDG scale code (also refer to point e)
– Character 10: Downtime code (also refer to point g)
1 2 3 4 5 6 7 8 9 10
M A N X - 1 1 1 C
A B C D E F G H I
1 GlasBot Pty Ltd - New machine investigation
2
3 VAT 14%
4Annual
interest rate 8%
5Period (years) 5
6
7 UMDG scale
8From To
Error
margin (%)
9 100 199 2.5%
10 200 299 1.5%
11 300 399 2.0%
12 400 499 4.0%
13 500 599 3.0%
14
15Machine
name
Purchase
price
(including
VAT)
Residual
value after
5 years
Monthly
instalment
(including
VAT)
Monthly
instalment
(excluding
VAT)
UMDG
scale
code
Downtim
e code
Error
margin
(%)
Downti
me
days
16 Man X-545C R 4,747,160 R 300,000 R 100,338.21 R 88,015.97 545 C 3.0% 1
17 Man V-339B R 6,327,420 R 0 R 128,297.26 R 112,541.46 339 B 2.0% 2
18 Man X-237C R 7,528,060 R 40,000 R 153,186.30 R 134,373.95 237 C 1.5% 1
19 Man Z-183B R 4,925,000 R 50,000 R 100,541.73 R 88,194.50 183 B 2.5% 2
20
21Largest monthly instalment
excluding VAT R 134,373.95
EXCEL QUESTION 2
EXCEL QUESTION 2Required:
Use the information and spreadsheet provided to answer the following questions:
Note: Where it is indicated that your formula will be copied to other cells, your formula
must take absolute and relative addresses into account, but only where necessary!
1. Which spreadsheet formula was entered into cell D16 to calculate the monthly instalment
amount including VAT for Man X-545C? The formula should return a positive answer.
Note: Your formula will be copied to cells D17:D19. (4.5)
2. Which spreadsheet formula was entered into cell E16 to calculate the monthly instalment
amount excluding VAT for Man X-545C? Note: Your formula will be copied to cells
E17:E19. (2)
3. Which spreadsheet formula was entered into cell F16 to extract the UMDG scale code for
Man X-545C from the machine name? Note: Your formula will be copied to cells
F17:F19 (2.5)
EXCEL QUESTION 2Required cont…:
4. Which spreadsheet formula was entered into cell G16 to extract the downtime code for
Man X-545C from the machine name? Note: Your formula will be copied to cells
G17:G19. (2)
5. Which spreadsheet formula was entered into cell H16 to obtain the error margin % for Man
X-545C from the UMDG scale starting in cell A7? Note: Your formula will be copied to
cells H17:H19. (4)
6. Which spreadsheet formula was entered into cell I16 to calculate the number of downtime
days for Man X-545C? Note: Your formula will be copied to cells I17:I19. (3)
7. Which spreadsheet formula was entered into cell E21 to calculate the largest instalment
excluding VAT for all the machines? (2)
Total [20]
Good luck with your
exam!!
top related