Analysis and Key Ratios in FP&A

We talked a lot about soft skills and political acumen one must possess in order to succeed in FP&A. All of those are critical but you still need to be able to do your core job well. I will focus here on key analysis that any FP&A professional should be able to perform.

Trends of any particular value is a quick way to see how this value has performed over time. Usual trends cover either a current fiscal year or a 12-month rolling trend. Some reports could use rolling 4 -12 quarters. The best way to present trends outside simple tables is through charts. Line chart or bar chart is good for trending. Try not to put too much information on a single trending chart — Revenue and Net Income might be too much for the same chart, especially if you are a part of a low-margin business. The trick here could be to show one on primary and the other on the secondary axis. The simple table format is great to present trends, especially when presenting a full Income Statement with multiple lines. When presenting a total fiscal year and when showing quarters is requested, my preference is to keep 12 months together, then quarter and keep total year at the end. This way you can follow the trend and not see three months of data followed by a jump in the quarter. 
Ratios and Normalization are important as they allow to see how various values performed in relationship to one another. While trends provide a great view into how a firm is performing in absolute terms, it might be somewhat misleading. For example, when showing a trend of Revenue growing month to month the initial reaction could be that the company performs great.

However, when looking at the Net Income as % of Revenue, this picture could be complete opposite with values rapidly declining. This could indicate shrinking margin, out of control fixed expenses or unsustainable promotions.

Ratios allow for the values to be normalized. Typical ratios are shown as % of Revenue. Good candidates for normalization are average price or cost per unit, average salary per employee, sales or cost per day. When looking at someone’s analysis you can do a quick check by validating a ratio and see if normalized values make sense. For example, you procurement provides you with new expectation for next year, you can then see how much it is per project unit to be sold (ideally per unit made, but assume you are not building finished goods inventory) and after that compare to the same average per unit from the prior year. You can also leverage trending to see if over time these costs have been increasing or decreasing. Basic normalization can provide you with a “smell test” — do the numbers smell right. My average fully loaded employee cost is $50k/year, we increased headcount due to expansion and now when checking average per employee the cost is $45k/year. This doesn’t pass the smell test. Doing some basic math my overall salary & benefits forecast is 10% (or higher since new employees usually come at a premium) lower than it is supposed to be.

Flexing is my favorite, and I’m not talking about standing in front of a mirror, well maybe I am. In FP&A I call flexing — adjusting one of the variables to prior period or plan value. This helps to break a variance into specific drivers. It is often called Price/Volume change analysis. For example, you sold 1,000 gadgets for $25,000, making it an average price of $25/gadget. Last year your average price was $30/gadget. Flexing sales at prior year’s price would have resulted in $30,000 of revenue, thus $5,000 was lost due to the price decrease. The analysis would have to go deeper to determine whether there were additional incentives or discounts or any lost collections etc. Continuing with the same example, let’s say last year we only sold 700 gadgets, this means last year’s revenue was 21,000 (700 * $30). Year over year our revenue has grown by $4,000 ($25,000 — $21,000). Sound great, right? It is great, but as we can see the increase in the volume of sales comes with the trade-off of lower pricing. Flexing for the volume we can say that we had $7,500 of additional revenue due to volume increase (300 * $25), which was partially offset by $3,500 in lower pricing (($30 — $25) *700) netting to $4,000 mentioned above. The trick to flexing is to stay consistent. Notice that we multiplied additional volume at new price point since all of the new volume is sold at the new lower price. We then assume that have we kept the same pricing for the first 700 units, we should have sold it at $30, instead of $25, thus applying 5 discount only to 700 units. When you are dealing with a single product total difference can be easily broken down between volume and price (or cost). The sum of the two impacts should add up to total variance. This analysis will work if both price and volume move the same direction (increase or decrease) or opposite direction (our example). Flexing can be used for other things as well — for example, what would have happened if FX rates stayed at Plan level instead of actual rates? What would have happened if utility prices did not change? Whatever are the main drivers for your company, those drivers should be flexed to understand how they impact your company’s performance.

Mix is the next component of flexing analysis. When you have multiple products change in the mix of products could result in higher or lower profitability. You can perform the analysis of each individual product just like we did above in the section on Flexing or do it in total. In this case, in addition to volume and price (cost), you would have a mix component. For example, an airline sold 300 tickets for an average flight: 10 are business class at $1,000 per ticket and the rest are regular class at $400/ticket. Total revenue here would be 10 * $1,000 + 290 * $400 = 126,000. Our average price per ticket here is $420. Now let’s say that the same plane got repurposed for a longer howl flights increasing prices by 20%, shrinking seats in the economy class and removing a few economy seats (welcome to new level of flying where butts are getting bigger while seats are getting smaller) and as the result increasing business class to 20 seats. The new flight can now hold 304 passengers (we added 10 business class seats and removed 6 economy seats — one row). So we have increased our volume by 1.333% ((304–300)/300) and we raised prices by 20% as mentioned earlier. As the result of volume increase our additional revenue is $2016 ($420 average ticket price * 4 additional seats * 1.2 for 20% price increase). We also raised prices by 20%, which should equal to 25,200 ($420*20%*300). Our total benefit is 27,216. That’s great, but is it correct? We now sell 20 tickets at 1,200 ($1,000 plus 20% price increase) and 284 tickets at $480 ($400 plus 20% price increase). Total revenue from the flight should be 160,320 ($24,000 + 136,320). Our variance is 34,320 (160,320–126,000), while our explained variance is 27,216, leaving us with 7,104 of unexplained variance, which is attributed to the mix. From my experience, after flexing for prices and volume the remainder is assumed to be driven by mix. If there are no other major factors (exchange rate, discounts, incentives), it is a fair plug. Given that we have a simple example, let’s ensure our math ties. Our mix has shifted from roughly 3.33% business class to 6.58%, we almost doubled our most profitable customer segment. What changed in the mix is a) 10 additional business class customers and b) 6 fewer economy class passengers. To calculate mix we can do three approaches:
1. Simply plug in the remainder as mix. This is the less preferred method as it assumes no other changes. However where there are multiple product movements, as is the case with most large organizations, plug approach might be the best option. For additional analytics to get to the bottom of true mix impact, a more analytical approach would still be needed.
2. Calculate overall mix and volume impact and then back out the volume. 10 additional business class customers result in $12,000 of additional revenue plus 6 fewer economy class passengers result in -$2,880 (6 * $480). Totaling 9,120. The result does not tie to our expected unexplained variance of 7,104. To get there we need to subtract volume impact of 2,106, resulting in the expected result of 7,104. Simple luck? Not really, without getting into all the details of the math (you can see the table below), but simply taking the increase in both customer segments we inadvertently counting additional volume impact in here and as the result should back it out. 
3. Calculate true mix change by flexing new volume at old product mix (thus the term flexing that I like to use). This approach is a little more complex than the second one, however, it allows you to isolate mix. The first step for this approach is to “flex” new volume at prior product mix. To do so take % of the total before changes (3% for business, 97% for coach) and apply those to new total customer count of 304. You then take variance between new customer count by product and customer count flexed for prior mix and multiply the variance by the new price point. The table below illustrates the math:

Key Variances that are used by FP&A are variance to the latest forecast, plan, prior year and sometimes prior period. The first three are usually seen on most financial reports. Variance to latest forecast usually explains latest month result (assuming the company does monthly forecast updates). Variance to plan or budget compares the annual plan to either actual results or latest forecast. This variance allows companies to check whether they will meet or exceed the budget. Another variance is to prior year that allows companies to highlight improvement or downside in performance year over year. Variance to prior period is less common but is still used. Sometimes companies review sequential quarters and see variance quarter over quarter. Sequential variances can be seen via trending reports and as the result are less impactful in variance reports. Additionally, businesses that are seasonal in nature would benefit less from sequential variance analysis as it would not include seasonality impact.