Tech Tool: Spreadsheet SoftwareDespite the fact that the iElectronics company has had a co...

Tech Tool: Spreadsheet Software

Despite the fact that the iElectronics company has had a computer for the past 10 years, all data collections still rely on written documents. Only data processing activities are carried out on the computer. For example, the company payroll system has all the processing on the computer, and all payroll records are kept in a database. Time cards are still used to collect the hours worked by employees, but the total hours that each employee works for the week are typed into the database from terminals at the end of a pay period.

The salespeople are paid a base rate plus commission. Each individual’s commission is calculated by collecting all the sales slips for an employee for a week. The commission is calculated as 10 percent of total sales (not including tax) and entered into the system from a terminal. Currently, the only record kept of the sales staff’s performance is a general work sheet containing the weekly commissions paid to each salesperson. Performance becomes important when considering whom to promote.

The company’s inventory system is even more antiquated. A card for each item is kept in an inventory book at the central warehouse. This card carries the name, inventory number, and the number of units on hand in the central warehouse. When inventory control ships items out of the warehouse to stores, the number of units on hand is reduced on the card, manually. If Richard Kelly, the inventory manager, wants to know the actual number of units the company has of a particular item, the item card must be checked for the number of units in the warehouse, and then each store must be called to find the number of units held in shelf inventory. In addition, Richard Kelly has no control over the number of units carried as shelf inventory at the stores. Rachel Burton, vice president for operations, and the store managers set the allowable levels of the shelf inventory.

The shelf inventory levels of the items carried by each store are checked weekly against the reorder information carried in the master inventory book. When inventory for a item falls below the reorder point, the item’s inventory card is tagged, and the buyer for that item is notified. If the item is to be reordered, the buyer sends a purchase order to the supplier and a copy to the Finance Department. At the end of each quarter, the buyers receive a list from the Inventory Department of the items below the reorder point and tell the Inventory Department which items to drop from inventory.

When a shipment of items arrives at the central warehouse in Bridgton, the inventory clerks check each order against the enclosed invoice and send copies of the invoice to both the item buyer and the Finance Department. If the item received already has a card that has been tagged, a new card is created with the new inventory count. If the item is new, a card is created for it, and the store managers are notified that the item is in the warehouse.

The system runs fairly smoothly, but several problems have cost the company money. Most of them have occurred because the company never knows exactly what its inventory is for a given item. Items have had high actual inventory counts long after the buyer has said the item should be dropped, thus increasing cash tied up in inventory and causing losses when inventory is sold in the end-of-season sales. Sales have been lost because items were unavailable or were available at one store but not another. Regularly, items in the warehouse are found to be out of stock.This results in lots of small backorders before completing the sale. Finally, items have come into the central warehouse unexpectedly, with no room available to store them. The unexpected inventory required Richard Kelly to either send other items to the stores to make room or rent additional warehouse space on a short-term basis.

1. Download the data file for this exercise

2. Follow the instructions in the data file to build a PIECES framework template

3. Use the PIECES framework to analyze problems with the iElectronics inventory and sales system. Make assumptions if necessary. In your opinion, which problem must be fixed first? Why?