Inventory Problems

The next logical step in this investigation may be to look at Nelson Building's inventory controls. In the construction business, raw materials are not only valuable, they can be sold easily and without leaving a sales trail (e.g., selling 2x4 wood beams is not as complicated as selling stolen microchips or medical supplies). Several years ago, during your previous investigation of Nelson Building, you uncovered several problems with the company's inventory controls. The inventory counts were not performed with much regularity, and the opportunities for employees to smuggle raw materials from company sites were rampant. You suspect the company has fallen back into old habits. However, when you discuss Nelson 7 Building's inventory with Mr. Nelson, he assures you the one thing he's kept a close eye on is inventory processes. "We run two inventory counts a month. Four surprise checks every month, sometimes five or six. You may remember, Sean, after your last consultation, we brought in Julia Mitchell as Materials Manager. Best person I've got. She misses nothing. Mitchell supervises inventory usage and control herself. Reports directly to me, and I'm telling you, we're on top of it," Nelson says. Questions:

11. Do you agree that NBC's inventory controls, as described by Nelson, are sound?

12. What inventory checks, if any, should you perform?

You decide to seek confirmation about the inventory yourself by conducting a physical count. That way you don't have it nagging at you, and you won't have to come back to this fundamental issue later. The results of your physical count of the inventory are found in the worksheet count.xls. Open the spreadsheet invent.xls. This worksheet lists the inventory as it is listed "on the books." Compare the figures in the spreadsheet file to the physical count you performed. Questions:

13. Is there an inventory shrinkage problem?

14. What's your next move?

Invoice Analysis

You decide to leave the inventory issue and head to the invoice records. The pinch on the profit margin has clearly been occurring over the past six months. This at least narrows your search. Using a data mining program (such as ACL or IDEA), you extract invoice information from the company database and export that data into a spreadsheet. This lets you manipulate the records in different ways. Hopefully something will stand out, or at least look curious enough to follow up on. Open the spreadsheet invoice.xls. You'll find a listing of invoices issued during the first six months of 2011. Questions: Hint: To total the amount of invoices for each vendor, go to the last line for each vendor. Choose the first blank cell to the right of the last invoice amount and then choose the "sum" junction from the toolbar or drop down menus. Next, drag the box to select the group of invoices you wish to total and then hit enter.

15. Sort the data by REQUISITION NUMBER. Is there anything remarkable in the sorted list?

16. Sort the data by INVOICE DATE. Do any red flags show up in the data sort?

17. Sort the data by VENDOR. Then write a formula to TOTAL the amount invoiced by each vendor. Put this formula in the cell to the right of the last entry for each vendor in the sorted list. Next, sort the data in descending order by the TOTAL AMOUNT INVOICED by each vendor. List the three vendors with the greatest total invoicing amounts below. Include the number of invoices each presented, as well as the total amount billed. Back to the Ranch

s cut Arial Copy Format B I U Paste 2 Nelson Building Company 3 Inventory Count 4 Date: 8/05/11 6 Lumber 2X2 2x4 2X6 2X8 2X12 4X4 6X6 Count 900 400 300 200 200 700 Count 3800 37 250 15 Electrical 16 Wire(ft.) Fuse Boxes Switches Boxes Miscellaneous 17 18 19 20 21 22 Roofing Materials 23 24 25 Shingles - 7735 Shingles 5978 Shingles 1125 Shingles - 097741 Shingles 59784 Shingle BLE589 Count 50 50 50 150 75 29 30 31 32 s cut Arial Copy Format B I U Paste 2 Nelson Building Company 3 Inventory Count 4 Date: 8/05/11 6 Lumber 2X2 2x4 2X6 2X8 2X12 4X4 6X6 Count 900 400 300 200 200 700 Count 3800 37 250 15 Electrical 16 Wire(ft.) Fuse Boxes Switches Boxes Miscellaneous 17 18 19 20 21 22 Roofing Materials 23 24 25 Shingles - 7735 Shingles 5978 Shingles 1125 Shingles - 097741 Shingles 59784 Shingle BLE589 Count 50 50 50 150 75 29 30 31 32 G/L Balance: 39,425.50 Nelson Building Company General Ledger Inquiry: Inventory Date: 8/06/11 Quantity Price Amount Lumber 925 2.00 1,850.00 402 4.00 1,608.00 299 4.50 1,345.50 5.00 990.00 88 7.00 616.00 490.00 200 6.00 1,200.00 693 7.50 5,197.50 0.00 3800 0.80 3,040.00 37 10.50 388.50 250 3.00 750.00 500 1.50 750.00 0.00 0.00 0.00 50 35.00 1,750.00 50 35.50 1,775.00 50 37.00 1,850.00 150 37.50 5,625.00 150 47.00 7,050.00 75 42.00 3,150.00 2X2 2X4 2x6 2X8 2X12 4X4 6X6 4X12 198 98 5.00 Electrical Wire(ft.) Fuse Boxes Switches Boxes Miscellaneous Roofing Materials Shingles - 7735 Shingles -5978 Shingles -1125 Shingles -097741 Shingles- 59784 Shingles BLE589 39,425.50