Please enable JavaScript.
Coggle requires JavaScript to display documents.
Excel - Week 7 - Chapter 9 - 10 - Coggle Diagram
Excel - Week 7 - Chapter 9 - 10
Fixed scale
For each range, we have different percentages
It is easy to see which category sales fall into in this case
E.g. This is provided
You will need to complete the commission column
You use a fixed scale when you have calculations where you have different ranges
We will need to use the table to determine which percentage I'm using
Set up a Lookup database
True vlookup => Approximate values
Start with the bottom amount in your table
Sliding scale
For sliding scales, we also use the Vlookup
Set up your lookup database
E.g. This is provided
Would have done on paper like this:
Fanie = R6000
0 - 1000 = R1000 @ 1% = R10
1000 - 2000 = R1000 @ 2% = R20
2000 - 3000 = R1000 @ 4% = R40
= R70
3000 - 6000 = R3000 @ 6% = R180
=R250
The value does not simply fall into a single range, you will need to consider all the values below it as well. So it accumulates over all the ranges that fall below that value
No calculate the commission
Time value of money
FV(rate; NPER; PMT; PV; type)
E.g. This is given
PV(rate; NPER; PMT; FV; Type)
FV and PMT must be negative
Calculate
PMT(rate; NPER; PV; FV; Type)
Rate and nper depend on how many payments we make during the year
Type - At the end of the period use 0 and at the beginning of the period use 1
If you have outflows of cash, show as negative
We only work with nominal interest rates
If you have inflows of cash, show positive
Future value function gives you your future value of equal installments or the cash balance after all the payments have been made
Payments are negative
Present value function gives you the current value of equal installments or your capital amount at the start of your loan / savings
Payment function gives you your monthly payment
NPER x PMT = what you would pay towards your loan