PROJECT DESCRIPTION - Canutillo



4934585-889000685800Excel 2013 End of 4th6th Weeks ExamSpringLeaf DesignsFORMATTING, FORMULAS, AND CHARTS Project GoalM Project NameProject Goal00Excel 2013 End of 4th6th Weeks ExamSpringLeaf DesignsFORMATTING, FORMULAS, AND CHARTS Project GoalM Project NameProject GoalPROJECT DESCRIPTIONChristy Chen works at SpringLeaf Designs, a Seattle-based company that makes jewelry and accessories out of recycled materials. Christy has created a workbook to analyze costs and profits by product line. She has also compiled sales forecasts for the next five years. She has asked you to complete the data entry and analysis and to format the worksheets so they have a consistent and professional look. PROJECT STEPSSwitch to the Sheet1 worksheet. Make the following formatting changes:Merge and center the range F3:I3. Apply the Heading 2 cell style to the merged cell and change the fill color to Blue, Accent 5, Lighter 60% (9th column, 3rd row in the Theme Colors palette). Replicate these formatting steps for range C3:E3.Make the fill color of the range B19:I19 White, Background 1, Darker 35% (1st column, 5th row in the Theme Colors palette).Add a Thick Box Border around the range B2:I19. Add a Left Border to the ranges C3:C19 and F3:F19. Make the following formatting changes to the range C4:I4Change the font to Verdana.Change the font color to Blue-Gray, Text 2 (4th Column, 1st row in the Theme Colors palette).Change the font size to 10 pt.Apply Bold formatting. Center align cell contents.Apply text wrapping.Apply Bold formatting to the ranges B19:C19 and E21:E23. Italicize text in the ranges B6:B8, B10:B12, and B15:B16. Add a comment to cell F4 with the text: Average selling price listed. Prices vary slightly by store location and channel. Remove any existing text from the comment box, including the user name, before entering the new comment. In cell F6, type a formula to calculate the average selling price per beaded earring unit. (Hint: The average selling price for the beaded earrings is calculated by dividing the total Sales values in cell C6, by the number of Units Sold value in cell D6.) Copy the formula from cell F6 to the ranges F7:F8, F10:F13, and F15:F18.Apply the Currency Number format to the range F6:F18. (Tip: The Currency Number format should have 2 decimal places displayed by default. If it doesn’t, update the range to display 2 decimal places.) Select the range D6:D18 and use the Quick Analysis tool to create a formula that calculates the total number of units sold. The formula will use the SUM function and should appear in cell D19. Enter a formula in cell E6 to calculate the percentage of total sales accounted for by beaded earrings. The formula will divide beaded earring sales in C6 by total sales in C19. (Hint: Use an absolute reference to the total sales in cell C19, and a relative reference to beaded earring sales in cell C6). Copy the formula from cell E6 to the ranges E7:E8, E10:E13, and E15:E18.Apply the Percentage Number format with no decimal places to the range E6:E18. Based on range I6:I18, enter a formula in cell I21 that uses the MAX function to identify the highest average unit profit across SpringLeaf Design’s product lines. Based on range I6:I18, enter a formula in cell I22 that uses the MIN function to identify the lowest average unit profit across SpringLeaf Design’s product lines. Based on range I6:I18, enter a formula in cell I23 that uses the AVERAGE function to calculate SpringLeaf Design’s average profit per unit across product lines.Add a Thick Box Border around range E21:I23.Use conditional formatting to apply Solid Fill Blue Data Bars to the range I6:I18.Find and replace the misspelled word Cufs with the correctly spelled word CuffsRename Sheet1 Sales Analysis and apply the Green, Accent 6 tab color (10th column, 1st row of the Theme Colors palette) to the sheet tab.Go to the Forecast Data worksheet. Move the contents of cell G10 to cell G9. Fill the range E3:G3 with a number series based on the contents of range C3:D3. (Hint: You can ignore the error message in cells E3:G3). Format the range C4:G9 with the Accounting Number format with no decimal places and use $ as the Symbol. (Hint: Depending on how you complete this action, the number format may appear as Custom instead of Accounting.) Make the following changes to the column widths and row heights:Change the width of the column B to best fit (using AutoFit) its contents.Change the width of columns C through F to 9.0 characters. Change the height of row 2 to 40 pt. Enter a formula in cell C10 using the SUM function to total the values in range C4:C9. Copy the formula from cell C10 to the range D10:G10.Add a Thick Box Border cell border around range B2:H10. Insert a 3-D Pie chart based on the range B4:C9 to graph 2016 Sales by product line. Apply chart Style 7 and enter the text 2016 Sales by Product as the chart title. Reposition the chart so the upper-left corner is in cell B13. Select the range B3:G9 and use the Quick Analysis tool to create a Clustered Column chart showing the sales forecasted through 2020 for SpringLeaf Design’s product line. (Tip: The data should be grouped by product in your chart) Apply chart style Style 8 to the chart and enter the text Sales Forecast as the chart title. Resize and reposition the chart so that the upper-left corner is in cell J2 and the bottom-right corner is in cell P10.Insert a header in the worksheet. Using the Header & Footer Elements, display the worksheet name in the center header section and the current date in the right header section. Hide the gridlines in the worksheet. Go to the Forecast Chart worksheet. Make the following changes to the Stacked Bar chart:Change the data labels to the Center position.Reposition the chart legend to appear in the Right position. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.Final Figure 1: Sales Analysis Worksheet17145004845050Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright ? 2014 Cengage Learning. All Rights Reserved.020000Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright ? 2014 Cengage Learning. All Rights Reserved.Final Figure 2: Forecast Data Worksheet17811754300220Copyright ? 2014 Cengage Learning. All Rights Reserved.020000Copyright ? 2014 Cengage Learning. All Rights Reserved. Final Figure 3: Forecast Chart Worksheet18192754363085Copyright ? 2014 Cengage Learning. All Rights Reserved.020000Copyright ? 2014 Cengage Learning. All Rights Reserved.0422275 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download