Excel Project GO E Capstone 1 ­ Front Range Action Sports 2.3

GO E Capstone 1 - Front Range Action Sports 2.3

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 create a summary sheet, format cells, insert charts, insert sparklines, and create a table in a 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.xlsx.    0.000
2    On the Net Sales worksheet, in the range F4:F8, calculate the annual total sales for each state.    4.000
3    In the range B9:F9, calculate totals.    4.000
4    Using absolute cell references as necessary, in cell G4, construct a formula to calculate the percent that the Colorado Total is of Total Sales, and then fill the formula down through the range G5:G8.    4.000
5    Apply the Total cell style to the range B9:F9.    4.000
6    In cell H4, insert a Line sparkline using the data range B4:E4. Format the sparkline by applying the Sparkline Style Accent 2 (no dark or light). Copy the sparklines down to cell H8.    4.000
7    Insert a 3-D Pie chart using the nonadjacent ranges A4:A8 and G4:G8. Change the chart style to Style 7. Reposition the chart so that the top left corner of the chart is centered in cell A10. Change the width of the chart to 6.5 inches.    4.000
8    Change the chart title to Annual Net Sales by State and add data labels to the chart that display only the Category Name and Percentage positioned in the Center. Change the data labels Font color to Black, Text 1 and apply Bold Italic. Remove the Legend from the chart.    5.000
9    Change the angle of the first slice of the pie to 200, and then explode the red New Mexico slice of the pie by 10%. Change the solid fill color of the orange Colorado slice to Tan, Accent 6.    5.000
10    On the Seattle Inventory worksheet, in cell G14, enter an IF function to determine the items to be ordered. If the Quantity in Stock is less than 50, then the cell should display Order. If not, then the cell should display OK. Copy the function down through cell G19.    4.000
11    Format the range A13:G19 as a table with headers and then apply Table Style Medium 5.    4.000
12    Add Gradient Fill Orange Data Bars to the range A14:A19. Add conditional formatting to the range G14:G19 so that the cells with text that contain the word Order are formatted as Yellow Fill with Dark Yellow Text. Sort the table by Item # from Smallest to Largest.     5.000
13    Filter the table on the Sport column to display only the Skiing types. Display a Total Row in the table, and then sum the Quantity in Stock for Skiing items. Type the result in cell B11. Remove the total row from the table and then clear the Sport filter.    4.000
14    In cell B5, enter a function that will display the average retail price. In cell B6, enter a function that will display the median retail price. In cell B7, enter a function that will calculate the lowest retail price. In cell B8, enter a function that will calculate the highest retail price. Format the range B5:B8 with Accounting Number Format.    8.000
15    Display the Inventory Summary sheet. In cell B4, enter a formula that references cell B4 in the Seattle Inventory sheet so that the Seattle Total Items in Stock displays in cell B4. In cell B5, enter a formula that references cell B5 in the Seattle Inventory sheet so that the Seattle Average Price displays in cell B5. In cells B6, B7, and B8, enter similar formulas to reference the Median Price, Lowest Price, and Highest price in the Seattle Inventory sheet.    5.000
16    In cell C4, enter a formula that references cell B4 in the Denver Inventory sheet so that the Denver Total Items in Stock displays in cell C4. In cells C5, C6, C7, and C8, enter similar formulas to reference the Average Price, Median Price, Lowest Price, and Highest price in the Denver Inventory sheet.    5.000
17    On the Annual Expenses sheet, construct formulas to calculate Totals by Quarter in the range B10:E10 and the Annual Totals in the range F5:F10. Apply the Total cell style to the Totals by Quarter (B10:F10), and then center the column headings (B4:G4) and apply the Heading 4 cell style.    4.000
18    Using absolute cell references as necessary, in cell G5, construct a formula to calculate the % of Total by dividing the Sales Expense Annual Total by the Annual Totals by Quarter. Fill the formula down through the range G6:G9.    4.000
19    Using the data in the nonadjacent ranges B4:E4 and B10:E10, insert a Line with Markers chart. Position the upper left corner of the chart slightly inside the upper left corner of cell A12. Change the height of the chart to 1.75 inches and the width of the chart to 7.25 inches.    3.000
20    Apply chart Style 7. Change the chart title to 2016 Direct Expenses. Edit the Vertical (Value) Axis so that the Minimum is 8000000 and the Major unit 1000000.    7.000
21    Use Format Painter to copy the formatting from cell A2 to A20. In cell B23, enter a formula that references the value in cell F10.    4.000
22    Using absolute cell references as necessary, in cell C23, construct a formula to calculate the projected expenses for 2018 after the Forecasted increase in cell B21 is applied. Fill the formula through cell F23.    5.000
23    Display the Sales Reps sheet. By using Flash Fill and deleting columns as necessary, place the last names in column A, the first names in column B, and then merge and center the title Sales Reps across the two columns.    4.000
24    Ensure that the worksheets are correctly named and placed in the following order in the workbook: Net Sales; Seattle Inventory; Denver Inventory; Inventory Summary; Annual Expenses; Sales Reps. Save the workbook. Close the workbook and then close Excel. Submit the workbook as directed.    0.000
    Total Points    100.000