Homework 5: Classic Gardens & Landscapes | ISMG 2050: Business Problem Solving Tools

Key Topics & Core Concepts

Chart Objects & Quick Analysis: Creating Clustered Column charts and 2-D Line charts with markers directly from worksheet ranges.
Chart Formatting & Styling: Applying predefined chart styles, multi-line titles with line breaks, custom outlines, and plot area shadow effects.
Data Source & Series Management: Removing unwanted data series by dragging resize handles and overriding default horizontal category axis label ranges.
Gridline & Legend Customization: Displaying primary minor and major axis gridlines, formatting outline colors, and repositioning chart legends.
Break-Even Sensitivity & Page Setup: Testing sales price sensitivity on break-even volume, centering across selections, and scaling print layouts to fit one page in landscape orientation.

Assignment Overview & Deliverables

In this project, you create a column chart to graph profit margins for Classic Gardens and Landscapes across departments and months. You will also create a multi-series line chart with markers to illustrate the break-even point in units for newly introduced catalog items.

Starter File: Download ClassicGardens-03.xlsx
Completed Project File Name: [your initials] ClassicGardensLandscapes.xlsx
Target Worksheets: Active Sheet / Gross Margin and BreakEven

Step-by-Step Instructions

Step 1: Open Starter & Build Departmental Column Chart

Open the ClassicGardens-03.xlsx workbook and save it as [your initials] ClassicGardensLandscapes.xlsx.
Use the Quick Analysis tool to create a Clustered Column chart object for cells A4:G9.
Move the chart object so that its top-left corner is at cell A12. Size the bottom of the chart to reach cell H30.
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.)
Apply chart Style 14 to the chart.
Apply a 1 pt Black, Text 1 (second column) outline to the chart area.
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.)
Resize the data range directly on the sheet to remove the Design Consulting data series from the chart.

Step 2: Review Break-Even Formulas & Build Data Source Table

Select cell A1 and click the BreakEven sheet tab.
Select cell B8. Review how margin per unit is calculated by subtracting the variable cost from the sales price.
Select cell B10. Review the formula dividing the fixed cost by the sales price minus the variable cost (margin per unit), rounded to show no decimal places.
Review the references and formulas in row 15. Build a table for the chart by copying the formulas in row 15 (cells C15:G15) down to the blank rows below it up to and including row 24.
Select cell B5 and type 200. Verify that at this price, the break-even point is 200 units (the level at which profit covers all costs).

Step 3: Construct the Break-Even Line Chart

Select cells B14:C24, hold Ctrl, and select cells E14:F24.
Create a 2-D Line chart with markers for each line.
Position the chart object to start at cell A26 and to reach cell H45.
Click the Select Data button (located on the Chart Design tab, in the Data group).
Click Edit in the Horizontal (Category) Axis Labels area.
Select cells B15:B24 for the Axis label range and click OK.
Deselect the # Sold box in the Legend Entries (Series) area and click OK (these are category labels and should not be plotted as a line).
Edit the chart title to display Break Even in Units.

Step 4: Display & Format Chart Elements

Use the Chart Elements quick button to position the legend at the top.
Display the primary major vertical gridlines.
Format both vertical and horizontal gridlines with Black, Text 1, Lighter 50% for the Shape Outline.
Apply a 1 pt Black, Text 1 outline for the chart area.
Apply an Offset: Bottom Right shadow effect to the plot area.
Change the sales price in cell B5 to $225.

Step 5: Complete Worksheet Formatting & Page Setup

Select cells A1:A3 and change the font size to 16.
Select cells A1:H3 and center the cells across the selection (via Format Cells > Alignment > Center Across Selection).
Apply a bottom border to cells A3:H3.
Select cell A1.
Set the page for Landscape orientation, center the page Horizontally, and scale it to Fit to 1 page.
Save and close the completed workbook as [your initials] ClassicGardensLandscapes.xlsx.
Submit your finalized workbook directly to Canvas before the cutoff date.

Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area	Verification Target
Column Chart Construction	Clustered Column chart built from `A4:G9`, styled with Style 14, positioned at `A12:H30`.
Series Editing	Design Consulting series removed; two-line title configured with Alt+Enter.
Break-Even Modeling	Row 15 formulas copied down through row 24; B5 tested at $200 and updated to $225.
2-D Line Chart with Markers	Non-contiguous ranges plotted; # Sold series deselected; category axis linked to `B15:B24`.
Visual Formatting & Print Setup	Legend at top, Black Text 1 Lighter 50% gridlines, plot area shadow, landscape fit-to-1-page print scaling.