Operations Management & Spreadsheets - Overview

I really enjoy it when a business owner allows me to help them improve their reporting, and in return, they share with me how their business works.

I've been privileged to work with many business owners that manufacture or assemble a product to help them develop management reports. Although each company sells a different product, there are some basic issues that are common to all.

In this series, I want to share with you the various problems that we must resolve to create the reports.

Our objective is to deliver a customized reporting system that provides management with the information it needs to be fully informed and quickly make decisions on matters that are critical to the growth, viability, and survival of the company

For many of these companies, buying an industry standard software is not a viable option for one or all of the following reasons:

1. The cost to purchase is too high for the perceived benefits.

2. An industry standard software package does not exist

3. An industry standard software package exists, but it is not designed to work with the client's business - it would not be cost-effective to customize the software to the client's business.

Consequently, many companies rely on spreadsheets - Excel and, increasingly, Google Sheets - for their management reporting system. Fortunately, today these programs have very sophisticated features that enable the clients to create very robust reporting programs.

Let's first look at the clients' operations - what does it take to produce the product?

The process has the following stages:

1. Receipt of inventory and raw materials.

2. Processing of inventory and raw materials to create the final product.

3. Packaging the final product.

4. Storage of the final product.

5. Delivery of the final product.

Steps 2 and 3 can be multi-step processes.

The client wants to track the activities from receipt of inventory to the delivery of the product. This is the information the client wants to track:

Inventory

• Quantity received

• The quantity used in processing

• Quantity unused

• Quantity reused

• Wasted/unused inventory and raw material

• Obsolete inventory

• The quantity used per unit

• Quantity in stock

• Minimum quantity required

• Reorder time – days from placing an order to receipt of inventory

• Quantity sold

• Cost

• Cost per unit

Activity

• Units per standard inventory quantity

• Units produced per hours

• Units produced per day/month

• Units produced per employee

• Unit production standard

• Unit production variance (standard vs actual)

• Compare inventory used in production to the starting and ending inventory

• Time required to complete the activity

Employee

• Number of employees

• Number of employees per shift

• Number of employees per shift per task

• Employees required to produce one unit

• Units produced per employee per day

• Units produced per employee per shift

• Units produced per employee per hour

• Employee’s department – may be assigned to more than one department if s/he does different tasks.

This information enables management to make fully informed decisions about matters that are critical to the company’s ability to operate efficiently, earn a sufficient profit, and survive in its market.

How does management use this information? These are some of the questions that the business owner wants to be answered:

• How many employees do I need to add as my sales grow?

• What is the standard unit of production, to set an employee performance standard or to know how much of an item is used during a given period?

• What is the proper level of inventory to maintain at all times?

• For any given period of time, what is my cost of goods sold expense?

• What is my total cost of production?

• Where is the best place to put my employee (assuming he is better at one task than another)?

• Do I need additional equipment, machinery, or space for my operations?

• How do I best allocate my limited resources to maximize production and profits?

In the next article, I'll discuss some of the spreadsheets vs database concepts that have to be addressed to create a proper reporting system using spreadsheets.