Analyzing Simulation Data

When you play a simulation of your supply chain you will see a running total cost of operations for each facility in the data display window on the right side of the screen. In that window you see the names of your facilities and to the right of each facility name is its accumulated operating cost shown in bold numbers. On the simulation screen you can click on the various tabs in the data display on the right side of the simulation screen to see this data. The data available in these four tabs are shown below.

SIDEBAR DATA DISPLAYS

Instr Facility data
(click image for larger picture)

Shown above from left to right are examples of: the Facilities tab; the Vehicles tab; the Products tab; and the Console tab.

In the “Facilities” tab you see the facility name, its accumulated opeating cost, amount of products on-hand, and a graphic display of products on-hand day by day

When you click on the “Vehicles” tab you see the names of the vehicles, cumulative total operating costs, cumulative carbon output, destination and name of route.

On the “Products” tab you will see facility names, product names, on-hand amounts and value of on-hand amounts

Click on the “Console” tab to get a daily breakout of this data for each facility and product

You can download all of this data to a spreadsheet for further analysis if you click on “Console” tab and then click button in the upper right corner labeled “Export Results to Excel”. This export button appears whenever the simulation stops because of a supply chain error or when you press the “Stop” button (also in upper right corner).

SEEING THE BIG PICTURE AND ANALYZING THE DETAILS

SCM Globe uses a map-based user interface to provide a clear geographic context for you to understand the simulation results and not get lost in the details. The goal is for you to see the detailed simulation data within the big-picture context provided by the map of your supply chain (see more about user interface design here – User Interface for SCM Globe).

As you run simulations of your supply chain you see how inventory moves through the different facilities. You see where too much inventory builds up and where it runs out. You see where facilities need more storage capacity and where they can do with less. You see how transportation costs vary depending on how often you make deliveries and what kinds of vehicles you use. Click on the display tabs for the different facilities and vehicles to see graphic and numeric presentations of the simulation data.

The game of supply chain management is about combining analytic skills with intuitive big picture understanding – also known as “street smarts”. When playing the game of supply chain management, the best players develop and combine their analytic skills and their street smarts.

SOME THINGS TO KEEP IN MIND

When you look at the on-hand inventory graphs for different facilities watch for a few simple yet critical characteristics:

Sc 2-panel1 (click image for larger picture)

  • Downward sloping lines mean not enough product is delivered to meet demand
  • Upward sloping lines mean more product is being delivered or manufactured than is needed to meet demand at that facility
  • Saw tooth patterns indicate deliveries happening every few days — on-hand product gets drawn down by daily demand and then spikes up when deliveries are made
  • Stair step patterns indicate occasional missed deliveries that eat into safety stock (downward sloping stair step), or they indicate occasional extra deliveries that add too much to safety stock (upward sloping stair step).
  • A flat line or relatively flat line means product delivery is matched with demand – that’s what you want to see, the flatter the line the better (like the yellow line in the display on the right side above). Try to flatten out the on-hand product trend lines as much as possible at each facility. Then move that line (and the inventory level it represents) down as far as possible so as to minimize on-hand inventory. That lowest level is the safety stock level – it equals enough product on-hand to cover demand on those days when product delivery is missed.

Try to synchronize production with overall demand. Total up daily demand at all the stores that are supported by a factory. Then set factory production levels to meet that demand and not exceed demand. If you move productions levels up or down, you can adjust operating costs up or down accordingly using your best estimates (or just leave the default costs in place if you wish). Learn more about reducing inventory and operating costs in the section “Cutting Inventory and Operating Costs“.

Click on the products tab to see where inventory is building up in the supply chain. The big numbers jump out quickly.

Click on the vehicles tab to see running costs of vehicle operations. The big numbers jump out quickly.

Use these on-screen data displays to get a big picture view of the overall supply chain. Avoid diving into detailed data analysis until you have first experimented with different actions based on your visual analysis of trends. Be creative.

Keep both browser tabs open – the supply chain edit tab and the simulation tab – and flip back and forth as often as needed to look at the data on the simulation tab and make changes on the edit tab (remember to click the Refresh button on the simulation tab before re-running a simulation after making changes on the edit tab).

First use quick visual analysis and street smarts to steer your supply chain in the right direction. Then use detail data and analytics to fine tune it. Download simulation data to a spreadsheet and do some detailed analysis of particular facilities, vehicles and routes to fine tune your design and achieve even lower operating costs and on-hand inventory amounts.

When your simulation stops because it found a problem, or because you clicked on the “Stop” button, a new button will appear on the Simulation screen. It is labeled “Export Results to Excel” (see screenshot below, red arrow #1). Click on this button and it will export the simulation data as a comma separated values file to your computer (see FAQs question #1 if you use Firefox browser).

Put formulas in your spreadsheets containing the downloaded simulation data and calculate useful ratios and key performance indicators. Calculate appropriate supply chain and financial performance indicators to analyze the simulation data. If the exact data you want is not provided by the simulation, then look for ways to calculate or estimate that data from what is available. Here are some points to keep in mind:

  • There is no simulation data on the sales revenue supported by the supply chain, but you can get a good estimate by looking at the data showing products going out of a retail facility.
  • Assume products going out of a facility defined as a store represent units sold, so multiply the number of units going out of retail facilities by the prices of those products and add in the gross margin or retail markup used by the stores and you have a good estimate of sales revenues.
  • Cost of certain products used in a factory or store can be estimated by the amount of those products that come in to that facility. Multiply the amount of those products that come in by the product price.
  • Simulation data for products coming in and out of facilities are cumulative, so to get daily numbers, subtract the previous day’s numbers from the current day’s numbers.
  • Facility costs include both rent expense and operating expense, and the simulation data is cumulative. Get the daily cost for a facility by dividing the total cumulative operating cost for a facility by the number of days in the simulation period.
  • Vehicle costs are also cumulative, so get average daily vehicle costs by dividing the total cumulative cost for a vehicle by the number of days in the simulation period.

DOWNLOAD SIMULATION DATA TO SPREADSHEET REPORTING TEMPLATES

You can download the data produced by simulations and use it to create different reports. In this section we’ll review a spreadsheet report template to create an Income & Expense Report and generate supply chain performance indicators. The screenshot below shows a simulation of the initial Cincinnati Seasonings supply chain after it has run for 30 days.

In this example the simulation runs beyond 30 days, yet only 30 days of data are needed, so just click the “Stop” button (1) after the simulation runs a few days past 30. The “Export Results to Excel” will appear. Click that button, and see the simulation data downloaded (2).

NOTE: The “Export Results to Excel” button will appear whenever you run a simulation and the simulation stops because it finds an error, or because you click the “Stop” button.

S1 - Sim Data Download
(click on image for larger picture)

The data is downloaded as a CSV (comma separated values) file so it can be opened by any spreadsheet application – Apple, Google, Microsoft, etc. If you are using the Firefox web browser there are a few more steps to opening the downloaded simulation data, see an explanation in the first answer in the FAQs section of this guide.

Once the simulation data is open in your spreadsheet, you can view the downloaded data. It is grouped into sections. There are data sections for:

  • On-Hand report product on-hand by facility by day
  • Demand report product demand by facility by day
  • Production report products produced by facility by day
  • In report – products coming in by facility by day (numbers are cumulative)
  • Out report – products going out by facility by day (numbers are cumulative)
  • Facilities Cost report – storage space rent plus operating cost by facility by day (costs are cumulative)
  • Vehicles Cost report – cumulative cost of operations by vehicle

Delete the simulation data that goes beyond 30 days. The screenshot below shows several days of extra data highlighted and about to be deleted. Click the “Edit” button in the spreadsheet command menu and “Delete” in the sub-menu to delete the highlighted rows from the spreadsheet (do not just highlight rows and press the delete key as this will only delete the data but not the actual rows in the spreadsheet). Trim the extra rows in the other sections of downloaded data as well. Leave just one blank row in the spreadsheet between the end of one data section and the name of the next data section.

Also divide each vehicle cost amount by the number of days in the simulation to get a pro-rated daily cost for vehicles. Then multiply vehicle daily costs by 30 to get 30-day costs for each vehicle.

S2 - CSV File
(click on image for larger picture)

The simplest way to load simulation data from this spreadsheet into the reporting template spreadsheet is to copy all the data on the spreadsheet you just trimmed, and insert it into the data worksheet of the reporting template spreadsheet. (See note at bottom of this page for instructions on downloading a copy of the reporting template spreadsheet.) Follow these steps:

  • Select all the data in the spreadsheet holding the downloaded simulation data (Ctrl-A), and copy that data (Ctrl-C).
  • Then open the reporting template spreadsheet At the bottom of that spreadsheet click the second tab to go to the second worksheet in the reporting template. This is the worksheet for the simulation data.
  • On this worksheet, select all (Ctrl-A), then insert (Ctrl-V) the data you just copied from the spreadsheet with the downloaded and trimmed simulation data.
  • Now click the first tab at the bottom of the reporting template spreadsheet to go to the worksheet with the reporting template. The new data just inserted will show up in the income and expense report and the performance indicators as shown below.

S3 - Income and KPIs Rpt
(click on image for larger picture)

These reports help identify the facilities that are not performing well and the operations that need improvement. For instance, we can see right away the Ft Wayne Store has problems. It is has negative gross profits. When you look at the supply chain performance numbers you see more about the Ft Wayne operation; its on-hand inventory rises sharply during the month, and the inventory days of supply are larger than they need to be. This is also true for several other facilities. And because of these problems, you can see the gross profit for the total company is negative. That needs to change.

By analyzing these reports you can see what changes you need to make to the facilities, vehicles and routes in the supply chain model that created this simulation data. Make those changes, run a simulation, and download the simulation data into this spreadsheet again and see if it produces the improvements you are looking for.

These reports pull data from the worksheet that contains the downloaded simulation data. Once you set up this reporting page, you can download data from different simulations into this spreadsheet and create reports to show data from different simulations of a given supply chain model. These reports are examples of what can be done. There are other reports and performance ratios that can be calculated from the downloaded data. This template can be expanded as needed to add new facilities and products so it will work with any other case study or supply chain model. Look at the spreadsheet report template and see how the reporting tab reads the data in the simulation data tab. Expand this reporting logic as needed to cover additional facilities and products you may add to your supply chain model. See your documentation for the spreadsheet you are using if you need additional information on how to expand the reporting logic in your spreadsheet template.

NOTE: Download a copy of this template from Google Drive here — in upper left corner of template screen under template name, click “File” and “Download as”, then select “Microsoft Excel” or “OpenDocument format” — https://docs.google.com/spreadsheets/d/15ETEtntXic8WQl-LSPbZQLnXgiwmwvQzLh37C9IKOF0/edit?usp=sharing

 

Copyright © 2016 by SCM Globe Corp.