SK16_XL_VOL1_GRADER_CAP_HW – Revenue 1.5

Project Description:

In this project, you will unhide a worksheet, work with grouped worksheets, insert and rename worksheets, insert summary, logical, date, and statistical functions, and refer to cells in other worksheets. Additionally, you will apply conditional formatting, create and modify charts, and create, sort, and filter Excel tables.

Start   Excel 2016. Download and open the file sk16_xl_ch1-4_grader_cap_hw.xlsx. Save the file as Last_First_sk16_xl_ch1-4_grader_cap_hw.xlsx.

Group the worksheets and change   the width of B:E to 13.00. Change the height of row 4 to 15.00.

With the worksheets grouped,   insert a function in E5:E14 to sum the Quarter Total. Insert a function in   B15:E15 to total the Budget for each location. Apply the Total cell style to   the Total Budget results. In E6:E14 apply Comma [0] cell style.

With the worksheets grouped,   insert a function in B17 to calculate the Average North budget item. In cell   B18, insert a function to calculate the highest North budget item. In cell   B19, insert a function to calculate the lowest North budget item. AutoFill the   range B17:B19 through column D.

Ungroup and insert a new   worksheet. Rename the worksheet Summary, and then change the tab color to Gold, Accent 6. Move the   worksheet to the first position in the workbook.

Copy the range A1:E4 from any of   the quarter worksheets. Paste the range to the Summary worksheet in A1:E4   using paste option Keep Source Column Widths.

On the Summary worksheet, change   the subtitle of cell A2 to Annual Budget. Change the label in cell A4 to Quarter. In cell A5, type 1st Quarter and the AutoFill down through   cell A8. Type Annual Total in cell A9.

On the Summary worksheet, enter   a formula in cell B5 setting the cell equal to cell B15 in the First Quarter   worksheet. Enter formulas in B6:B8 setting the cells equal to the North   totals from the Second, Third, and Fourth Quarter worksheets. AutoFill the   range B5:B8 through column E.

On the Summary worksheet, insert   a function in the range B9:E9 to calculate the column totals. Apply the Comma   [0] cell style to B6:E8, and then apply the Total cell style to the range   B9:E9.

On the Summary worksheet, in   cell A11, type Bonus if less than In cell A12, type 1100000 and then apply the Accounting number format   with 0 decimal places. Select the range A11:A12, and then apply Outside   Borders.

On the Summary worksheet, in   cell B11, insert the IF function. For the logical test, check whether the   North total is less than the value in cell A12. If the logical test is true, 500 should display, and if the   logical test is false, 50 should display. In the function, use an absolute cell reference   when referring to cell A12.

On the Summary worksheet, in   cell B11, apply the Currency [0] cell style, and then AutoFill cell B11   through D11.

On the summary worksheet, select   the range B5:D8, and then insert the default Data Bars conditional format.

On the Summary worksheet, in   cell A16, insert the TODAY function. Format the date with the March 14, 2012   date format.

On the Summary worksheet in the   range F5:F9, insert column Sparklines for the range B5:D9. Show the Low Point   and apply Sparkline Style Colorful #2.

Unhide the Last Year worksheet.   Copy the Annual Budget shape, and then paste the shape in the Summary   worksheet. Move and resize the shape to approximately the range A19:E23.

On the Last Year worksheet,   format the range A4:E14 as a table with headers using the Table Style Light   2. Add a Total row, and then apply Sum to B15:D15. Sort the Budget item   column in ascending order. Apply the Currency [0] cell style to the Total   row.

Hide the Last Year worksheet.

On the First Quarter Worksheet,   click cell A1. Find and replace the four occurrences of Qtr with Quarter in the worksheet headings (Hint: on the Replace tab of the Find   and Replace dialog box, select Within: Workbook, if necessary).

On the Fourth Quarter worksheet,   show the formulas in the worksheet. Set the width of the worksheet to print   on 1 page. Set rows 1:4 to repeat as titles when printing. Group the   worksheets.

With the worksheets grouped,   check and correct any spelling errors. Add the filename to the left footer   and the sheet name to the right footer. Return to Normal view, and select   cell A1. Ungroup the worksheets.

On the Summary worksheet, insert   a 3-D Pie chart using the non-adjacent ranges A4:A8 and E4:E8. Move the pie   chart to a new sheet and rename as Budget Chart.

Format the chart using Layout 1,   and Chart Style 8. Revise the title to Aspen Falls Annual Budget. Apply font size 14 to the data   labels.

Verify the worksheets are in   this order Budget Chart, Summary, First Quarter, Second Quarter, Third   Quarter, and Fourth Quarter. Save the file, and Close Excel. Submit the file   as directed.