Spreadsheet Engineering


Spreadsheet Engineering

When they are well designed, spreadsheet documents can be powerful day-to-day analysis models. SimWell will help you make the most out of Excel, by developing smart reusable workbooks for you, or coaching you to be able to make them yourself. Some studies show that errors occurs in about 88% of spreadsheets built, and at least 1% of formula cells (Panko, 2008). When these errors could lead to making wrong multi-million-dollar decisions, it’s better to ask a spreadsheet expert for assistance in creating such critical documents

Here are some of the many applications where spreadsheet models are helpful for analysis:

  • Forecasting
  • Production capacity calculations
  • Performance measures: KPIs
  • Balancing resources between tasks

Microsoft Excel – The Industry Standard for Spreadsheets

microsoft_excel_2013_logo_with_background

Microsoft Excel has been the most widely used spreadsheet software for over 20 years. It is part of the Office suite from Microsoft.

It is a particularly useful software because of the fact that pretty much everyone uses it for business and therefore sharing documents is easy. And that works both ways: since everyone uses it, it has received a tremendous number of user suggestions over the years and has therefore been improving continuously.

Flexible Display and Layout

The information stored in Excel sheets can be moved around and formatted to meet any specific situation’s requirements. The cells’ appearance is 100% adjustable. Even more than than, conditional formatting allows the colors in cells to be adjusted automatically, based on the values which are calculated.

For example:

  • If the sum of of all cells in a column must be 100%, a checkup can be setup and the color of the header will turn to red automatically when the 100% is not met.
  • In a range of cells presenting comparable values, Excel can automatically change the colors so that the highest values are green and the lowest values are red (or any other color, if the user has a preference)
microsoft-excel-tools-graphs-functions-pivot-tables

Macros and Automation with VBA

vba-microsoft-excel-macros-automation-in-spreadsheets

Any manual action in Excel can be automated to avoid wasting time by repeating the same steps over and over. It also reduces the chances of making mistakes. Visual Basic for Applications (VBA) allows recording some tasks and adjusting the code to do a lot more with loops, subroutines, etc… Here are some examples of automated tasks:

  • Updating a certain data set from an external source
  • Reformatting some data: splitting strings, find replace information
  • Creating several similar charts, but for difference KPIs

SimWell has experienced VBA programmers who can create some powerful, reusable code which will save you a lot of time and which you can reuse and adapt to many situations in several documents.

Compatible

Even if a collaborator doesn’t have access to a licensed Excel software installed on a computer, the workbooks can be opened by anyone using the free online version of Excel provided by Microsoft, as well as with Google Sheets and some open-source desktop applications like OpenOffice. Many other software and utilities are built to get data directly from Excel documents, or export information towards the Excel file format. For example, most Business Intelligence applications software such as Tableau can use Excel documents as a data source for analysis.

Powerful Data Manipulation

The tools in Excel provide a lot of power for filtering, grouping and sorting data. Pivot tables are one of the most useful functionalities are they are easy to learn. It can be used anytime you need to get counts, totals, averages values from a raw table of results. You can therefore summarize data and get KPIs for sales, production, marketing, inventory, shipping, projects, etc. Within a few clicks, any user can get insight from a Pivot Table. They are also very useful when building complex spreadsheet models where aggregated data needs to be reused elsewhere in the workbook file.

excel-pivot-table-totalsales-per-customer

Examples:

  • From a order list with dates and customer numbers:
    • For each customer: Get a total sales amount and number of orders
    • Get an average sales amount per order, by month
  • From a production report with product types and batch sizes:
    • Get the average batch size per product type
    • Get the total parts produced per day of the week, quarterly

Excel is the Perfect User-Interface for Arena Simulation

SimWell uses Excel to create high quality user interfaces to interact with Arena simulation models. Arena can import all of the input data required to run a model from a single Excel workbook. The input parameters are well organised in a certain number of sheets. The ranges and array formulas in Excel allow SimWell to ensure the input data is pulled into Arena in the most practical format possible. The security and validation options in the spreadsheets also prevent the user from changing values or formulas which should stay locked for the entire project/study.

simwell-arena-simulation-user-interface-ui-in-excel-input-output

Arena can also write outputs to Excel. Updating some KPIs in a dashboard in Excel can give the analyst the big picture of the results of a simulation run, without any delay.