As the manager of Reid’s Furniture Store, you track sales transactions by salesperson, department, amount, and payment type. Customers either finance their purchase through your store or pay in full at the time of purchase. You will calculate down payments and balances for all transactions. If a transaction is paid in full, the balance is zero. Customers who finance their transactions must pay off the balance within four years from the transaction date. You want to complete the March transaction log and use conditional functions to summarize key paid-in-full transactions. Next, you will insert financial functions and formulas to complete a loan amortization table and to cal-culate cumulative summaries. Finally, you will insert a map to indicate sales by postal code. Refer to Figure 7.34 as you complete this exercise.

a. Open e07p1Furniture and save it as e07p1Furniture_LastFirst.

b. Click cell C11 and extract the weekday by serial number by completing the following steps: • Click the Formulas tab, click Date & Time in the Function Library group, and then select WEEKDAY. • Type B11 in the Serial_number box, type 1 in the Return_type box, and then click OK. • Copy the function from cell C11 to the range C12:C113.

c. Click cell D11 and switch the weekday numbers for weekday names by completing the follow-ing steps: • Click Logical in the Function Library group and select SWITCH. • Type C11 in the Expression box. • Type 1 in the Value1 box and type E$2 in the Result1 box. • Type 2 in the Default_or_value2 box and type E$3 in the Results 2 box. • Continue typing numbers 3 through 7 in the respective Default_or_value boxes and type E$4 through E$8 in the respective Result boxes. Click OK. • Copy the function from cell D11 to the range D12:D113.

d. Click cell H11 and calculate the down payment for all sales. If a transaction is not financed, the down payment is identical to the amount. If the transaction is financed, the down payment is 10% of the amount purchased. Complete the following steps: • Click Logical in the Function Library and select IF. • Type NOT(F11=”Finance”) in the Logical_test box in the Function Arguments dialog box. • Press Tab and type G11 in the Value_if_true box. • Press Tab and type G11*C$3 to multiply the amount purchased by the down payment per-centage rate in cell C3. Click OK. • Double-click the cell H11 fill handle to copy the formula to the range H12:H113.

e. Click cell I11, type =G11-H11, and press Ctrl+Enter. Copy the formula to the range I12:I113. Notice that paid-in-full transactions show a negative sign (–) instead of a value.

f. Click cell J11 and calculate the date the payment is due by completing the following steps: • Click Logical in the Function Library and select IF. • Type I11 in the Logical_test box in the Function Arguments dialog box. • Click in the Value_if_true box, click the Name Box arrow, select More Functions, type EDATE, click Go, and then click OK. • Type B11 in the Start_date box. • Click in the Months box, type C$4, and then click OK. • Edit the function to look like this: =IF(I11>0,EDATE(B11,C$4),”-“) • Double-click the cell J11 fill handle to copy the formula to the range J12:I113.

g. Click cell J2 and calculate the number of paid-in-full transactions by completing the following steps:

Click More Functions in the Function Library group, point to Statistical, scroll down, and then select COUNTIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, and then click OK. The function indicates that 40 transactions were paid in full.

h. Click cell J3 and calculate the total dollar amount of the paid-in-full transactions by complet-ing the following steps: • Click Math & Trig in the Function Library group, scroll down, and then select SUMIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, type G11:G113 in the Sum_range box, and then click OK. The function indicates that the total amount of paid-in-full transactions is $93,017.

i. Click cell J4 and calculate the average transaction amount for the paid-in-full transactions by completing the following steps: • Click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, type G11:G113 in the Average_range box, and then click OK. The function indicates that the average trans-action for paid-in-full transactions is $2,325.43.

j. Click cell J5 and calculate the average transaction amount for transactions over $4,000 by completing the following steps: • Click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIFS. • Type G11:G113 in the Average_range box, type F11:F113 in the Criteria1_range box, type “Paid in Full” in the Criteria1 box, type G11:G113 in the Criteria_range2 box, type “>4000” in the Criteria2 box, and then click OK. The function indicates that the average transaction for paid-in-full transactions over $4,000 is $6,450.29.

k. Click cell J6. Adapt Step j to use the MAXIFS function. The function returns $11,972.00.

l. Click the March 1 Loans sheet tab, click cell B5, and calculate the future value of the loan payments given compound interest by completing the following steps: • Click Financial in the Function Library group, scroll through the list, and then select FV. • Type E2 in the Rate box, E5 in the Nper box, -B4 in the Pmt box, and then click OK. • Format cell B5 with Accounting Number Format

m. Click cell B8, click Financial in the Function Library group on the Formulas tab, and then select CUMIPMT. Complete the following steps to calculate cumulative interest at the end of each year: • Type $E2 in the Rate box, $E5 in the Nper box, $B3 in the Pv box, 1 in the Start_period box, B7*$E3 in the End_period box, 0 in the Type box, and then click OK. Edit the function by typing – after equal sign. The cumulative interest at the end of the first year is $973.21. • Click the Home tab, click Copy, select the range C8:E8, click the Paste arrow, and then click Formulas. • Enter the CUMPRINC function in cell B9 using the same arguments you used for the CUMIPMT function. Include the – sign after the equal sign and copy the formulas to the range C9:E9.

n. Enter functions and formulas in the amortization table by completing the following steps: • Click cell C12, click Financial in the Function Library group on the Formulas tab, scroll through the list, and then select IPMT. Type E$2 in the Rate box, A12 in the Per box, E$5 in the Nper box, -B$3 in the Pv box, and then click OK. The first payment includes $91.03 in interest. • Click cell D12, click Financial in the Function Library group, scroll through the list, and then select PPMT. Type E$2 in the Rate box, A12 in the Per box, E$5 in the Nper box, -B$3 in the Pv box, and then click OK. The first payment includes $741.06 in principal payment. • Type =B12-D12 in cell E12. • Type =E12 in cell B13. Copy the formula in cell B13 to the range B14:B59. • Select the range C12:E12 and copy the functions and formula to the range C13:E59. • Enter the SUM function in cells C60 and D60. Double underlines should display. • Apply Accounting Number Format to the ranges B12:E12 and C60:D60. • Apply Comma Style to the range B13:E59. • Apply Underline to the range C59:D59.

o. Click the Map sheet tab and insert a map by completing the following steps: • Select the range B2:C6, click the Insert tab, click Maps in the Charts group, and then click Filled Map. • Cut the map chart and paste it in cell A9. • Select the Chart Title and type March Sales by Zip Code.

p. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets.

q. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07p1Furniture_LastFirst.

As the manager of Reid’s Furniture Store, you track sales transactions by salesperson, department, amount, and payment type. Customers either finance their purchase through your store or pay in full at the time of purchase. You will calculate down payments and balances for all transactions. If a transaction is paid in full, the balance is zero. Customers who finance their transactions must pay off the balance within four years from the transaction date. You want to complete the March transaction log and use conditional functions to summarize key paid-in-full transactions. Next, you will insert financial functions and formulas to complete a loan amortization table and to cal-culate cumulative summaries. Finally, you will insert a map to indicate sales by postal code. Refer to Figure 7.34 as you complete this exercise.

a. Open e07p1Furniture and save it as e07p1Furniture_LastFirst.

b. Click cell C11 and extract the weekday by serial number by completing the following steps: • Click the Formulas tab, click Date & Time in the Function Library group, and then select WEEKDAY. • Type B11 in the Serial_number box, type 1 in the Return_type box, and then click OK. • Copy the function from cell C11 to the range C12:C113.

c. Click cell D11 and switch the weekday numbers for weekday names by completing the follow-ing steps: • Click Logical in the Function Library group and select SWITCH. • Type C11 in the Expression box. • Type 1 in the Value1 box and type E$2 in the Result1 box. • Type 2 in the Default_or_value2 box and type E$3 in the Results 2 box. • Continue typing numbers 3 through 7 in the respective Default_or_value boxes and type E$4 through E$8 in the respective Result boxes. Click OK. • Copy the function from cell D11 to the range D12:D113.

d. Click cell H11 and calculate the down payment for all sales. If a transaction is not financed, the down payment is identical to the amount. If the transaction is financed, the down payment is 10% of the amount purchased. Complete the following steps: • Click Logical in the Function Library and select IF. • Type NOT(F11=”Finance”) in the Logical_test box in the Function Arguments dialog box. • Press Tab and type G11 in the Value_if_true box. • Press Tab and type G11*C$3 to multiply the amount purchased by the down payment per-centage rate in cell C3. Click OK. • Double-click the cell H11 fill handle to copy the formula to the range H12:H113.

e. Click cell I11, type =G11-H11, and press Ctrl+Enter. Copy the formula to the range I12:I113. Notice that paid-in-full transactions show a negative sign (–) instead of a value.

f. Click cell J11 and calculate the date the payment is due by completing the following steps: • Click Logical in the Function Library and select IF. • Type I11 in the Logical_test box in the Function Arguments dialog box. • Click in the Value_if_true box, click the Name Box arrow, select More Functions, type EDATE, click Go, and then click OK. • Type B11 in the Start_date box. • Click in the Months box, type C$4, and then click OK. • Edit the function to look like this: =IF(I11>0,EDATE(B11,C$4),”-“) • Double-click the cell J11 fill handle to copy the formula to the range J12:I113.

g. Click cell J2 and calculate the number of paid-in-full transactions by completing the following steps:

Click More Functions in the Function Library group, point to Statistical, scroll down, and then select COUNTIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, and then click OK. The function indicates that 40 transactions were paid in full.

h. Click cell J3 and calculate the total dollar amount of the paid-in-full transactions by complet-ing the following steps: • Click Math & Trig in the Function Library group, scroll down, and then select SUMIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, type G11:G113 in the Sum_range box, and then click OK. The function indicates that the total amount of paid-in-full transactions is $93,017.

i. Click cell J4 and calculate the average transaction amount for the paid-in-full transactions by completing the following steps: • Click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, type G11:G113 in the Average_range box, and then click OK. The function indicates that the average trans-action for paid-in-full transactions is $2,325.43.

j. Click cell J5 and calculate the average transaction amount for transactions over $4,000 by completing the following steps: • Click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIFS. • Type G11:G113 in the Average_range box, type F11:F113 in the Criteria1_range box, type “Paid in Full” in the Criteria1 box, type G11:G113 in the Criteria_range2 box, type “>4000” in the Criteria2 box, and then click OK. The function indicates that the average transaction for paid-in-full transactions over $4,000 is $6,450.29.

k. Click cell J6. Adapt Step j to use the MAXIFS function. The function returns $11,972.00.

l. Click the March 1 Loans sheet tab, click cell B5, and calculate the future value of the loan payments given compound interest by completing the following steps: • Click Financial in the Function Library group, scroll through the list, and then select FV. • Type E2 in the Rate box, E5 in the Nper box, -B4 in the Pmt box, and then click OK. • Format cell B5 with Accounting Number Format

m. Click cell B8, click Financial in the Function Library group on the Formulas tab, and then select CUMIPMT. Complete the following steps to calculate cumulative interest at the end of each year: • Type $E2 in the Rate box, $E5 in the Nper box, $B3 in the Pv box, 1 in the Start_period box, B7*$E3 in the End_period box, 0 in the Type box, and then click OK. Edit the function by typing – after equal sign. The cumulative interest at the end of the first year is $973.21. • Click the Home tab, click Copy, select the range C8:E8, click the Paste arrow, and then click Formulas. • Enter the CUMPRINC function in cell B9 using the same arguments you used for the CUMIPMT function. Include the – sign after the equal sign and copy the formulas to the range C9:E9.

n. Enter functions and formulas in the amortization table by completing the following steps: • Click cell C12, click Financial in the Function Library group on the Formulas tab, scroll through the list, and then select IPMT. Type E$2 in the Rate box, A12 in the Per box, E$5 in the Nper box, -B$3 in the Pv box, and then click OK. The first payment includes $91.03 in interest. • Click cell D12, click Financial in the Function Library group, scroll through the list, and then select PPMT. Type E$2 in the Rate box, A12 in the Per box, E$5 in the Nper box, -B$3 in the Pv box, and then click OK. The first payment includes $741.06 in principal payment. • Type =B12-D12 in cell E12. • Type =E12 in cell B13. Copy the formula in cell B13 to the range B14:B59. • Select the range C12:E12 and copy the functions and formula to the range C13:E59. • Enter the SUM function in cells C60 and D60. Double underlines should display. • Apply Accounting Number Format to the ranges B12:E12 and C60:D60. • Apply Comma Style to the range B13:E59. • Apply Underline to the range C59:D59.

o. Click the Map sheet tab and insert a map by completing the following steps: • Select the range B2:C6, click the Insert tab, click Maps in the Charts group, and then click Filled Map. • Cut the map chart and paste it in cell A9. • Select the Chart Title and type March Sales by Zip Code.

p. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets.

q. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07p1Furniture_LastFirst.

As the manager of Reid’s Furniture Store, you track sales transactions by salesperson, department, amount, and payment type. Customers either finance their purchase through your store or pay in full at the time of purchase. You will calculate down payments and balances for all transactions. If a transaction is paid in full, the balance is zero. Customers who finance their transactions must pay off the balance within four years from the transaction date. You want to complete the March transaction log and use conditional functions to summarize key paid-in-full transactions. Next, you will insert financial functions and formulas to complete a loan amortization table and to cal-culate cumulative summaries. Finally, you will insert a map to indicate sales by postal code. Refer to Figure 7.34 as you complete this exercise.

a. Open e07p1Furniture and save it as e07p1Furniture_LastFirst.

b. Click cell C11 and extract the weekday by serial number by completing the following steps: • Click the Formulas tab, click Date & Time in the Function Library group, and then select WEEKDAY. • Type B11 in the Serial_number box, type 1 in the Return_type box, and then click OK. • Copy the function from cell C11 to the range C12:C113.

c. Click cell D11 and switch the weekday numbers for weekday names by completing the follow-ing steps: • Click Logical in the Function Library group and select SWITCH. • Type C11 in the Expression box. • Type 1 in the Value1 box and type E$2 in the Result1 box. • Type 2 in the Default_or_value2 box and type E$3 in the Results 2 box. • Continue typing numbers 3 through 7 in the respective Default_or_value boxes and type E$4 through E$8 in the respective Result boxes. Click OK. • Copy the function from cell D11 to the range D12:D113.

d. Click cell H11 and calculate the down payment for all sales. If a transaction is not financed, the down payment is identical to the amount. If the transaction is financed, the down payment is 10% of the amount purchased. Complete the following steps: • Click Logical in the Function Library and select IF. • Type NOT(F11=”Finance”) in the Logical_test box in the Function Arguments dialog box. • Press Tab and type G11 in the Value_if_true box. • Press Tab and type G11*C$3 to multiply the amount purchased by the down payment per-centage rate in cell C3. Click OK. • Double-click the cell H11 fill handle to copy the formula to the range H12:H113.

e. Click cell I11, type =G11-H11, and press Ctrl+Enter. Copy the formula to the range I12:I113. Notice that paid-in-full transactions show a negative sign (–) instead of a value.

f. Click cell J11 and calculate the date the payment is due by completing the following steps: • Click Logical in the Function Library and select IF. • Type I11 in the Logical_test box in the Function Arguments dialog box. • Click in the Value_if_true box, click the Name Box arrow, select More Functions, type EDATE, click Go, and then click OK. • Type B11 in the Start_date box. • Click in the Months box, type C$4, and then click OK. • Edit the function to look like this: =IF(I11>0,EDATE(B11,C$4),”-“) • Double-click the cell J11 fill handle to copy the formula to the range J12:I113.

g. Click cell J2 and calculate the number of paid-in-full transactions by completing the following steps:

Click More Functions in the Function Library group, point to Statistical, scroll down, and then select COUNTIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, and then click OK. The function indicates that 40 transactions were paid in full.

h. Click cell J3 and calculate the total dollar amount of the paid-in-full transactions by complet-ing the following steps: • Click Math & Trig in the Function Library group, scroll down, and then select SUMIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, type G11:G113 in the Sum_range box, and then click OK. The function indicates that the total amount of paid-in-full transactions is $93,017.

i. Click cell J4 and calculate the average transaction amount for the paid-in-full transactions by completing the following steps: • Click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIF. • Type F11:F113 in the Range box, type “Paid in Full” in the Criteria box, type G11:G113 in the Average_range box, and then click OK. The function indicates that the average trans-action for paid-in-full transactions is $2,325.43.

j. Click cell J5 and calculate the average transaction amount for transactions over $4,000 by completing the following steps: • Click More Functions in the Function Library group, point to Statistical, and then select AVERAGEIFS. • Type G11:G113 in the Average_range box, type F11:F113 in the Criteria1_range box, type “Paid in Full” in the Criteria1 box, type G11:G113 in the Criteria_range2 box, type “>4000” in the Criteria2 box, and then click OK. The function indicates that the average transaction for paid-in-full transactions over $4,000 is $6,450.29.

k. Click cell J6. Adapt Step j to use the MAXIFS function. The function returns $11,972.00.

l. Click the March 1 Loans sheet tab, click cell B5, and calculate the future value of the loan payments given compound interest by completing the following steps: • Click Financial in the Function Library group, scroll through the list, and then select FV. • Type E2 in the Rate box, E5 in the Nper box, -B4 in the Pmt box, and then click OK. • Format cell B5 with Accounting Number Format

m. Click cell B8, click Financial in the Function Library group on the Formulas tab, and then select CUMIPMT. Complete the following steps to calculate cumulative interest at the end of each year: • Type $E2 in the Rate box, $E5 in the Nper box, $B3 in the Pv box, 1 in the Start_period box, B7*$E3 in the End_period box, 0 in the Type box, and then click OK. Edit the function by typing – after equal sign. The cumulative interest at the end of the first year is $973.21. • Click the Home tab, click Copy, select the range C8:E8, click the Paste arrow, and then click Formulas. • Enter the CUMPRINC function in cell B9 using the same arguments you used for the CUMIPMT function. Include the – sign after the equal sign and copy the formulas to the range C9:E9.

n. Enter functions and formulas in the amortization table by completing the following steps: • Click cell C12, click Financial in the Function Library group on the Formulas tab, scroll through the list, and then select IPMT. Type E$2 in the Rate box, A12 in the Per box, E$5 in the Nper box, -B$3 in the Pv box, and then click OK. The first payment includes $91.03 in interest. • Click cell D12, click Financial in the Function Library group, scroll through the list, and then select PPMT. Type E$2 in the Rate box, A12 in the Per box, E$5 in the Nper box, -B$3 in the Pv box, and then click OK. The first payment includes $741.06 in principal payment. • Type =B12-D12 in cell E12. • Type =E12 in cell B13. Copy the formula in cell B13 to the range B14:B59. • Select the range C12:E12 and copy the functions and formula to the range C13:E59. • Enter the SUM function in cells C60 and D60. Double underlines should display. • Apply Accounting Number Format to the ranges B12:E12 and C60:D60. • Apply Comma Style to the range B13:E59. • Apply Underline to the range C59:D59.

o. Click the Map sheet tab and insert a map by completing the following steps: • Select the range B2:C6, click the Insert tab, click Maps in the Charts group, and then click Filled Map. • Cut the map chart and paste it in cell A9. • Select the Chart Title and type March Sales by Zip Code.

p. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets.

q. Save and close the file. Exit Excel. Based on your instructor’s directions, submit e07p1Furniture_LastFirst.

a. Open e07p1Furniture and save it as e07p1Furniture_LastFirst.

a. Open e07p1Furniture and save it as e07p1Furniture_LastFirst.

k. Click cell J6. Adapt Step j to use the MAXIFS function. The function returns $11,972.00.

k. Click cell J6. Adapt Step j to use the MAXIFS function. The function returns $11,972.00.