Deliverable 5 - Interpreting Key Performance Indicators Deliverable 5 - Interpreting Key Performance Indicators
paula castro
FIN4489-Scenario-7-Data.xlsxBudget Prior 12 Months
| Warehouse Distribution Budget | (Warehouse budget covers operating expenses only; product sales, corporate expenses such as taxes, etc. are covered by other company departments.) | |||||||||||||||
| Year Ending December 31 | Formulas | |||||||||||||||
| Statement in U.S. Dollars | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Budget | ||||
| Expenses | January | February | March | April | May | June | July | August | September | October | November | December | Total Year | Comments | ||
| Warehouse Operations | ||||||||||||||||
| Receiving Team | ||||||||||||||||
| Employee Wages Including Overtime | 7,785 | 7,455 | 7,556 | 7,069 | 7,313 | 7,042 | 7,293 | 6,825 | 7,562 | 7,556 | 7,581 | 7,324 | 88,361 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 1,946 | 1,864 | 1,889 | 1,767 | 1,828 | 1,761 | 1,823 | 1,706 | 1,891 | 1,889 | 1,895 | 1,831 | 22,090 | See Staffing worksheet | ||
| Stocking Team | ||||||||||||||||
| Employee Wages Including Overtime | 19,811 | 18,969 | 19,230 | 18,250 | 18,609 | 18,369 | 17,969 | 18,369 | 19,245 | 18,230 | 18,748 | 18,868 | 224,667 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 4,953 | 4,742 | 4,808 | 4,563 | 4,652 | 4,592 | 4,492 | 4,592 | 4,811 | 4,558 | 4,687 | 4,717 | 56,167 | See Staffing worksheet | ||
| Receiving and Stocking Operations | ||||||||||||||||
| Equipment Rentals | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 36,000 | 1 warehouse forklift for every 2 receiving and stocking employees @ $500 per month per truck | ||
| Picking Team | ||||||||||||||||
| Employee Wages Including Overtime | 31,683 | 27,777 | 30,752 | 28,769 | 29,761 | 27,777 | 28,944 | 28,333 | 30,777 | 30,752 | 30,784 | 29,629 | 355,738 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 7,921 | 6,944 | 7,688 | 7,192 | 7,440 | 6,944 | 7,236 | 7,083 | 7,694 | 7,688 | 7,696 | 7,407 | 88,935 | See Staffing worksheet | ||
| Packing Team | ||||||||||||||||
| Employee Wages Including Overtime | 59,648 | 52,294 | 57,898 | 54,162 | 56,030 | 52,294 | 52,791 | 52,294 | 57,944 | 57,898 | 54,529 | 54,876 | 662,658 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 14,912 | 13,074 | 14,475 | 13,541 | 14,008 | 13,074 | 13,198 | 13,074 | 14,486 | 14,475 | 13,632 | 13,719 | 165,665 | See Staffing worksheet | ||
| Picking and Packing Operations | ||||||||||||||||
| Equipment Rentals | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 25,200 | 1 order picker forklift for every 2 picking employees @ $300 per month per truck | ||
| Equipment Depreciation | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 12,000 | $1,000 per month continues in new year | ||
| Supplies - Customer Shipments | 100,623 | 90,104 | 97,669 | 91,589 | 94,519 | 89,247 | 90,529 | 89,534 | 97,747 | 96,823 | 94,459 | 94,500 | 1,127,343 | See General Info for rate per order | ||
| Third Party Shipping (UPS, FedEx) | 536,654 | 480,555 | 520,901 | 488,474 | 504,101 | 475,984 | 482,820 | 477,513 | 521,316 | 516,388 | 503,780 | 504,000 | 6,012,486 | See General Info for rate per order | ||
| Sub-Total Expenses | 792,036 | 709,878 | 768,965 | 721,476 | 744,361 | 703,184 | 713,195 | 705,423 | 769,573 | 762,356 | 743,892 | 742,971 | 8,877,308 | |||
| Warehouse General & Administrative | ||||||||||||||||
| Rent | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 240,000 | Remains fixed in new year; warehouse has capacity for 50% growth in volume | ||
| Insurance | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 80,000 | Remains fixed in new year | ||
| Repairs and Maintenance | 5,650 | 2,800 | 8,820 | 1,000 | 18,270 | Varies based on equipment | ||||||||||
| Utilities | 1,840 | 1,693 | 1,803 | 1,858 | 1,877 | 1,849 | 1,950 | 1,987 | 1,822 | 1,766 | 1,748 | 1,900 | 22,094 | Average of $1,840 per month | ||
| Licenses and Fees | 12,000 | 12,000 | Remains fixed in new year | |||||||||||||
| Administrative and Management Salaries | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 374,200 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 11,338 | 11,338 | 11,338 | 11,338 | 11,338 | 11,338 | 11,338 | 11,338 | 11,338 | 11,338 | 11,338 | 11,105 | 135,827 | See Staffing worksheet | ||
| Miscellaneous | 368 | 55 | 147 | 530 | 222 | 500 | 1,822 | |||||||||
| Shrinkage | 23,628 | 23,628 | 23,628 | 23,628 | 23,628 | 23,628 | 23,628 | 23,628 | 23,628 | 23,628 | 23,628 | 23,628 | 283,541 | See Performance Metrics for % and General Info for values | ||
| Supplies - Office | 35 | 20 | 88 | 45 | 76 | 10 | 150 | 22 | 65 | 100 | 611 | |||||
| Sub-Total Expenses | 95,060 | 94,530 | 100,413 | 106,822 | 97,539 | 94,742 | 95,307 | 94,804 | 103,608 | 94,605 | 94,852 | 96,083 | 1,168,364 | |||
| Net Operating Expense | 887,095 | 804,407 | 869,378 | 828,298 | 841,900 | 797,925 | 808,502 | 800,227 | 873,181 | 856,961 | 838,743 | 839,054 | 10,045,673 | |||
| Beginning of Year Budget | 10,000,000 | |||||||||||||||
| Variance | (45,673) | |||||||||||||||
| Variance % | -0.46% |
Staffing Prior 12 Months
| Prior Year Staffing by Team | Monthly Salary | Hourly Rate | Overtime Rate | Avg Hours per Month | Avg OT Hours per Month | Bonus % | Benefits % | Comments | |
| Receiving Team 1 | $ 13.50 | $ 20.25 | 173.3 | 5.0 | 5% | 20% | Annual raises for the receiving team have averaged 4% in each of the past 3 years | ||
| Receiving Team 2 | $ 13.50 | $ 20.25 | 173.3 | 5.0 | 5% | 20% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | ||
| Receiving Team 3 | $ 13.50 | $ 20.25 | 173.3 | 5.0 | 5% | 20% | |||
| Receiving Team Open | $ - 0 | 173.3 | 5.0 | 5% | 20% | ||||
| Receiving Team Open | $ - 0 | 173.3 | 5.0 | 5% | 20% | ||||
| Stocking Team 1 | $ 12.50 | $ 18.75 | 173.3 | 2.0 | 5% | 20% | Annual raises for the stocking team have averaged 3% in each of the past 3 years | ||
| Stocking Team 2 | $ 14.00 | $ 21.00 | 173.3 | 2.0 | 5% | 20% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | ||
| Stocking Team 3 | $ 14.00 | $ 21.00 | 173.3 | 2.0 | 5% | 20% | |||
| Stocking Team 4 | $ 13.50 | $ 20.25 | 173.3 | 2.0 | 5% | 20% | |||
| Stocking Team 5 | $ 13.50 | $ 20.25 | 173.3 | 2.0 | 5% | 20% | |||
| Stocking Team 6 | $ 13.50 | $ 20.25 | 173.3 | 2.0 | 5% | 20% | |||
| Stocking Team 7 | $ 13.00 | $ 19.50 | 173.3 | 2.0 | 5% | 20% | |||
| Stocking Team 8 | $ 13.00 | $ 19.50 | 173.3 | 2.0 | 5% | 20% | |||
| Stocking Team Open | $ - 0 | 173.3 | 2.0 | 5% | 20% | ||||
| Stocking Team Open | $ - 0 | 173.3 | 2.0 | 5% | 20% | ||||
| Stocking Team Open | $ - 0 | 173.3 | 2.0 | 5% | 20% | ||||
| Picking Team 1 | $ 11.00 | $ 16.50 | 173.3 | 6.0 | 5% | 20% | Annual raises for the picking team have averaged 3% in each of the past 3 years | ||
| Picking Team 2 | $ 10.00 | $ 15.00 | 173.3 | 6.0 | 5% | 20% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | ||
| Picking Team 3 | $ 9.50 | $ 14.25 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 4 | $ 10.50 | $ 15.75 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 5 | $ 11.00 | $ 16.50 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 6 | $ 10.50 | $ 15.75 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 7 | $ 11.50 | $ 17.25 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 8 | $ 11.00 | $ 16.50 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 9 | $ 11.00 | $ 16.50 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 10 | $ 13.50 | $ 20.25 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 11 | $ 10.50 | $ 15.75 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 12 | $ 14.50 | $ 21.75 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 13 | $ 14.00 | $ 21.00 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team 14 | $ 14.00 | $ 21.00 | 173.3 | 6.0 | 5% | 20% | |||
| Picking Team Open | $ - 0 | 173.3 | 6.0 | 5% | 20% | ||||
| Picking Team Open | $ - 0 | 173.3 | 6.0 | 5% | 20% | ||||
| Picking Team Open | $ - 0 | 173.3 | 6.0 | 5% | 20% | ||||
| Picking Team Open | $ - 0 | 173.3 | 6.0 | 5% | 20% | ||||
| Packing Team 1 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 20% | Annual raises for the packing team have averaged 2% in each of the past 3 years | ||
| Packing Team 2 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 20% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | ||
| Packing Team 3 | $ 11.00 | $ 16.50 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 4 | $ 11.50 | $ 17.25 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 5 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 6 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 7 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 8 | $ 12.00 | $ 18.00 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 9 | $ 11.00 | $ 16.50 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 10 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 11 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 12 | $ 14.50 | $ 21.75 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 13 | $ 14.00 | $ 21.00 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 14 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 15 | $ 14.50 | $ 21.75 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 16 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 17 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 18 | $ 13.00 | $ 19.50 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 19 | $ 14.50 | $ 21.75 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 20 | $ 13.00 | $ 19.50 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 21 | $ 12.00 | $ 18.00 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 22 | $ 13.00 | $ 19.50 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 23 | $ 11.00 | $ 16.50 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team 24 | $ 11.00 | $ 16.50 | 173.3 | 4.0 | 5% | 20% | |||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 20% | ||||
| Receiving Team Lead | $ 3,333.33 | 10% | 20% | Annual raises for the administrative and lead staff have averaged 4% in each of the past 3 years | |||||
| Stocking Team Lead | $ 3,683.33 | 10% | 20% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | |||||
| Picking Team Lead | $ 3,166.67 | 10% | 20% | ||||||
| Packing Team Lead 1 | $ 3,333.33 | 10% | 20% | ||||||
| Packing Team Lead 2 | $ 3,000.00 | 10% | 20% | ||||||
| Lead or Manager Open | 10% | 20% | |||||||
| Administrative 1 | $ 3,000.00 | 10% | 20% | ||||||
| Warehouse Assistant Mgr | $ 5,000.00 | 20% | 25% | Annual raises for the management team have averaged 5% in each of the past 3 years | |||||
| Warehouse Manager | $ 6,666.67 | 20% | 25% | Benefits were 25% of wages in the prior year; HR has informed your group that this percent increases to 26% in the new year |
Performance Metrics
| Receiving and Stocking | Department (Prior Year) | Company Warehouse 2 | Industry Best | Industry Average | Description | |
| Receiving Cycle Time (Dock to Stock) in Hours | 3.0 | 3.2 | 1.9 | 12.6 | Time in hours from products being delivered at warehouse docks to stocking as available inventory in warehouse shelves for distribution | |
| Receiving Productivity (Cases per Person per Hour) | 50.0 | 52.0 | Time in hours for receiving tasks (processing receipts in computer system, unloading deliveries, scanning/recording codes, and placing in holding areas) | |||
| Stocking Productivity (Cases per Person per Hour) | 20.0 | 19.5 | Time in hours for stocking tasks (pickup from holding areas then transfer to, unbox, and stock on designated shelving areas based on item types and sizes) | |||
| Receiving and Stocking Cost (Average Cost per Case) | $ 1.34 | Costs of employee labor and related benefits for receiving and stocking staff plus equipment costs related to processes (such as forklift leases) | ||||
| Picking and Packing | Department (Prior Year) | Company Warehouse 2 | Industry Best | Industry Average | Description | |
| Order Cycle Time (Order to Pick to Ship) in Hours | 9.2 | 8.0 | 3.8 | 15.0 | Time in hours from products being delivered at warehouse docks to stocking as available inventory in warehouse shelves for distribution | |
| Picking Productivity (Orders per Person per Hour) | 17.0 | 16.0 | Time in hours for picking tasks (retrieving inventory for order from designated shelving area and transferring to packing holding areas) | |||
| Packing Productivity (Orders per Person per Hour) | 15.0 | 16.0 | Time in hours for packing tasks (retrieve from pickup holding areas then pack in box, label and transfer to shipping vendor holding area plus process shipment details in computer system) | |||
| Picking and Packing Cost (Average Cost per Order) | $ 1.74 | Costs of employee labor and related benefits for picking and packing staff plus equipment costs related to processes (such as forklift leases); does not include packaging supply costs such as boxes and tape or third party shipping charges which vary based on customer order size and destination | ||||
| Order Picking Accuracy | 99.20% | 99.00% | 99.85% | 98.00% | # Accurate Orders / Total # of Orders = Order Picking Accuracy Inaccurate orders (shipped goods do not match customer order) result in customer returns and rework plus additional shipments | |
| Rate of Customer Returns (Damaged Goods) | 0.22% | 0.70% | 0.20% | 0.95% | # of Orders Returned Due to Damage / Total # of Orders Sent = Rate of Return (Damaged Goods) | |
| Shrinkage % of Inventory Value (Theft, Breakage/Damage, Other) | 2.00% | 1.00% | 0.50% | 1.50% | Value of Inventory Missing or Unsellable / Total Value of Inventory = Shrinkage Rate |
Efficiency Project Options
| Option 1 - Purchase Pick-t- Ship Conveyor Lines | Description | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | Terminal Value | |||
| Start-up delay | 1 month | Productivity savings (estimated) | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | ||||
| Number of conveyor lines | 3 | Equipment purchase and installation | $ (120,000) | $ 98,000 | |||||||||||||
| Purchase / install per line | $40,000 | Depreciation adjustment for equipment | $ (2,000) | $ (2,000) | $ (2,000) | $ (2,000) | $ (2,000) | $ (2,000) | $ (2,000) | $ (2,000) | $ (2,000) | $ (2,000) | $ (2,000) | $ (98,000) | |||
| Deprecation life | 5 years | Income tax | $ - 0 | $ (1,827) | $ (1,827) | $ (1,827) | $ (1,827) | $ (1,827) | $ (1,827) | $ (1,827) | $ (1,827) | $ (1,827) | $ (1,827) | $ (1,827) | $ 19,600 | ||
| Net cash flow excl depreciation | $ (120,000) | $ 9,306 | $ 9,306 | $ 9,306 | $ 9,306 | $ 9,306 | $ 9,306 | $ 9,306 | $ 9,306 | $ 9,306 | $ 9,306 | $ 9,306 | $ 117,600 | ||||
| Net present value | $ 80,848 | ||||||||||||||||
| Option 2 - Lease Pick-to-Ship Conveyor Lines | Description | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | Terminal Value | |||
| Start-up delay | 1 month | Productivity savings (estimated) | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | $ 11,133 | ||||
| Number of conveyor lines | 3 | Start-up installation costs | $ (90,000) | ||||||||||||||
| Start-up installation | $ 90,000 | Monthly equipment rental | $ (1,500) | $ (1,500) | $ (1,500) | $ (1,500) | $ (1,500) | $ (1,500) | $ (1,500) | $ (1,500) | $ (1,500) | $ (1,500) | $ (1,500) | ||||
| Monthly payment | $ 1,500 | Income tax | $ 18,000 | $ (1,927) | $ (1,927) | $ (1,927) | $ (1,927) | $ (1,927) | $ (1,927) | $ (1,927) | $ (1,927) | $ (1,927) | $ (1,927) | $ (1,927) | $ - 0 | ||
| Net cash flow | $ (72,000) | $ 7,706 | $ 7,706 | $ 7,706 | $ 7,706 | $ 7,706 | $ 7,706 | $ 7,706 | $ 7,706 | $ 7,706 | $ 7,706 | $ 7,706 | $ - 0 | ||||
| Net present value | $ 7,895 | ||||||||||||||||
| Company Factors (All Options) | Project description: The pick-to-ship conveyor lines will move items picked by the picking team from the shelving area direct to the packing/shipping area. The lines will reduce the distance that the picking team has to travel -- saving 30 seconds per pick. This potentially increases the picking productivity from 17.0 to 19.8 orders per hour. There is also a distance reduction for the packing team -- saving 20 seconds per order. This potentially increases the packing productivity from 15.0 to 16.4 orders per hour. | ||||||||||||||||
| Discount rate (monthly) | 1% | ||||||||||||||||
| Income tax rate | 20% | ||||||||||||||||
| Depreciation adjustments to income tax are calculated | Productivity savings estimates are based on prior year volumes; savings would likely be greater for new year volume increase | ||||||||||||||||
| on a straight line basis for this high level analysis | Project implementation is optional. If project is implemented, the manager must decide whether to purchase or lease. | ||||||||||||||||
Objectives & General Info
| Distribution Operations | Prior Year Values | New Year Targets | Comments | |
| Cases of Goods Received in Dock | 320,000 | 400,000 | Increase in warehouse activity based on expected increase in customer orders | |
| Customer Orders Shipped | 751,000 | 939,000 | Expecting a 25% increase in customer orders due to new products and expansion | |
| Average Inventory Value per Order | $ 18.50 | $ 20.35 | Expecting a 10% increase in value of customer orders due to pricing changes and order size | |
| Average Supply Cost per Order | $ 1.50 | $ 1.65 | Expecting a 10% increase in cost of boxes and other packing supplies due to order size | |
| Average Shipping Cost per Order | $ 8.00 | $ 8.64 | Expecting an 8% increase in cost of shipping customer orders due to order size and vendor fee inflation | |
| Performance Targets | Prior Year Values | New Year Targets | Comments | |
| Receiving and Stocking Cost (Average Cost per Case) | $ 1.34 | $ 1.32 | Company executives have set a target of 1% reduction in average cost | |
| Picking and Packing Cost (Average Cost per Order) | $ 1.74 | $ 1.73 | Company executives have set a target of 1% reduction in average cost | |
| Receiving and Stocking Cost (Average Cost per Case) | $ 1.09 | New year estimated value based on new budget and new target volumes | ||
| Picking and Packing Cost (Average Cost per Order) | $ 1.41 | New year estimated value based on new budget and new target volumes |
Budget New Year
| Warehouse Distribution Budget | (Warehouse budget covers operating expenses only; product sales, corporate expenses such as taxes, etc. are covered by other company departments.) | Formulas | (Update formulas and values as needed to cover new year receiving and order volumes) | |||||||||||||
| Year Ending December 31 | ||||||||||||||||
| Statement in U.S. Dollars | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | Budget | ||||
| Expenses | January | February | March | April | May | June | July | August | September | October | November | December | Total Year | Comments | ||
| Warehouse Operations | ||||||||||||||||
| Receiving Team | ||||||||||||||||
| Employee Wages Including Overtime | 7,324 | 7,324 | 7,324 | 7,324 | 7,324 | 7,324 | 7,324 | 7,324 | 7,324 | 7,324 | 7,324 | 7,324 | 87,885 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 1,977 | 1,977 | 1,977 | 1,977 | 1,977 | 1,977 | 1,977 | 1,977 | 1,977 | 1,977 | 1,977 | 1,977 | 23,729 | See Staffing worksheet | ||
| Stocking Team | ||||||||||||||||
| Employee Wages Including Overtime | 18,868 | 18,868 | 18,868 | 18,868 | 18,868 | 18,868 | 18,868 | 18,868 | 18,868 | 18,868 | 18,868 | 18,868 | 226,412 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 5,094 | 5,094 | 5,094 | 5,094 | 5,094 | 5,094 | 5,094 | 5,094 | 5,094 | 5,094 | 5,094 | 5,094 | 61,131 | See Staffing worksheet | ||
| Receiving and Stocking Operations | ||||||||||||||||
| Equipment Rentals | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 3,000 | 36,000 | 1 warehouse forklift for every 2 receiving and stocking employees @ $500 per month per truck | ||
| Picking Team | ||||||||||||||||
| Employee Wages Including Overtime | 29,629 | 29,629 | 29,629 | 29,629 | 29,629 | 29,629 | 29,629 | 29,629 | 29,629 | 29,629 | 29,629 | 29,629 | 355,550 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 8,000 | 8,000 | 8,000 | 8,000 | 8,000 | 8,000 | 8,000 | 8,000 | 8,000 | 8,000 | 8,000 | 8,000 | 95,999 | See Staffing worksheet | ||
| Packing Team | ||||||||||||||||
| Employee Wages Including Overtime | 54,876 | 54,876 | 54,876 | 54,876 | 54,876 | 54,876 | 54,876 | 54,876 | 54,876 | 54,876 | 54,876 | 54,876 | 658,512 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 14,817 | 14,817 | 14,817 | 14,817 | 14,817 | 14,817 | 14,817 | 14,817 | 14,817 | 14,817 | 14,817 | 14,817 | 177,798 | See Staffing worksheet | ||
| Picking and Packing Operations | ||||||||||||||||
| Equipment Rentals | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 2,100 | 25,200 | 1 order picker forklift for every 2 picking employees @ $300 per month per truck; possible project increase | ||
| Equipment Depreciation | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 12,000 | $1,000 per month continues in new year; possible project increase | ||
| Supplies - Customer Shipments | 129,113 | 129,113 | 129,113 | 129,113 | 129,113 | 129,113 | 129,113 | 129,113 | 129,113 | 129,113 | 129,113 | 129,113 | 1,549,350 | See General Info for rate per order | ||
| Third Party Shipping (UPS, FedEx) | 676,080 | 676,080 | 676,080 | 676,080 | 676,080 | 676,080 | 676,080 | 676,080 | 676,080 | 676,080 | 676,080 | 676,080 | 8,112,960 | See General Info for rate per order | ||
| Sub-Total Expenses | 951,877 | 951,877 | 951,877 | 951,877 | 951,877 | 951,877 | 951,877 | 951,877 | 951,877 | 951,877 | 951,877 | 951,877 | 11,422,526 | |||
| Warehouse General & Administrative | ||||||||||||||||
| Rent | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 20,000 | 240,000 | Remains fixed in new year; warehouse has capacity for 50% growth in volume | ||
| Insurance | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 6,667 | 80,000 | Remains fixed in new year | ||
| Repairs and Maintenance | - 0 | Varies based on equipment | ||||||||||||||
| Utilities | 1,840 | 1,840 | 1,840 | 1,840 | 1,840 | 1,840 | 1,840 | 1,840 | 1,840 | 1,840 | 1,840 | 1,840 | 22,080 | Average of $1,840 per month | ||
| Licenses and Fees | 12,000 | 12,000 | Remains fixed in new year | |||||||||||||
| Administrative and Management Salaries | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 31,183 | 374,200 | See Staffing worksheet | ||
| Employee Bonus and Benefit Programs | 11,612 | 11,612 | 11,612 | 11,612 | 11,612 | 11,612 | 11,612 | 11,612 | 11,612 | 11,612 | 11,612 | 11,612 | 139,344 | See Staffing worksheet | ||
| Miscellaneous | - 0 | |||||||||||||||
| Shrinkage | 31,848 | 31,848 | 31,848 | 31,848 | 31,848 | 31,848 | 31,848 | 31,848 | 31,848 | 31,848 | 31,848 | 31,848 | 382,173 | See Performance Metrics for % and General Info for values | ||
| Supplies - Office | - 0 | |||||||||||||||
| Sub-Total Expenses | 103,150 | 103,150 | 103,150 | 115,150 | 103,150 | 103,150 | 103,150 | 103,150 | 103,150 | 103,150 | 103,150 | 103,150 | 1,249,797 | |||
| Net Operating Expense | 1,055,027 | 1,055,027 | 1,055,027 | 1,067,027 | 1,055,027 | 1,055,027 | 1,055,027 | 1,055,027 | 1,055,027 | 1,055,027 | 1,055,027 | 1,055,027 | 12,672,323 | |||
| Prior Year Budget | 10,045,673 | |||||||||||||||
| Variance | (2,626,650) | |||||||||||||||
| Variance % | -26.15% |
Staffing New Year
| Prior Year Staffing by Team | Monthly Salary | Hourly Rate | Overtime Rate | Avg Hours per Month | Avg OT Hours per Month | Bonus % | Benefits % | Comments | Adjust wage rates, overtime hours, and bonus % as desired for new year | ||
| Receiving Team 1 | $ 13.50 | $ 20.25 | 173.3 | 5.0 | 5% | 22% | Annual raises for the receiving team have averaged 4% in each of the past 3 years | You can also add employees to cover new year volumes using open fields | |||
| Receiving Team 2 | $ 13.50 | $ 20.25 | 173.3 | 5.0 | 5% | 22% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | ||||
| Receiving Team 3 | $ 13.50 | $ 20.25 | 173.3 | 5.0 | 5% | 22% | |||||
| Receiving Team Open | $ - 0 | 173.3 | 5.0 | 5% | 22% | ||||||
| Receiving Team Open | $ - 0 | 173.3 | 5.0 | 5% | 22% | ||||||
| Stocking Team 1 | $ 12.50 | $ 18.75 | 173.3 | 2.0 | 5% | 22% | Annual raises for the stocking team have averaged 3% in each of the past 3 years | ||||
| Stocking Team 2 | $ 14.00 | $ 21.00 | 173.3 | 2.0 | 5% | 22% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | ||||
| Stocking Team 3 | $ 14.00 | $ 21.00 | 173.3 | 2.0 | 5% | 22% | |||||
| Stocking Team 4 | $ 13.50 | $ 20.25 | 173.3 | 2.0 | 5% | 22% | |||||
| Stocking Team 5 | $ 13.50 | $ 20.25 | 173.3 | 2.0 | 5% | 22% | |||||
| Stocking Team 6 | $ 13.50 | $ 20.25 | 173.3 | 2.0 | 5% | 22% | |||||
| Stocking Team 7 | $ 13.00 | $ 19.50 | 173.3 | 2.0 | 5% | 22% | |||||
| Stocking Team 8 | $ 13.00 | $ 19.50 | 173.3 | 2.0 | 5% | 22% | |||||
| Stocking Team Open | $ - 0 | 173.3 | 2.0 | 5% | 22% | ||||||
| Stocking Team Open | $ - 0 | 173.3 | 2.0 | 5% | 22% | ||||||
| Stocking Team Open | $ - 0 | 173.3 | 2.0 | 5% | 22% | ||||||
| Picking Team 1 | $ 11.00 | $ 16.50 | 173.3 | 6.0 | 5% | 22% | Annual raises for the picking team have averaged 3% in each of the past 3 years | ||||
| Picking Team 2 | $ 10.00 | $ 15.00 | 173.3 | 6.0 | 5% | 22% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | ||||
| Picking Team 3 | $ 9.50 | $ 14.25 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 4 | $ 10.50 | $ 15.75 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 5 | $ 11.00 | $ 16.50 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 6 | $ 10.50 | $ 15.75 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 7 | $ 11.50 | $ 17.25 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 8 | $ 11.00 | $ 16.50 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 9 | $ 11.00 | $ 16.50 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 10 | $ 13.50 | $ 20.25 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 11 | $ 10.50 | $ 15.75 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 12 | $ 14.50 | $ 21.75 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 13 | $ 14.00 | $ 21.00 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team 14 | $ 14.00 | $ 21.00 | 173.3 | 6.0 | 5% | 22% | |||||
| Picking Team Open | $ - 0 | 173.3 | 6.0 | 5% | 22% | ||||||
| Picking Team Open | $ - 0 | 173.3 | 6.0 | 5% | 22% | ||||||
| Picking Team Open | $ - 0 | 173.3 | 6.0 | 5% | 22% | ||||||
| Picking Team Open | $ - 0 | 173.3 | 6.0 | 5% | 22% | ||||||
| Packing Team 1 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 22% | Annual raises for the packing team have averaged 2% in each of the past 3 years | ||||
| Packing Team 2 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 22% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | ||||
| Packing Team 3 | $ 11.00 | $ 16.50 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 4 | $ 11.50 | $ 17.25 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 5 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 6 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 7 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 8 | $ 12.00 | $ 18.00 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 9 | $ 11.00 | $ 16.50 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 10 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 11 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 12 | $ 14.50 | $ 21.75 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 13 | $ 14.00 | $ 21.00 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 14 | $ 13.50 | $ 20.25 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 15 | $ 14.50 | $ 21.75 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 16 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 17 | $ 12.50 | $ 18.75 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 18 | $ 13.00 | $ 19.50 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 19 | $ 14.50 | $ 21.75 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 20 | $ 13.00 | $ 19.50 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 21 | $ 12.00 | $ 18.00 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 22 | $ 13.00 | $ 19.50 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 23 | $ 11.00 | $ 16.50 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team 24 | $ 11.00 | $ 16.50 | 173.3 | 4.0 | 5% | 22% | |||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Packing Team Open | $ - 0 | 173.3 | 4.0 | 5% | 22% | ||||||
| Receiving Team Lead | $ 3,333.33 | 10% | 22% | Annual raises for the administrative and lead staff have averaged 4% in each of the past 3 years | |||||||
| Stocking Team Lead | $ 3,683.33 | 10% | 22% | Benefits were 20% of wages in the prior year; HR has informed your group that this percent increases to 22% in the new year | |||||||
| Picking Team Lead | $ 3,166.67 | 10% | 22% | ||||||||
| Packing Team Lead 1 | $ 3,333.33 | 10% | 22% | ||||||||
| Packing Team Lead 2 | $ 3,000.00 | 10% | 22% | ||||||||
| Lead or Manager Open | 10% | 22% | |||||||||
| Administrative 1 | $ 3,000.00 | 10% | 22% | ||||||||
| Warehouse Assistant Mgr | $ 5,000.00 | 20% | 26% | Annual raises for the management team have averaged 5% in each of the past 3 years | |||||||
| Warehouse Manager | $ 6,666.67 | 20% | 26% | Benefits were 25% of wages in the prior year; HR has informed your group that this percent increases to 26% in the new year |
