Solid fill green data bars excel

Excel Sheet Homework

Office 2010 – myitlab:grader – Instructions GO! with Microsoft Excel 2010 Vol. 1 (Capstone)

Ice Creamery Sales

Project Description: In this project, you will work with multiple worksheets and enter formulas and functions to calculate totals, averages, maximum values, and minimum values. Additionally, you will use Goal Seek, format cells, and insert charts, sparklines, and a table into the workbook.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the downloaded Excel file named GO_eV1_Grader_CAP. 0 2 On the Quarterly Sales Summary worksheet, in cell F7, enter a function that will add together the values in the range B7:E7. Copy the function down through cell F13. 4 3 In cell G7, enter a formula that will calculate the projected 2017 sales using the projected sales growth rate in cell B4 and the total 2016 sales in cell F7. Make the reference to cell B4 an absolute reference, and then copy the formula down through cell G13. 4 4 In cell B14, enter a function that will add together the Q1 sales for all products. Copy the function across through cell G14. 4 5 Apply the Total cell style to the range A14:G14. 2 6 In cell H7, insert a line Sparkline using the data range B7:E7. Format the sparkline by applying the style Sparkline Style Colorful #2. Copy the sparklines down to H13. 5 7 Create a Pie in 3-D chart using the nonadjacent ranges A7:A13 and F7:F13. Change the chart style to Style 10. 3 8 Reposition the chart so that the top left corner of the chart is centered in cell A15. Change the height of the chart to 4.8 inches and the width of the chart to 6.5 inches. 3 9 Insert a title above the chart using the text Ice Creamery Sales and add data labels to the chart that display percentages and leader lines in the inside end of each wedge. Move the legend to the bottom of the chart. 6 10 Change the angle of the first slice of the pie to 200, and then explode the green slice of the pie by 17%. Change the solid fill color of the green slice to White, Background 1, Darker 50%. 3 11 On the Bonuses worksheet, in cell J5, enter an IF function that will calculate whether or not an employee receives a bonus. If their total sales are greater than $100,000, then the cell should show Yes. If not, then the cell should show No. Copy the function down through cell J14. 4 12 Format the range A4:J14 as a table with headers using the Table Style Medium 2. 3 13 Add Gradient Fill Orange Data Bars to cells I5:I14. Add conditional formatting to cells J5:J14 so that cells with a value of Yes are formatted as Green Fill with Dark Green Text. Sort the table by Last Name from A to Z. 5 14 Add a total row to the table. Count the number of records in the State column and sum the Total Sales column. Remove the summary field in the Bonus column. 2 15 In cell C17, enter a function that will count the number of salespeople from NY. In cell C18, enter a function that will count the number of salespeople from NJ. In cell C19, enter a function that will count the number of salespeople from CT. 4 16 In cell C20, enter a function that will display the maximum total sales amount. 2 17 In cell C21, enter a function that will display the minimum total sales amount. 2 18 In cell C22, enter a function that will calculate the average total sales for the year. 2 19 On the Sales Projections worksheet, change the value in cell B4 to 12%. Copy the range C7:C14 and paste the values (not the formulas) in cells B18:B25. Change cell B4 to 20% Copy the range C7:C14 and paste the values in cells C18:C25. Change cell B4 to 25% Copy the range C7:C14 and paste the values in cells D18:D25. Change cell B4 to 30% Copy the range C7:C14 and paste the values in cells E18:E25. 4 20 In cell F18, enter a relative cell reference for the value in G7 on the Quarterly Sales Summary worksheet. Copy this cell reference to cells F19:F25. Copy the formatting from the range E18:E25 to cells F18:F25. 5 21 Insert a new sheet to the right of the Sales Projections worksheet. Rename it Sales Analysis and change the tab color to Purple, Accent 4. 3 22 Copy cell A1 from the Sales Projections worksheet and paste it in cell A1 on the Sales Analysis worksheet. In cell A2, enter the text Sales Goal in the cell. Merge and center cells A2:F2. Apply the Heading 2 style to the merged cell. 4 23 Copy the range A6:B14 from the Sales Projections worksheet and paste it into cells A6:B14 of the Sales Analysis worksheet. 9 24 Use format painter to copy the formatting from the range B6:B14 to cells C6:C14. In cell C6, type % of Total Sales. Center and wrap the text in cell C6. Autofit columns A and B, and then change width of column C to 90 pixels. 9 25 In cell C7, enter a formula that will calculate the percent of Ice Cream Bars - Chocolate sales out of the total 2016 sales. Make the reference to cell B14 an absolute reference. Change the format of the cell to percentage with no decimals, and then copy the formula down through cell C13. 5 26 In cell A17, enter the text Ice cream cones Sales Goal in the cell. Copy the range B13:C13 and paste it into B17:C17. In cell C17, use Goal Seek to determine what ice cream cone sales need to be (in cell B17) if they are to be 15% of total sales (in cell C17). Save the results. 3 27 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Quarterly Sales Summary; Bonuses; Sales Projections; Sales Analysis. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points 100

Updated on: 10/08/2010 2 E2010_GOV1_CAP_Instructions.docx