Breakdown

The Breakdown sheet automatically creates a disaggregation of an annual Income Statements into two business Segments. These segments are inputs on the Forecast and can be adjusted there, with all edits showing up here also.

Currently a feature in the Standard Financial Model and the Starter Finacial Model.

How to use #

This sheet goes beyond financial analysis to provide additional management analysis on the performance of the business. If you have multiple business lines, or different types of revenues, or multiple geographic regions, perhaps with different underlying economics (i.e. cost of goods sold, gross margin, acquisition expenses, operational expenses, contribution margin), and want to provide an additional layer of analysis to communicate the performance of these segments separately. For example, an ecommerce business with a subscription offer, for example, might want to breakout their revenues and cost of sales; a hardware business with a service or subscription add-on might want to breakout the hardware from the software and show the economics separately.

Not every user will find this analysis valuable, and this sheet is optional and can be safely deleted if desired, or hidden if you are not allocating revenues and expenses to different Segments on the Forecast sheet.

How it works #

Each revenue and expense line on the Forecast sheet has a column dedicated to each Segment that allows you to use the dropdown and select the segment to allocate each revenue and expense.

Expenses spanning multiple segments can be allocated to a "Overhead" category - common for SG&A overhead expenses - or broken out into separate lines on the Forecast sheet.

My recommendation is to label one segment as "Unallocated", "Overhead", or "Corporate" as a catch-all for any revenues or expenses that do not need to be allocated to a business segment, and that is why the default distribution has an "Unallocated" business segment. In general, if allocating it does not bring any additional insight about the business, it's likely not important to use the model to allocate it.

Inputs #

There is one input on the sheet: in C2, there is a dropdown that allows you to select the fiscal year you want to show in the sheet. Select the year you want to show, and the model will automatically update all calculations below.

Common Modifications #

Any information can be added to this sheet just by inserting rows and linking to the relevant metric calculated in the model. There are a few common modifications to this sheet:

• Changing the detail to Revenues, COGS, or SG&A. If you have multiple revenue streams, customer sets, or business lines, often you will want to detail those on the Breakdown sheet to highlight composition and changes over time. The revenue and SG&A segments provided by default are the segments prebuilt from your inputs on the Forecast sheet; if you change the labels for the segments on that sheet, they will automatically update here. If you change the organization of the segments or add/delete segments, you will need to also make the same edits to this sheet. In most cases you can insert/delete rows on this sheet to match the organization on the Forecast sheet, then simply copy the existing formulas from the row below or above your new rows, and the calculations will work; more involved edits will take assistance in editing the SUMPRODUCT() formula so that it picks up the correct information.
• Adding a Segment. Often a user will add a business segment on the Forecast sheet, and then want to show it here as well. Just insert a new column in the respective position (i.e. between the same segments as done on the Forecast sheet), and copy all the formulas from a business segment column next to it. You will need to manually adjust the formula in row 5 to pick up the new segment.
• Deleting a Segment. Simply delete the respective column on the Breakdown sheet, all formulas for the other segments and totals will work without edits.
• If you want to use this for multiple years of data, simply use the dropdown to select the year to change the year displayed dynamically, or duplicate the sheet and use the dropdowns to select the appropriate year for each sheet.