pAIO16_XL_CAP_GRADER - Front Range Action Sports 1.1 Use the Zip file attached below to do this assignment. Make sure each step below is completed. 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_XL_Grader_AIO_CAP.xlsx. 0.000 2 On the Net Sales worksheet, calculate totals in the ranges F4:F8 and B9:F9. Apply the Total cell style to the range B9:F9. 6.000 3 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 apply Percent Style. Fill the formula down through the range G5:G8. 7.000 4 In the range H4:H8, insert Line sparklines to represent the trend of each state across the four quarters. Do not include the totals. Add Markers to the sparklines 6.000 5 Select the range A3:E8, and then use the Recommended Charts command to suggest an appropriate chart. Click the first Clustered Column chart that uses the state names as the category axis. Align the upper left corner of the chart inside the upper left corner of cell A11, and then size the chart so that its lower right corner is slightly inside cell H24. Apply chart Style 7. As the chart title, type Quarterly Net Sales by State 14.000 6 To show the percent that each state contributes to the total sales, select the nonadjacent ranges that represent the state names and state totals. Insert a 3-D Pie chart, and then move the chart to a New sheet. Name the sheet Net Sales by State 6.000 7 Change the Chart Title to Annual Net Sales by State and then change the chart title Font Size to 36. Remove the Legend from the chart, and then add Data Labels that display only the Category Name and Percentage positioned in the Center. Change the data labels Font Size to 14 and apply Bold and Italic. Change the Font color to White, Background 1. 10.000 8 Select the entire pie and display the Format Data Series pane. From the 3-D Format gallery, modify the 3-D options by changing the Top bevel and Bottom bevel to the first bevel option in the first row. Set all of the Width and Height boxes to 512 and then change the Material to the third Standard type—Plastic. 0.000 9 Insert a Custom Footer with the File name in the left section and then save your workbook. 1.000 10 On the Seattle Inventory worksheet, 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. 7.000 11 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. 10.000 12 Format the range A13:G19 as a table with headers and then apply Table Style Medium 5. (NOTE: Depending on the version of Excel installed on your system, the style name may display as Dark Green, Table Style Medium 5). 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. 8.000 13 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 with Bold and Italic. Sort the table by Item # from Smallest to Largest. 6.000 14 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 15 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 16 Display the Sales Reps sheet. By using Flash Fill and deleting columns as necessary, place the last names in column A and the first names in column B. Widen both columns to 100 pixels, and then merge and center the title Sales Reps across the two columns. Apply the Heading 1 cell style to the title. Note, Mac users, select the range A2:A8 and, on the Data tab, click Text to Columns. Use Comma as the delimiter and accept the remaining default options. Change the width of the columns to 100 pixels and continue with the instructions. 6.000 17 Select the Net Sales, Seattle Inventory, Denver Inventory, Inventory Summary, and Sales Reps sheets. Change the Orientation to Landscape, center the worksheets Horizontally, and insert a footer in the left section with the file name. 3.000 18 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Net Sales by State; Net Sales; Seattle Inventory; Denver Inventory; Inventory Summary; Sales Reps. Save the workbook. Close the workbook and then close Excel. Submit the workbook as directed. 0.000 Total Points 100.000

CompetencyIn this project, you will demonstrate your mastery of the following competency:Analyze the essential components of operational processes in relation to efficiency and improvementScenarioYou just started a new position at Mountain Top View Inc. as a manager overseeing all three store locations. John Applewood is one of the owners, and he would like to work with you to develop strategic business goals and improve operational processes. One of the first tasks John asks you to do is to provide recommendations for supporting the company’s business goals. He has asked that you provide the recommendations in the form of a scorecard and to include notes about business goals and initiatives in a follow-up email.Mountain Top View Inc. Company OverviewA few years ago, based on their love of the outdoors, John and Ann Applewood started a small outdoor equipment business called Mountain Top View Inc. The company has expanded so rapidly over the past few years that it is now a publicly traded corporation. There are three stores located across the northeastern United States. Additionally, it has a wide international customer base and sell its products online.Mountain Top View Inc. Mission StatementMountain Top View Inc. provides unparalleled service and quality products that ignite people’s passion and appreciation for nature, the world, and their community.Mountain Top View Inc. VisionOur vision is to lead the outdoor equipment industry in inspiring and equipping people from all walks of life to enjoy the great outdoors through unparalleled service and a diverse array of quality products.Business GoalsJohn has proposed the following business goals for the upcoming year, and he would like your input with regard to possible improvements in operational processes to support these goals.Increase sales by 5% across store locations and online salesRetain existing customers by implementing a rewards programSupport vendor relationships in order to improve supply baseDecrease employee turnover rateDirectionsDevelop an operational processes scorecard that addresses operational processes related to financial, internal, customer, and learning and growth perspectives, and suggest appropriate operational processes to support the company’s business goals.Operational Processes Scorecard: John and Ann would like you to add the business goals to the balanced scorecard. They recognize that the goals are in draft form, so they have asked that you make changes as you see fit, adjusting the goals accordingly.Add the business goals to the scorecard in the Business Goals section and make revisions to business goals to ensure specificity and efficiency.As you revise the goals, consider their ability to measure effectiveness and add context about an operational process that would support the goal.Describe a metric or performance measurement that would effectively evaluate the operational process or processes associated with each business goal. Address the following in your responses:What is a logical measure of success for the business goals identified?How does the metric or performance measurement address the root question of the organizational goal or need?Email to the Owners: John has proposed business goals for the upcoming year, and he would like your input with regard to possible improvements and suggestions based on what is included on the balanced scorecard. In your email, discuss the relationship between the business goals and the operational processes that would support the goals.Provide a brief (one- to two-sentence) explanation to justify the use of the metric or performance measurement used in each perspective of the balanced scorecard (financial, internal, customer, learning and growth). Address the following in your response:How will the metric support the company in meeting the business goal?What specific operational processes does the metric measure?Provide strategies for development of future goals, providing insight into the development and operational processes and expectations that the goals should address. Address the following in your response:What is a good starting point in developing goals? How does this connect to the next steps?How might the mission or vision statement support the development of business goals?Are there any environmental and social responsibility aspects management should consider in the development of future goals?How do these strategies align with the organization’s operational processes and support management in determining appropriate focus areas?Describe common mistakes that are made when designing and reporting metrics to measure operational processes. Address the following in your response:Specifically in the process of designing metrics, what common mistakes can inhibit the metric’s effectiveness?When measuring operational processes, what are common mistakes in determining what to measure, how to measure it, and how to frame the information?What to SubmitEvery project has a deliverable or deliverables, which are the files that must be submitted before your project can be assessed. For this project, you must submit the following:Operational Processes Scorecard- template is attached down below!Highlight specific operational processes and metrics and elaborate on how these will be attained.Email to the OwnersJohn and Ann have asked that you include notes about the business goals and future initiatives in order to elaborate on specific elements included in the scorecard, such as revised business goals and the use of recommended metrics. The email should be a half page or 1 page in length and should be submitted as a Word document. Include the following heading:TO: John and Ann ApplewoodFROM: (Your Name)SUBJECT: Operational Processes Scorecard Follow-UpReference List For each source that you cite, you need to include the author’s name, the publication year, the title of the source, and the location of the source in a References section at the end of your work. Cite all sources appropriately.