Strategy Dynamics Briefing 14: A “spreadsheet” view of resource accumulation

Posted by:

All this talk about how accumulation is different than the usual way we think of causal relationships may have got you worried…

Is this some mysterious process that needs fancy methods to work?

Although the time-charts and diagram structures we use may see an unfamiliar way of looking at business performance, they simply re-present what could equally be shown in a spreadsheet.Take this figure from the last briefing, where a constant rate of customer gains interacts with a rising loss rate to cause growth and decline in the company’s total number of customers, and hence in its sales.

Diagram: customers won and lost

  • In month 1, we add 20 customers and lose 12, so end the month with
    100 + 20 – 12 = 108.
  • In month 2, we add 20 customers and lose 14, so end the month with
    108 + 20 – 14 = 114
  • … and so on.

The spreadsheet also adds an extra line for average sales last month. As discussed before, when factors change over time, periodic calculations become increasingly accurate as the time between those snapshots shortens. In this example, unless sales rates change a lot from week to week, monthly periods are accurate enough for estimating future sales patterns.

A few things to note:

  • This is no different than what we do to track cash-flow and cash
    levels from period to period – so if we ‘account’ for cash like this,
    why would we not account for customers, staff and other valuable
    resources in the same way?
  • Whilst you can do this with a spreadsheet, there’s a big jump in
    understanding from the visual presentation of the time-paths.
  • The resource flow rates — new customers and customers lost
    per month
    — are telling us the trajectory on which the business is
    heading at the start of each period.

In this case, the initial customer win rate of 20/month and losses of 12/month mean that the customer base is heading upwards by a net +8 per month. By month 4, customer losses match the win rate, so there is no net change in the customer base. By month 12, customer losses of 36/month are way faster than the win rate of 20, so into the next month (month 13), the net change will be –16/month.

Here, winning 20 customers and losing 12 is not the same as winning 100 and losing 92! And this distinction is important in relation to other resources too. For example, for many years it was taken as normal for staff in the fast food sector to turn over quickly, with the average person staying for just a few months of temporary employment. Work methods and training were designed to get new staff productive quickly. But some aspects of staff performance, such as good customer service, come with experience. Consequently, Starbucks enjoyed for years a big advantage with a staff employment system that succeeded in retaining staff for much longer periods—typically well over 12 months. Not only were hiring and training costs reduced, but customers got a better experience too.

So going back to the key point of this briefing – accumulating resources are perfectly easy to model in a spreadsheet if you really want to. However, it’s not so easy to set up the clear graphical relationships that packages like mystrategy™ offer. [ This is the mapping and modeling software used to create all the diagrams in these briefings and the book – see www.strategydynamics.com/mystrategy]. Using such software packages has another big advantage over spreadsheets – because you can only set up equations using variables that are physically linked, it’s much harder to make structural errors in your models.

A friend of mine uses this facility to audit clients’ spreadsheet models – and alarmingly finds important errors in most cases! The variables are also named, so your equations look like “sales = customers * sales per customer” not “D13 = A$12 * C13”.

Surely not so simple!

A response I get quite early on to the basic idea that resources accumulate is ‘Yes, but there’s more than just winning and losing customers – some are bigger or more profitable, and others are smaller.

True, and we certainly have to deal with this, but let’s not try to run before we can walk. It will be some time before we can get into how to deal with a complete profile of a customer-base, but we can already work on customer-differences with the simple concept of ‘segmentation’, i.e. splitting them into groups.

To take a simple example, you may want to differentiate between a small but important group of large customers and a more numerous group of small ones. This is dealt with simply by ‘photo-copying’ the figure in this briefing – one copy for the large ones and a second copy for the small ones – and adding together the resulting ‘total sales to large customers’ and ‘total sales to small customers’ to produce ‘total sales to all customers’.

The spreadsheet equivalent would be to have separate worksheets for large and small customers – the analogy of a ‘photo-copy’ still works – and a front-page worksheet where the total is calculated. This is pretty trivial if all you are doing is tracking the customer flows and resulting sales for two groups as we are in this example, but could clearly get more complex, and therefore vulnerable to mistakes, if multiple groups are involved with more extensive information for each.

For an example, see below…

Until next time…

 

If you would like to receive the series from the beginning in your email inbox, please register on on our website and subscribe to Briefings in “My Account”

Not all customers [or staff] are equal

Segmentation can get rather sophisticated and complex.

For example, in my previous experience in the restaurant industry, we carved up the market very successfully by distinguishing
[a] the different groups of consumers who eat out – younger couples, older couples, single-sex groups, families with children etc. – from
[b] the various needs that a particular eating out occasion was fulfilling – celebration, ‘refuelling’, intimate meal and so on.

The result was a rather extensive matrix with 40+ significant combinations of groups-v-needs contributing to our total sales. For each ‘cell’ in this matrix, we needed to track the flows of people won and lost, the frequency of their eating-out occasions, the average spend per occasion, and so on. Handling all this in a single spreadsheet with no errors requires quite some expertise!

Strategic Management Dynamics book coverIt is beyond the purpose of these briefings to get into exactly how these kinds of situations are actually modeled in dynamics software. But it is worth mentioning that they enable this kind of complexity to be handled rather easily. You simply set up the causal structure one time, including the customer-resource and its flows, then tell the software that the structure is replicated for the various segments and add the relevant data for each.
[You need a more powerful software than mystrategy for this purpose, however.]

This briefing summarises discussion from chapter 3 of Strategic Management Dynamics,
pages 131-134

Read more about the book on our website

  Related Posts

Add a Comment