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:
The cost to purchase is too high for the perceived benefits.
An industry standard software package does not exist
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.
7 views