Homework answers / question archive / Excel chapter 4 grader project GO16_XL_CH04_GRADER_4G_AS - Warehouse Lookup Project Description: In this project, you will create a named range and use it to set data validation

Excel chapter 4 grader project GO16_XL_CH04_GRADER_4G_AS - Warehouse Lookup Project Description: In this project, you will create a named range and use it to set data validation

MS Excel

Share With

---

Excel chapter 4 grader project

GO16_XL_CH04_GRADER_4G_AS - Warehouse Lookup

Project Description:

In this project, you will create a named range and use it to set data validation. You will use a PMT  function to calculate a value and then use it in a two-variable data table. You will also enter  VLOOKUP functions to return values from a table, and format cells in the workbook. You will also  audit a worksheet and correct errors.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Instructions

1. Start Excel. Download and open the workbook named  go16_xl_ch04_grader_4g_as.xlsx .

2. Click the Warehouse Payment Table sheet. In cell B8, enter a PMT function using  cell B4 divided by  12  as the rate, B3 as the number of payment periods, and B2 as the present value of the loan. Display the result as a positive number.

3. In the range B8:H16, create a two-variable data table. Set B3 as the row input cell,  and B4 as the column input cell.

4. Apply the Accounting number format to cells C9:H16.

5. Select the payment option closest to $10,000 per month for a 120-month loan— cell D14— and then apply the Note cell style.

6. On the Job Information sheet, select the range A4:C11. Sort the range by the Job  Code column in ascending order.

7. Create a range named  Job_Code  using the data in cells A5:A11 on the Job  Information worksheet.

8. On the Staffing Plan worksheet, select the range A9:A18. Display the Data  Validation dialog box, and validate from a List using the source  =Job_Code . From  the newly created Data Validation list, click cell A9, click the arrow, and then click  M-AMG .

9. On the Staffing Plan worksheet, in cell B9, create a VLOOKUP function that will look up the Description of the Job Code in cell A9 using the information A5:C11 in the  Job Information worksheet as the table array. After selecting the table array, be  sure to press F4 to make it absolute. The Description to be looked up is in column 2 of the table array.

10. Copy the VLOOKUP function in B9 down through cell B18.

11. In cell C9, enter  3  as the # of Positions and in cell D9 type  Management  as the  Type.

12. On the Staffing Plan worksheet, in cell E9, insert a VLOOKUP to look up the Salary  of the Job Code in cell A9 by using the information in the Job Information sheet as  the table array; Salary is in column 3 of the table array.

13. Copy the VLOOKUP function in E9 down through cell E18.

14. Add the following staff position, beginning in row 10: Job Code  # of Positions  Type

S-STR           5               Stock

15. Delete the unused rows, sum the Budget Amount, and then apply the Total cell  style.

16. Display the Revenue worksheet and trace the precedents for the formula in cell I5.  Use the Error Checking dialog box to correct the error in the cell so that the  formula is using the Growth Assumption for Bridal Baskets, not for Baby Baskets.

17. Resume error checking. When detected, correct the error in cell M6 so that the  formula references cell L9 instead of L10. Ensure that the reference to L9 is an  absolute reference.

18. Resume error checking. When detected, correct the error in cell F7 by copying the  formula from the cell above. Continue error checking and correct the error in cell J8 by copying the formula from the cell on the left.

19. Use Format Painter to copy the format in cell M5 to M6.

20. In the sheet tab row, right-click any sheet tab, and then click Select All Sheets. Display the Page Setup dialog box, click the Margins tab, and then under Center on page, click Horizontally. Click the Header/Footer tab, click Custom Footer, and the n  in the Left section, insert the File Name. Click OK two times. Ungroup the sheets,  and then for these three worksheets—Warehouse Payment Table, Staffing Plan, and Revenue—set the Orientation to Landscape. Select the Revenue sheet, and on the  Page Layout tab, set the Scale to 90%. Add the tag  New York Revenue  to the  properties.

21. Ensure that the worksheets are correctly named and placed in the following order  in the workbook:  Warehouse Purchase, Warehouse Payment Table, Staffing Plan,  Job Information, Revenue. Save the workbook. Close the workbook and then exit  Excel. Submit the workbook as directed.