The following activity introduces the concept of identifying and isolating problem parameters in your calculations. If you have access to the program, you should do the activities as you read. This will enhance your understanding of each step.
These exercises build on the Example1 worksheet created in Lessons 1, 2, and 3.
Let's suppose we wish to add an additional row of data for our regional sales summary. This row will contain the total sales figures (for each region and the grand total) adjusted for commissions paid on the sales made. We'll further assume that the current commission scheme calls for a 5% commission rate on all sales, but that this commission rate may be adjusted upward later after the yearly sales total is considered.Since this rate is likely to change it will be convenient to isolate it in our worksheet model rather than bury its actual value within formulas. If we were to put the actual value in a variety of formulas, then we'd have to adjust each formula once the rate changes. But if we isolate the rate in a cell and refer to it symbolically in our formulas, we only have to change its value once when it changes! The worksheet then makes all the necessary adjustments in the calculations for us automatically.
Modify the Example1 worksheet by adding the documentation for the new row of adjusted figures (note that this text is in two rows -- cells A15 and A16) and by adding a cell (and documentation) for the sales commission rate. These changes are illustrated in the figure below.
Now we can easily develop a formula for the adjusted sales figure for Region 1 by employing the commission rate and the previous sales total. The figure below illustrates. Make sure you understand the logic behind the formula before entering it in your worksheet in cell B15 as shown.
Once we enter our new formula, the calculation for the adjusted sales in Region 1 is displayed. We can check that this is correct (it should equal the previous sales figure less 5%). Since the calculation is correct, we can now replicate the formula to cells C15, D15, and E15. The figure shows the worksheet in preparation for that replication.
Once the formula is replicated we should check the other adjusted sales figures. Oops! The sales figures for cells C15, D15, and E15 are identical to the previous sales figures in cells C10, D10, and E10.What went wrong? Click on cell C15 to examine the replicated formula and you will discover the error -- see the figure below. The commission rate "variable" is now cell D18. This occurs because this cell reference was moved one to the right (just as the other references were) when we replicated the formula one cell to the right. But cell D18 contains no data -- hence the spreadsheet assigns it the value 0, which explains why no commission was subtracted.
How can we correct this error? We want the reference to the commission rate to remain at cell C18 when we replicate the formula in cell B15. To accomplish this we must override the default relative addressing mode in Excel. We do this by designating the cell and row values in our formula with a preceding $. The figure below shows this adjustment being made to the formula in cell B15. The resulting cell reference $C$18 is called an absolute address and will anchor this cell to remain fixed in any replication of the formula.
Once we change the cell reference for the commission rate to an absolute address and replicate the formula once more we will get the correct results. The figure below illustrates. If your figures do not match those shown, check your work for errors.
Note: We can change just one of the row or column addresses to be absolute if we choose. In this case, it would have sufficed to make only the column address absolute for the commission rate. In other words, we could have used the address $C18, leaving the row address relative. This works here because we do not replicate the formula to another row, hence there is no change in the row reference in the replicated formulas anyway. While this is acceptable, it is usually a good idea to go ahead and make both the row and column addresses absolute for problem parameters. Then we are protected if we redesign the worksheet later and make replications in a different way than we originally planned.
In this exercise you will will modify the previous exercise (in Lesson 3) you did for computing car loan payments. Recall in that exercise that we computed payments for a fixed loan amount ($10,000) and interest rate (10%) while varying the loan term. A better (more general) worksheet would allow us also to vary the loan amount and interest rate. That's what you'll do in this exercise.Begin by changing the worksheet title documentation (taking out the reference to the loan amount and interest rate). Next add cells to contain the loan amount and interest rate as two problem parameters. The figure below shows all these changes. We've also inserted a column (new column C) to open up some space in the modified worksheet. To do this, click on old column C's name in the column label row and access the Column command in the Insert menu.
To complete the exercise, modify the formula (arguments in the PMT function) in cell D6 to use the problem parameters (loan amount and interest rate) symbolically by cell reference instead of as explicit values. Then you should be able to use the worksheet to compute loan payments for a variety of situations.Check your calculations against the ones shown in the figure below for a $12,000 loan at interest rate 9%. If there is a discrepancy, find your error and correct it.
*** End of Lesson 4 ***
