New Castle County Technical Services (NCCTS) provides technical support services for a number of companies in New Castle County, Delaware. You previously used What-If Analysis tools to create one-and two-variable data tables to analyze the impact of various changes in the hourly rate and the hours billed. You also used Goal Seek to determine the optimum billing rate to earn $500 for a 5.5-hour repair. Moving forward, NCCTS would like to expand and upgrade its network operation center. It is estimated this will cost $65,000, which your company would like to finance over a four-year span. Your next task is to utilize the WEEKDAY and SWITCH functions to add functionality to the March Hours worksheet. You will then complete an Amortization schedule to detail payment and interest information for the $65,000 investment financed over four years (48 months) paid monthly with a 5.25% annual percentage rate (APR). a. Open e071r1NCCTS and save it as e07r1NCCTS_LastFirst. b. Display the March Hours worksheet, create a nested function in cell I5 using the SWITCH function and WEEKDAY function to calculate the day of the week that the technical support issue was resolved based on the date in cell H5. In the nested WEEKDAY function, use 1 as the return_type. In the SWITCH function, the value1 argument should be 1, and the Result1 should be Sunday.

c. Copy the function from cell I5 to the range I6:I39.

d. Insert the AVERAGEIF function in cell O5 to determine the average hours logged on Hardware Support.

e. Insert the MAXIFS function in cell O6 to determine the max hours logged on Hardware Support.

f. Insert the SUMIF function in cell O7 to determine the total hours logged on Hardware Support.

g. Display the Amortization worksheet and enter a formula in cell E3 that calculates the periodic interest rate. Note payments will be made on a monthly basis.

h. Enter a formula in cell E4 the calculates the total number of payments. i. Insert the PV in cell E2 to determine present value of the loan amount.

j. Enter a reference to the beginning balance of the loan in cell B8.

k. Enter a reference to the monthly payment in cell C8. Use a mixed reference.

l. Insert the IPMT function in cell D8 to calculate the interest paid for the first payment. Use appropriate mixed and relative cell references.

m. Insert the PPMT function in cell E8 to calculate the principal repayment for the first payment. Use appropriate mixed and relative cell references.

n. Enter a formula in cell F8 to calculate the ending balance of the loan after the first payment.

o. Enter a reference to the ending balance of payment 1 (F8) in cell B9. Copy the formula to the range B10:B55.

p. Select the range C8:F8 and copy the functions and formulas to the range C9:F55.

q. Insert the CUMIPMT function in cell H8 to calculate the total interest due on the first pay-ment. Use appropriate mixed and relative cell references. Copy the function from cell H8 to the range H9:H55.

r. Insert the CUMPRINC function in cell I8 to calculate the total principal repayment after the first payment. Use appropriate mixed and relative cell references. Copy the function from cell I8 to the range I9:I55.

s. Insert the SUM function in cell D56 and cell E56.

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

u. Save and close the workbook. Based on your instructor’s directions, submit e07r1NCCTS_LastFirst.

New Castle County Technical Services (NCCTS) provides technical support services for a number of companies in New Castle County, Delaware. You previously used What-If Analysis tools to create one-and two-variable data tables to analyze the impact of various changes in the hourly rate and the hours billed. You also used Goal Seek to determine the optimum billing rate to earn $500 for a 5.5-hour repair. Moving forward, NCCTS would like to expand and upgrade its network operation center. It is estimated this will cost $65,000, which your company would like to finance over a four-year span. Your next task is to utilize the WEEKDAY and SWITCH functions to add functionality to the March Hours worksheet. You will then complete an Amortization schedule to detail payment and interest information for the $65,000 investment financed over four years (48 months) paid monthly with a 5.25% annual percentage rate (APR). a. Open e071r1NCCTS and save it as e07r1NCCTS_LastFirst. b. Display the March Hours worksheet, create a nested function in cell I5 using the SWITCH function and WEEKDAY function to calculate the day of the week that the technical support issue was resolved based on the date in cell H5. In the nested WEEKDAY function, use 1 as the return_type. In the SWITCH function, the value1 argument should be 1, and the Result1 should be Sunday.

c. Copy the function from cell I5 to the range I6:I39.

d. Insert the AVERAGEIF function in cell O5 to determine the average hours logged on Hardware Support.

e. Insert the MAXIFS function in cell O6 to determine the max hours logged on Hardware Support.

f. Insert the SUMIF function in cell O7 to determine the total hours logged on Hardware Support.

g. Display the Amortization worksheet and enter a formula in cell E3 that calculates the periodic interest rate. Note payments will be made on a monthly basis.

h. Enter a formula in cell E4 the calculates the total number of payments. i. Insert the PV in cell E2 to determine present value of the loan amount.

j. Enter a reference to the beginning balance of the loan in cell B8.

k. Enter a reference to the monthly payment in cell C8. Use a mixed reference.

l. Insert the IPMT function in cell D8 to calculate the interest paid for the first payment. Use appropriate mixed and relative cell references.

m. Insert the PPMT function in cell E8 to calculate the principal repayment for the first payment. Use appropriate mixed and relative cell references.

n. Enter a formula in cell F8 to calculate the ending balance of the loan after the first payment.

o. Enter a reference to the ending balance of payment 1 (F8) in cell B9. Copy the formula to the range B10:B55.

p. Select the range C8:F8 and copy the functions and formulas to the range C9:F55.

q. Insert the CUMIPMT function in cell H8 to calculate the total interest due on the first pay-ment. Use appropriate mixed and relative cell references. Copy the function from cell H8 to the range H9:H55.

r. Insert the CUMPRINC function in cell I8 to calculate the total principal repayment after the first payment. Use appropriate mixed and relative cell references. Copy the function from cell I8 to the range I9:I55.

s. Insert the SUM function in cell D56 and cell E56.

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

u. Save and close the workbook. Based on your instructor’s directions, submit e07r1NCCTS_LastFirst.

New Castle County Technical Services (NCCTS) provides technical support services for a number of companies in New Castle County, Delaware. You previously used What-If Analysis tools to create one-and two-variable data tables to analyze the impact of various changes in the hourly rate and the hours billed. You also used Goal Seek to determine the optimum billing rate to earn $500 for a 5.5-hour repair. Moving forward, NCCTS would like to expand and upgrade its network operation center. It is estimated this will cost $65,000, which your company would like to finance over a four-year span. Your next task is to utilize the WEEKDAY and SWITCH functions to add functionality to the March Hours worksheet. You will then complete an Amortization schedule to detail payment and interest information for the $65,000 investment financed over four years (48 months) paid monthly with a 5.25% annual percentage rate (APR). a. Open e071r1NCCTS and save it as e07r1NCCTS_LastFirst. b. Display the March Hours worksheet, create a nested function in cell I5 using the SWITCH function and WEEKDAY function to calculate the day of the week that the technical support issue was resolved based on the date in cell H5. In the nested WEEKDAY function, use 1 as the return_type. In the SWITCH function, the value1 argument should be 1, and the Result1 should be Sunday.

c. Copy the function from cell I5 to the range I6:I39.

d. Insert the AVERAGEIF function in cell O5 to determine the average hours logged on Hardware Support.

e. Insert the MAXIFS function in cell O6 to determine the max hours logged on Hardware Support.

f. Insert the SUMIF function in cell O7 to determine the total hours logged on Hardware Support.

g. Display the Amortization worksheet and enter a formula in cell E3 that calculates the periodic interest rate. Note payments will be made on a monthly basis.

h. Enter a formula in cell E4 the calculates the total number of payments. i. Insert the PV in cell E2 to determine present value of the loan amount.

j. Enter a reference to the beginning balance of the loan in cell B8.

k. Enter a reference to the monthly payment in cell C8. Use a mixed reference.

l. Insert the IPMT function in cell D8 to calculate the interest paid for the first payment. Use appropriate mixed and relative cell references.

m. Insert the PPMT function in cell E8 to calculate the principal repayment for the first payment. Use appropriate mixed and relative cell references.

n. Enter a formula in cell F8 to calculate the ending balance of the loan after the first payment.

o. Enter a reference to the ending balance of payment 1 (F8) in cell B9. Copy the formula to the range B10:B55.

p. Select the range C8:F8 and copy the functions and formulas to the range C9:F55.

q. Insert the CUMIPMT function in cell H8 to calculate the total interest due on the first pay-ment. Use appropriate mixed and relative cell references. Copy the function from cell H8 to the range H9:H55.

r. Insert the CUMPRINC function in cell I8 to calculate the total principal repayment after the first payment. Use appropriate mixed and relative cell references. Copy the function from cell I8 to the range I9:I55.

s. Insert the SUM function in cell D56 and cell E56.

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

u. Save and close the workbook. Based on your instructor’s directions, submit e07r1NCCTS_LastFirst.

c. Copy the function from cell I5 to the range I6:I39.

c. Copy the function from cell I5 to the range I6:I39.

e. Insert the MAXIFS function in cell O6 to determine the max hours logged on Hardware Support.

e. Insert the MAXIFS function in cell O6 to determine the max hours logged on Hardware Support.

f. Insert the SUMIF function in cell O7 to determine the total hours logged on Hardware Support.

f. Insert the SUMIF function in cell O7 to determine the total hours logged on Hardware Support.

j. Enter a reference to the beginning balance of the loan in cell B8.

j. Enter a reference to the beginning balance of the loan in cell B8.

k. Enter a reference to the monthly payment in cell C8. Use a mixed reference.

k. Enter a reference to the monthly payment in cell C8. Use a mixed reference. k. Enter a reference to the monthly payment in cell C8. Use a mixed reference.

l. Insert the IPMT function in cell D8 to calculate the interest paid for the first payment. Use appropriate mixed and relative cell references. l. Insert the IPMT function in cell D8 to calculate the interest paid for the first payment. Use appropriate mixed and relative cell references.

m. Insert the PPMT function in cell E8 to calculate the principal repayment for the first payment. Use appropriate mixed and relative cell references. m. Insert the PPMT function in cell E8 to calculate the principal repayment for the first payment. Use appropriate mixed and relative cell references.

n. Enter a formula in cell F8 to calculate the ending balance of the loan after the first payment.

n. Enter a formula in cell F8 to calculate the ending balance of the loan after the first payment. n. Enter a formula in cell F8 to calculate the ending balance of the loan after the first payment.

o. Enter a reference to the ending balance of payment 1 (F8) in cell B9. Copy the formula to the range B10:B55. o. Enter a reference to the ending balance of payment 1 (F8) in cell B9. Copy the formula to the range B10:B55.

p. Select the range C8:F8 and copy the functions and formulas to the range C9:F55.

p. Select the range C8:F8 and copy the functions and formulas to the range C9:F55. p. Select the range C8:F8 and copy the functions and formulas to the range C9:F55.

q. Insert the CUMIPMT function in cell H8 to calculate the total interest due on the first pay-ment. Use appropriate mixed and relative cell references. Copy the function from cell H8 to the range H9:H55. q. Insert the CUMIPMT function in cell H8 to calculate the total interest due on the first pay-ment. Use appropriate mixed and relative cell references. Copy the function from cell H8 to the range H9:H55.

r. Insert the CUMPRINC function in cell I8 to calculate the total principal repayment after the first payment. Use appropriate mixed and relative cell references. Copy the function from cell I8 to the range I9:I55. r. Insert the CUMPRINC function in cell I8 to calculate the total principal repayment after the first payment. Use appropriate mixed and relative cell references. Copy the function from cell I8 to the range I9:I55.

s. Insert the SUM function in cell D56 and cell E56.

s. Insert the SUM function in cell D56 and cell E56. s. Insert the SUM function in cell D56 and cell E56.

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

u. Save and close the workbook. Based on your instructor’s directions, submit e07r1NCCTS_LastFirst.

u. Save and close the workbook. Based on your instructor’s directions, submit e07r1NCCTS_LastFirst.u. Save and close the workbook. Based on your instructor’s directions, submit e07r1NCCTS_LastFirst.