Skip to main content

Product Portfolio and Lifecycle Case

Business Scenario​

Charles River Home Furnishings sells finished goods across multiple collections, style families, materials, and finishes. Merchandising, sales, and operations all depend on the same Item master while the catalog is active. They need product attributes that are clear enough to support reporting and reliable enough to support real decisions.

Lifecycle status raises the stakes. A Core item supports the steady catalog. A Seasonal item carries a narrower selling window. A Discontinued item signals that the company should stop treating the product like part of the active portfolio. When those labels drift away from the item master or from actual activity, management loses trust in the portfolio view.

Your job is to explain the portfolio structure first, confirm that the item master can support it, and then show how lifecycle logic changes the way managers interpret sales and exception activity.

The Problem to Solve​

You need to prove which item attributes define the portfolio in a usable way. Confirm that lifecycle labels and active status align with the current catalog state. Confirm that sales and portfolio mix differ by collection, style family, lifecycle status, and supply mode. Confirm that lifecycle exceptions appear in real operational activity.

What You Need to Develop​

  • A clean description of the portfolio structure for one or more collections.
  • A lifecycle-status interpretation tied directly to item-master fields.
  • A mix analysis using collection, style family, lifecycle status, and supply mode.
  • An exception view for missing attributes, inconsistent status, or post-discontinuation activity.
  • A short management-facing conclusion on which catalog dimensions deserve follow-up in the separate profitability case.

Before You Start​

Step-by-Step Walkthrough​

Step 1. Define the portfolio structure in the item master​

Start with the portfolio itself. Before you interpret sales or returns, you need to know how the company classifies the catalog.

What we are trying to achieve

Establish the main catalog dimensions that segment the portfolio across collections, style families, lifecycle status, and supply mode.

Every later conclusion depends on these dimensions. If the portfolio structure is weak or unclear, later managerial analysis will also be weak.

Suggested query

Product Portfolio Mix by Collection Style Lifecycle Supply Mode

Use this first to see how the catalog is distributed across collection, style family, lifecycle status, and supply mode.

What this query does

It summarizes the product portfolio using the main item-master attributes that define catalog structure.

How it works

The query groups Item rows by collection, style family, lifecycle status, and supply mode. That gives you a high-level portfolio map before you move into quality or transaction evidence.

What to look for in the result

  • the largest collections and style families
  • how much of the catalog sits in each lifecycle group
  • where manufactured and purchased items concentrate
  • which dimensions look strong enough to support later analysis

Step 2. Check whether the item master is complete enough to trust​

Once the catalog structure is visible, test whether the supporting attributes are complete enough to trust.

What we are trying to achieve

Find sellable items that are missing key catalog attributes such as collection, style family, primary material, finish, or lifecycle status.

Missing item attributes weaken reporting, teaching, and management review. A portfolio analysis fails quickly if core item-master fields are blank.

Suggested query

Item Master Completeness Review

Use this to find items with missing catalog attributes that should exist for their item group.

What this query does

It flags items that do not carry the expected descriptive fields for their item group.

How it works

The query evaluates item-group-specific attribute expectations inside Item and returns rows where one or more required descriptive fields are missing.

What to look for in the result

  • missing CollectionName
  • missing StyleFamily
  • missing PrimaryMaterial
  • missing Finish or Color
  • missing LifecycleStatus

Step 3. Test whether lifecycle labels and active status align​

After completeness, move to consistency. A complete item master can still carry conflicting lifecycle logic.

What we are trying to achieve

Identify current-state conflicts between LifecycleStatus and IsActive.

Lifecycle analysis only works when current-state item status makes sense. If active flags and lifecycle labels disagree, portfolio reporting becomes hard to defend.

Suggested query

Item Status Alignment Review

Use this to isolate conflicts between lifecycle status and active-status logic.

What this query does

It returns item-master rows where current catalog status appears internally inconsistent.

How it works

The query reads Item directly and compares lifecycle labels with active-status logic and related descriptive fields.

What to look for in the result

  • discontinued items that still look active
  • inactive items that still look like current catalog items
  • catalog segments where status logic appears less reliable
  • whether the problem is isolated or widespread

Step 4. Connect the catalog to sales and portfolio mix​

Now connect the item master to business activity. The goal here is to confirm that the catalog dimensions actually matter when products sell.

What we are trying to achieve

Show how portfolio dimensions appear in billed sales and contribution interpretation across collections, materials, lifecycle groups, and supply modes.

Catalog structure earns its value when it explains business results. This step shows whether the item-master dimensions are useful enough to support later profitability work.

Suggested query

Sales Margin by Collection Style Material

Use this to connect billed sales and margin patterns back to collection, style family, and material.

Contribution Margin by Collection Material Lifecycle Supply Mode

Use this follow-through query to extend the view into lifecycle and supply-mode interpretation.

What this query does

The first query summarizes billed sales and margin by catalog dimensions. The second extends the same view into lifecycle status and supply mode.

How it works

These queries join sales activity back to Item and aggregate results by the descriptive fields that define the portfolio.

What to look for in the result

  • collections with stronger billed sales concentration
  • lifecycle groups that carry weaker or stronger contribution patterns
  • supply-mode differences inside the same collection family
  • which dimensions deserve deeper follow-up in the profitability case

Step 5. Extend the case into lifecycle exception follow-up​

Finish with the control question. If lifecycle status matters, it should change how you interpret real activity after the catalog state shifts.

What we are trying to achieve

Find lifecycle-driven exception activity and connect it to customer-facing outcomes such as returns or refunds.

This step turns lifecycle status into an operational control concept. It shows whether the company continued using products after launch windows or after discontinuation.

Suggested query

Discontinued or Prelaunch Item Activity Review

Use this to identify operational activity that occurred before launch or after discontinuation.

Portfolio Return Refund Impact by Collection Lifecycle

Use this follow-through query to connect lifecycle grouping to return and refund behavior.

What this query does

The first query flags item activity that conflicts with lifecycle timing. The second shows how lifecycle groupings relate to downstream return and refund pressure.

How it works

The audit query overlays item lifecycle fields with transaction dates from sales, purchasing, manufacturing, shipment, and invoicing. The managerial query groups return and refund measures by collection and lifecycle status.

What to look for in the result

  • discontinued items that still show fresh activity
  • pre-launch items used too early
  • collections with heavier return or refund pressure by lifecycle group
  • whether lifecycle status changes the interpretation of the portfolio story

Required Student Output​

Submit a short case memo or notebook note with these four artifacts:

  • Evidence summary: identify the key result rows, metrics, timing patterns, or exception families that changed your diagnosis.
  • Accounting or business interpretation: explain what the evidence means for the process, accounting treatment, managerial decision, or control risk.
  • Database explanation: name the source tables, row grain, join keys, or trace path that make the evidence defensible.
  • Management or audit conclusion: state which driver, document path, or exception family should be followed up first and why.

Optional Excel Follow-Through​

  1. Filter Item to one collection or one style family.
  2. Compare CollectionName, StyleFamily, PrimaryMaterial, Finish, Color, SupplyMode, and LifecycleStatus.
  3. Build one pivot that counts items by collection, lifecycle status, and supply mode.
  4. Build one follow-up pivot that compares billed sales or return pressure by collection and lifecycle group.
  5. Keep the workbook focused on catalog structure and lifecycle interpretation. Save broader profitability modeling for the separate profitability case.

Wrap-Up Questions​

  • Accounting/process: How does lifecycle status change the way item activity should be interpreted?
  • Database/source evidence: Which item attribute, lifecycle field, activity row, or status-alignment key supports the portfolio segmentation?
  • Analytics judgment: Which item-master quality issue would most weaken management reporting?
  • Escalation/next step: Which portfolio dimension should management carry into the profitability case next?

Next Steps​

Notice a mistake?Report an error on this page