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.