Deliverable 7 – Financial Analysis for Master Budget Planning

Assignment Content

  1. Competency
    • Interpret core managerial finance concepts and data.
    • Analyze operational budget drivers and variances.
    • Interpret financial decision-making criteria.
    • Evaluate financing strategies for business operations.
    • Evaluate operational performance using analytic and comparative techniques.
    • Devise risk mitigation strategies in financial management.
    • Student Success Criteria
      View the grading rubric for this deliverable by selecting the “This item is graded with a rubric” link, which is located in the Details & Information pane.

      Scenario
      You are a department manager for a distribution team that packages and ships products from a warehouse location to fulfill customer orders. Your company has been acquired by a larger firm. The new owners are requesting that each department manager prepare a master budget for the upcoming year and submit it for approval. The submission must include a written justification of the requested amount and at least one risk mitigation action to control or reduce costs.

      The information available to meet this request includes:

    • The department’s expenses, staffing, and output for the past 12 months;
    • Metrics, financial and operational, that can be used to compare the department’s performance and output to a department that provides similar distribution support to another division of the company;
    • One potential efficiency project with two available financing options;
    • Data on the company’s historic employee practices such as annual raises and bonuses; and
    • The level of output that the department must meet in the upcoming year based on the new owners’ sales goals.
    • Instructions
      Using the information provided, as well as relevant economic data researched independently, make decisions about:
    • The staffing level required to meet the expected output requirements;
    • The annual raises and bonuses that should be included in the budget;
    • Whether the efficiency project option should be implemented, and if so, using which financing option; and
    • The cost control (i.e. risk mitigation) action(s) for implementation.
    • From your decisions, prepare a master budget for the upcoming year in Excel. Then, prepare a written justification, memo-style, for the budget that discusses your decisions and the rationale for each. Include support for your decisions from your analysis of the data and the financial and operational metrics (historical and expected) as well as at least one external economic or industry source.

      In the justification memo, include one visual (chart, graph, etc.) created from the data. The purpose and type of visual selected should be based on the data being highlighted. Examples include a summary pie chart of the total budget, a bar chart comparing the department’s historical metrics to the expected metrics with the new budget, or costs across time to implement the efficiency project using the recommended financing options.

      Submit an Excel file with the new master budget and a justification memo with embedded visual.

Criterion 1

A – 4 – Mastery

Prepared a professional style master budget for the upcoming year in Excel; utilized all of the information provided for each decision.

Criterion 2

A – 4 – Mastery

Submitted an extensive memo-style justification for the budget that thoroughly discusses the decisions and the rationale for each.

Criterion 3

A – 4 – Mastery

Thoroughly communicates the support for the decisions from the analysis of the data, financial, and operational metrics (historical and expected) as well as at least one external economic or industry source.

Criterion 4

A – 4 – Mastery

Submitted a very professional, formatted Excel file with the new master budget and a justification memo with embedded visual; the type of visual selected was based on the data being highlighted.

,

Budget 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 &a




Who We Are


We are a professional custom writing website. If you have searched a question
and bumped into our website just know you are in the right place to get help in your coursework.

Do you handle any type of coursework?

Yes. We have posted over our previous orders to display our experience. Since
we have done this question before, we can also do it for you. To make sure we do
it perfectly, please fill our Order Form. Filling the order form correctly will assist
our team in referencing, specifications and future communication.

Is it hard to Place an Order?

1. Click on the “Place order tab at the top menu or “Order Now” icon at the
bottom and a new page will appear with an order form to be filled.

2. Fill in your paper’s requirements in the “PAPER INFORMATION” section
and click “PRICE CALCULATION” at the bottom to calculate your order
price.

3. Fill in your paper’s academic level, deadline and the required number of
pages from the drop-down menus.

4. Click “FINAL STEP” to enter your registration details and get an account
with us for record keeping and then, click on “PROCEED TO CHECKOUT
at the bottom of the page.

5. From there, the payment sections will show, follow the guided payment
process and your order will be available for our writing team to work on it.

Leave a Reply

Your email address will not be published.