Link Search Menu Expand Document
Virtues

Export of Data from MSTR to Excel

urn:js:virtue:aspire:proposal:33.1

TL;DR

Export of data in CSV form from MSTR and not the use of the MSTR Excel Plugin.

Rational

  • The use cases for Excel contained in this presentation are just based on the experiences of one individual with one years experience in each of Fresh Supply Chain & Supply Chain Strategy. It is not intended to be an exhaustive list of use cases, but just to provide indicative examples of how Excel is used to make critical data-driven operational and strategic decisions
  • The different use cases have some different relationships to the necessity of Excel being the tool of choice
    • Some involve collaboration with external parties, who themselves use Excel (it is the “lingua franca”, as it were)
    • Some could in theory by done in R/Python etc. but those responsible for doing the work don’t (and are not expected to) have coding skill, but are expected to solve analytical problems
    • Some could in theory be done in MSTR, but it would
      1. involve massively upskilling hundreds of people to advanced levels of ability in MSTR
      2. it would make recruitment and on boarding extremely challenging as we recruit from an internal and external talent pool that has facility with doing analytical work in Excel
  • All the use cases in the presentation fall under the category of “ad hoc analysis” as opposed to “reporting”. We fully agree that Excel should not and must not be a reporting tool or be part of our data infrastructure
  • It is our view that this also has implications for what option is chosen in order to facilitate export of data into Excel, which is explained at the end

Implications

MSTR Export vs Excel Plug-In

  • The ideal solution, in our view, not only makes it easy for our users to access the data they need to do ad-hoc analysis, but also does not encourage them to use it in a perverse way
  • We also, as far as we can, want to encourage MSTR to be used to its full potential, and for our users to become familiar with how to use it as fast as possible For these reasons, we believe that enabling the Export function is preferable to the use of the Excel plugin The key feature of the plugin – to enable automatic refresh of data – would be very important if we wanted to use Excel for ongoing processes, this is exactly what we want to avoid
  • For the vast majority of use cases, automatic refresh of data is (in our view) not necessary
    • Even if data does sometimes change, the key is not whether a piece of ad-hoc analysis is now “out of date” or “wrong”, but whether it has resulted in an incorrect business decision
    • In those use cases where automatic refresh is necessary (e.g. reporting and ongoing repeatable processes), our opinion is we should be looking to productionise them using the proper tools
  • Even if the user journey eventually ends up in an Excel export from MSTR, the user will have had to at least started off
  • In contrast, the user journey of the plugin doesn’t involve learning how to master MSTR at all, and could encourage the wrong behaviours
  • We do have some reservations about the relative functionality of the plugin that I am sure can be resolved with development work, however we would much prefer to spend development time making the MSTR environment a good user experience that encourages the use of MSTR
  • I fear the reality of the plugin is we will get a lot of user demand to improve the functionality of the plugin, which is not advancing our strategic data goals

Appendix

Migrated From Confluence

link Original author: Lotz, David

Use Case 1: Produce Long Lead Time Order Management

While confirmed supplier orders are all done systemically by SCION on a 24 hour order cycle, the process of advising growers of how much fruit they need to bring into the UK to meet expected demand is done completely manually on much longer lead times

The process involves

  • Creating long-term volume plans (effectively a very long term demand forecast for the whole of the season)
  • Receiving files from our service providers showing the quantity of stock is currently in the country
  • Receiving files from growers confirming what has been shipped
  • Using the 4 week RDF demand forecast combined with the volume plan, current stock and confirmed stock on the water to calculate Load Advice to growers on how much to ship to meet future demand. This is sent weekly to growers all over the world, in Excel files

Modelling of the volume plan is done in Excel, and is usually based on sales volumes of previous years and adjusted for promotions, past availability issues, pricing changes etc. that would affect sales. Seasonal changeovers also differ year on year due to weather conditions

The stock files received from service providers are in Excel, as are the confirmed Load Advices from growers

The Load Advice is calculated using Excel models

There are approximately 10 analysts in Produce who work on some aspect of this process

Use Case 2: Promotion Planning

In order to make sure that suppliers are prepared for the uplift of planned promotions, we frequently collaborate with suppliers well in advance of the systemic forecast horizon to provide indications of likely volumes

While many promotions are routine and have been done before, we also frequently run novel promotions where it may not be straightforward to assess likely volumes based on past data. These can often be high profile, such as the £1 TTD Easy Peelers features in our 2019 Christmas advertisement, or the seasonal £1 veg offer in Easter 2019 (with Easter moving around, this by necessity ends up being different every year)

These processes are often iterative – making assumptions, sense checking the output etc. – and the modelling is done in Excel

  • Taking the TTD Easy peelers as an example, the analyst downloaded all the Christmases we had worth of daily SKU level sales data in Citrus, then did some modelling in Excel to figure out how we thought volumes would likely change this year across the subcat based on the different pricing strategy and the impetus of the advertising
  • We combined this with other data such as the weight of the product, so we could see whether the number of tonnes of citrus we thought we were going to sell was plausible compared to when we’ve run offers at different product weights etc
  • The analyst then sent spreadsheets containing indicative volumes to the suppliers to ensure they had enough packaging and would be able to supply

All Supply Chain analysts /leads/managers will do this sort of work to some degree

Use Case 3: Swing Analysis (/ general considerations for Strategy & Change teams)

Swing analysis is just one example of a great many types of modelling done in the Buying Better for Less team to identify cost-saving opportunities in the supply base

At most basic level, swing analysis takes assumptions about the suppliers business (using sources such as company accounts and industry knowledge) to assess how changes in Sainsbury’s volume would impact the economics of a supplier’s business, primarily through fixed cost dilution

The analysis of the supplier’s business is done in Excel, and then actual sales volumes are used to assess the financial impact of actual volume changes on supplier economics

This work could all be done, in theory, in other software packages. However, the external market we recruit from into these roles is from consultancies / other businesses / internally where we are looking for

  • Commercial nous / experience
  • Project management and influencing skills
  • Advanced excel skills

Were we to not expect these individuals to use Excel to do their work, we would face a choice between a greatly reduced talent pool, sacrificing necessary commercial experience or placing a very steep learning curve on new joiners to have to completely relearn how to do basic aspects of their job. We are also competing with an external market here where Excel is a marketable skill and therefore people want to use and develop their skill

BBFL is a team of approx. 20. These considerations would also apply to use cases in Strategy & Change which are teams of comparable size

Use Case 4: Fresh Brexit preparation (an example of slightly more advanced analysis)

In the event of a disorderly Brexit, a key part of the plan to protect availability in Fresh foods was to temporarily hold more stock in store. However, to decide how much protection we could afford and what the cost would be, we had to estimate the effect of increased stock holding on wastage

In order to do this, I created a linear regression in Excel using historical stock, sales, waste, rate of sale and product life data to attempt to isolate the impact of having more stock cover on waste

The skill sets in supply chain include a number of people like me who understand statistics/basic data science, but who do not have a coding background. Therefore while in theory these sorts of pieces of analysis could be done in R/Python we do expect anyone in the operation to have facility with these

Again, in theory you could separate the domain of supply chain managers/ analysts and “Analytics”, however

  • the reality is that the former are expected to solve analytical problems whilst also having all of the softer skills of supplier and cross-functional collaboration (which is actually the much larger part of the job)
  • They are not paid as if they have advanced analytical skills
  • We don’t (I believe) have enough people who are paid to have advanced analytical skills to do all this work

Therefore, absent a rethink of our job roles and talent strategy, we have to give our non-Analytics experts the ability to do this sorts of analysis in the tool that they understand how to use given the backgrounds they are recruited from

Use Case 5: Spanish Inbound Peak Logistics Planning

Christmas and Easter involve bringing in significantly more volume of produce from Spain than in our normal operation, and Supply Chain needs to provide logistics an estimate of how many vehicles we are likely to require on which days in order that the trucks and drivers can be secured

In order to provide this to logistics, we need to convert forecasted sales into the number of trucks needed to move the product, and when those trucks will be required

The volume plan (as referred to in Use Case 1) and the demand forecast are combined with assumptions about case sizes, number of cases per pallet and number of pallets per vehicle and the order lead time to estimate the amount of vehicle space required each day that is implied by the demand forecast

This modelling is done in Excel, and it is tied into a number of different processes referred to in Use Case 1 (above)

This work is also done on a long-term basis to aid logistics planning and minimising the end to end cost of our Spanish operation

The Spanish logistics operation is managed by a 3rd party under contract, and Excel is the tool of choice for that external collaboration