Step 1
note it asks for today's date. they want you to use the TODAY function, not the DATE function.
Step 4 + 5
note that if you get step 4 wrong, you'll lose points for step 5.
our goal is to display the COST for each CUSTOMER based on RING TYPE. if we translate this into VLOOKUP syntax, we get:
LOOKUP VALUE = RING TYPE
ARRAY = COSTS
COLUMN = (see below)
let's look at each argument (part of the syntax of the formula).
1. LOOKUP VALUE: B5:B11 show the ring types for each customer. so, we want to start with B5. we also want it to be a normal relative reference so the reference can move down the column for each customer. this lookup value (ring type) will be the unit of comparison found in the array.
2. ARRAY: A16:B19 show the costs for each ring type. we do NOT want these references to change/move when we paste the formula. therefore, we must make the array absolute using $ before both column letter and row number.
3. COLUMN: we want to display the cost. cost is in column 2 of the array.
therefore, =VLOOKUP(B5,$A$16:$B$19,2)
Step 7
we're trying to find out the TOTAL COST. in order to do this, we need to know if the CUSTOMER chose to PERSONALIZE their ring. IF they did, we need to add that to their basic ring cost. IF they did NOT, we want to display the RING COST. if we translate this into IF function syntax:
LOGICAL TEST: PERSONALIZATION YES/NO
VALUE IF TRUE (if the PERSONALIZATION says YES): RING COST + PERSONALIZATION SURCHARGE (5%)
VALUE IF FALSE (if the PERSONALIZATION says NO): RING COST
let's look at each argument.
1. LOGICAL TEST: whether the customer chose personalization or not is shown in column D, so we'll start with D5. the choice is shown with text values "yes" and "no" so that's what we're checking. does D5 = "yes"?
2. IF TRUE: we will show the ring cost, which is shown in column C after step 4 (so the first one is C5). then we need to add the personalization charge to the ring cost. the personalization surcharge is in B21. we don't want this reference to move at all, so it needs to be absolute. because the surcharge is a percentage, we need to multiply it by the ring cost to find amount (this is the same as adding tax). this looks like %*cost. then we add that to our base ring cost.
3. IF FALSE: we only show the ring cost in column C.
therefore, =IF(D5="yes",C5+C5*$B$21,C5)
Step 10
a payment function is used to calculate the amounts for payments at regular intervals. for this assignment, we're trying to figure out a MONTHLY payment amount for each CUSTOMER based on their TOTAL COST. in PMT syntax:
RATE: the INTEREST rate, usually an absolute reference, divided by the total pay periods (MONTHLY)
NPER: n = number, so this means number of payments per period, which in our case is MONTHLY for a given amount of YEARS
PRESENT VALUE (PV): how much they owe, the TOTAL COST
(FV): leave it blank - this is only needed if the person isn't paying the total amount
(type): just leave it blank
1. RATE: the question gives it to us: B22. it's a standard unchanging value so needs to be absolute so it doesn't move around. because it's an annual percentage, we need to divide by 12 (for each month).
2. NPER: amount of payments annually (12) multiplied by the years given in column F.
3. PRESENT VALUE: total cost shown as negative because they owe the money. if you don't make this negative, you will get a negative result implying you owe your customers money!
therefore: =PMT($B$22/12,F5*12,-E5)
Comments