EX16_XL_CH02_GRADER_CAP_HW – Inland Jewelers 1.5
Project Description:
You are an account manager for Inland Jewelers, a regional company that makes custom class rings for graduating seniors. Your supervisor requested a workbook to report on new accounts created on payment plans. The report should provide details on total costs to the student as well as payment information. Each ring financed has a base price which can fluctuate based on ring personalization.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
| Step | Instructions | Points Possible |
|---|---|---|
| 1 | Start Excel. Download and open the file named exploring_e02_grader_h1.xlsx. | 0.000 |
| 2 | Insert a function in cell B2 to display the current date from your system. | 10.000 |
| 3 | With cell B2 selected, set the width of column B to AutoFit. | 2.000 |
| 4 | Insert a Lookup function in cell C5 to display the ring cost for the first student. | 14.000 |
| 5 | Copy the formula from cell C5 to the range C6:C11. | 6.000 |
| 6 | Apply Accounting number format to the range C5:C11. | 3.000 |
| 7 | Insert an IF function in cell E5 to calculate the total due. If the student has chosen to personalize the ring, there is an additional charge of 5% located in cell B21 that must be applied; if not, the student only pays the base price. Use appropriate relative and absolute cell references. | 14.000 |
| 8 | Copy the formula from cell E5 to the range E6:E11. | 6.000 |
| 9 | Apply Accounting number format to the range E5:E11. | 3.000 |
| 10 | Insert a function in cell G5 to calculate the first student’s monthly payment, using appropriate relative and absolute cell references. | 14.000 |
| 11 | Copy the formula from cell G5 to the range G6:G11. | 7.000 |
| 12 | Apply Accounting number format to the range G5:G11. | 3.000 |
| 13 | Calculate totals in cells C12, E12, and G12. | 7.000 |
| 14 | Apply Accounting number format to the cells C12, E12, and G12, if necessary. | 2.000 |
| 15 | Set 0.3” left and right margins and ensure the page prints on only one page. | 5.000 |
| 16 | Insert a footer with your name on the left side, the sheet name in the center, and the file name on the right side. | 4.000 |
| 17 | Save the workbook. Close Excel. Submit the file as directed by your instructor. | 0.000 |
| Total Points | 100.000 |
B2 Inland Jewelers 2 Date Name Ring Type Cost Personalized Total 5 Dodson Silver Yes 6 Jones Gold 7 Clarke Titanium No 8 Range Platinum 9 McKinley Platinum 10 Broadnax Gold 11 Burch Titanium Yes 2 Totals 15 Ring Type Cost 400.00 16 Silver 550.00 17 Gold 18 Titanium 700.000 19 Platinum 750.000 Personalizing 5% Surcharge nterest Rate 3% 23 Monthly Payment