Ch 3 Independent Project 3-4

For this project, you create a column chart to graph profit margins for Classic Gardens and Landscapes. You also create a line chart to illustrate a break-even point in units for new catalog items.

[Student Learning Outcomes 3.1, 3.2, 3.3, 3.4, 3.6]

File Needed: ClassicGardens-03.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-ClassicGardens-03.xlsx

Skills Covered in This Project

• Create a chart object.
• Size and position a chart object.
• Edit and format chart elements.
• Edit the source data for a chart.
• Build a line chart object.
• Edit axis labels.
• Position the legend.
• Add and format gridlines in a chart.

Steps to complete This Project

Mark the steps as checked when you complete them.

1. Open the ClassicGardens-03 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Use the Quick Analysis tool to create a Clustered Column chart object for cells A4:G9.
3. Move the chart object so that its top-left corner is at cell A12. Size the bottom of the chart to reach cell H30.
4. Edit the chart title to display CGL Gross Margin in Dollars on the first line. On the second line, type By Department. (In the Formula bar, press Alt+Enter to start a new line.)
5. Apply chart Style 14 to the chart.
6. Apply a 1 pt Black, Text 1 (second column) outline to the chart area.
7. Display the primary minor horizontal axis gridlines. Format the major value axis gridlines with a Black, Text 1 outline color. Do not change the color for the minor gridlines. (The Chart Elements drop-down list identifies the gridlines as Vertical (Value) Axis because they track values on the vertical axis.)
8. Resize the data range to remove the Design Consulting data series from the chart (Figure 3-84).Figure 3-84 Dragging the resize pointer
9. Select cell A1 and click the BreakEven sheet tab.

1. 2. Review break-even formulas and build the data source for a line chart.
      1. Select cell B8. The margin per unit is calculated by subtracting the variable cost from the sales price.
      2. Select cell B10. The formula divides the fixed cost by the sales price minus the variable cost (margin per unit). The results are rounded to show no decimal places.
      3. Review the references and formulas in row 15. Build a table for the chart by copying the formulas in row 15 to the blank rows below it up to and including row 24.
      4. Select cell B5 and type 200. Verify that at this price, the break-even point is 200 units. This is the level at which the profit covers all costs.
   3. Create a line chart.
      1. Select cells B14:C24 and E14:F24.
      2. Create a 2-D Line chart with markers for each line.
      3. Position the chart object to start at cell A26 and to reach cell H45.
   4. Edit the source data.
      1. Click the Select Data button [Chart Design tab, Data group].
      2. Click Edit in the Horizontal (Category) Axis Labels area (Figure 3-85). Excel has determined a scale for the category labels, but you can override those results.

1. Select cells B15:B24 for the Axis label range and click OK.
2. Deselect the # Sold box in the Legend Entries (Series) area and click OK (Figure 3-86). These are category labels and should not be plotted as a line.

1. Edit the chart title to display Break Even in Units.
2. Display and format chart elements.
   1. Use the Chart Elements quick button to position the legend at the top.
   2. Display the primary major vertical gridlines.
   3. Format both vertical and horizontal gridlines with Black, Text 1, Lighter 50% for the Shape Outline.
   4. Apply a 1 pt Black, Text 1 outline for the chart area.
   5. Apply an Offset: Bottom Right shadow effect to the plot area.
3. Change the sales price to $225 (cell B5).
4. Complete worksheet formatting.
   1. Select cells A1:A3 and change font size to 16.
   2. Select cells A1:H3 and center the cells across the selection.
   3. Apply a bottom border to cells A3:H3.
   4. Select cell A1.
   5. Set the page for landscape orientation, center the page horizontally, and scale it to fit one page.
5. Save and close the workbook (Figure 3-87).