Independent Project 6-4

Blue Lake Sporting Goods is analyzing the future value of energy savings from solar panels. They also plan to calculate statistics related to inventory using Database and other functions.

[Student Learning Outcomes 6.2, 6.3, 6.4, 6.6, 6.7, 6.8]

File Needed: BlueLake-06.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 6-4.xlsx

Skills Covered in This Project

• Calculate the future value of savings.
• Use the SWITCH function.
• Calculate the standard deviation for product sales.
• Use RANK.EQ to rank sales for a product.
• Use the FILTER function to extract data.
• Nest the SORT and UNIQUE functions to display data.
• Use a 24-hour clock to calculate time passed.
• Evaluate a formula for understanding.

1. Open the BlueLake-06 workbook from your student data files and save it as [your initials] Excel 6-4.
2. Determine the future value of energy savings.
   1. Select cell C8 on the Solar Install sheet and start an FV formula.
   2. Select cell C5 for the Rate argument and divide by 12 to use a monthly rate. Because the savings are monthly, the rate must reflect the same time division.
   3. Click the Nper box, select cell C6, and multiply by 12 for monthly savings.
   4. Click the Pmt box and select the payment cell (Figure 6-103).
   5. Click OK. The future value of savings is $87,213, well worth the $25,000 setup cost.
3. Use the SWITCH function to display department codes.
   1. Click the Inventory sheet tab and select cell B5. The department codes display in column H.
   2. Start a SWITCH function (Logical category) and select cell C5 as the Expression to be switched.
   3. Click the Value1 box, select cell G5, and press F4 (FN+F4) to make the reference absolute.
   4. Click the Result1 box, select cell H5, and make the reference absolute.
   5. Click the Default_or_value2 box, select cell G6, and make the reference absolute.
   6. Click the Result2 box, select cell H6, and make the reference absolute (Figure 6-104).
   7. Complete the arguments for the remaining two departments.
   8. For the Default_or_value5 argument, type No match.
   9. Copy the formula and preserve the bottom border.
   10. Increase the indent one time for the new codes.
   11. Select cell C14 and type Running.
   12. Select and hide column C.
   13. Change the font size for cell A1 to 14 (Figure 6-105).
4. Calculate the difference between estimated and actual sales.
   1. Click the ThirdQtr sheet tab and select cell F6.
   2. Build a formula to subtract cell D6 from cell E6. The first result is −4 because the company sold fewer than expected.
   3. Select cell F6 and click the Formula bar.
   4. Click after the equals sign (=) in the Formular bar and type ab to display Formula AutoComplete (Figure 6-106).
   5. Press Tab to insert the ABS function and press Enter. The absolute value is the number without the minus sign.
   6. Select cell F6 again. Excel completed the closing parenthesis.
   7. Copy the formula to row 28. Absolute values are necessary to calculate the standard deviation in the next step.

1. Calculate the standard deviation and the rank.
   1. Click cell F30.
   2. Use STDEV.P with cells F6:F28 to determine the deviation.
   3. Use RANK.EQ in cell F32 to determine the rank in actual sales for the Louisville Slugger.
2. Use FILTER to display department products.
   1. Select cell J5 and type winter sports.
   2. Select cell I6 and start a FILTER function (Lookup & Reference category).
   3. Select cells C6:E28 as the Array argument to display three columns in the result.
   4. Click the Include argument box and select cells B6:B28.
   5. Type = and select cell J5. The argument will filter the data in column B with the name in cell J5 as criteria (Figure 6-107).
   6. Click OK. The results array includes four items.
   7. Select cell B15, type wi, and press Enter to accept the AutoComplete suggestion. The FILTER results array now displays five items.
3. Nest SORT and UNIQUE functions to display department names.
   1. Select cell M6.
   2. Click the Lookup & Reference button [Formulas tab, Function Library group] and choose SORT.
   3. Nest a UNIQUE function as the Array argument for SORT.
   4. Select cells B6:B28 as the Array argument for UNIQUE.
   5. Click SORT in the Formula bar to return to its Function Arguments dialog box (Figure 6-108).
   6. Click OK. The results array includes eight department names.
4. Calculate processing time.
   1. Click the OrderTimes sheet tab and select cell C5.
   2. Open the Format Cells dialog box and display the Number tab. The times are formatted to display a 12-hour clock represented by 1:30 PM in the Type list. Close the dialog box.
   3. Select cell E5 and note that it is formatted as General. (You use the formula in cell F5 later in this project.)
   4. Type =( to start the formula and click cell D5.
   5. Type − to subtract, click cell C5, and type ), the closing parenthesis.
   6. Type *24 to convert the results to hours. Excel calculates time using a 24-hour clock and shows results as a fraction of a day unless you do this multiplication (Figure 6-109).
   7. Press Enter. The results format as a time.
   8. Format the results as General. The result is 2.5 hours.
   9. Copy the formula to row 8. The result for time that goes past midnight in cell E8 is wrong.

1. Evaluate a formula to learn how Excel performs times arithmetic.
   1. Select cell F5. The logical_test for the IF formula checks if the time in cell D5 is less than the time in cell C5. If the result is TRUE, the time in cell D5 is increased by 1 because it is one day later. If cell D5 is greater than cell C5 (the result is FALSE), the value in cell D5 is used in the subtraction. Cell C5 is subtracted from the results of the IF formula.
   2. Open the Format Cells dialog box and choose 13:30 in the Time category to format cell F5 using a 24-hour clock. The result displays as 2:30 for two hours and thirty minutes.
   3. Confirm that cell F5 is selected.
   4. Click the Evaluate Formula button [Formulas tab, Function Library group]. The first evaluation is cell D5, underlined in the Evaluation box.
   5. Click Step In. The cell evaluates to 12:00 PM as expected.
   6. Click Step Out. The next evaluation is cell C5. You need not step in to see that it is 9:30 AM.
   7. Click Evaluate to display values for the expression D5<C5. The values reflect where the time fits in the 24-hour day. Cell D5 is half the day (0.5) and cell C5 is 0.3958333… of the day (Figure 6-110).
   8. Click Evaluate again to see the result FALSE. This means the value in cell D5 will be used in the subtraction and 1 will not be added for the next day.
   9. Click Evaluate again. The next step is to subtract the value in cell C5.
   10. Click Evaluate two times to see the result 2:30.
   11. Click Close.
2. Copy and evaluate the formula for times past midnight.
   1. Move the formula in cell F5 to cell E5 and replace your first formula.
   2. Copy the formula to cells E6:E8.
   3. Select cell E8 and click the Evaluate Formula button [Formulas tab, Function Library group].
   4. Click Evaluate three times to reach TRUE as the result. The value_if_true argument adds 1 to the value in cell D8.
   5. Click Evaluate two times to see that 1 is added to the value for cell D8. The value represents a fractional part of a 24-day (Figure 6-111).
   6. Click Evaluate three times to reach the result of 3:30, three hours and thirty minutes.
   7. Close the dialog box.
   8. Select cell E8 and copy the formula to row 33. Fix borders as needed.
3. Save and close the workbook.