Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Deliverable #1 The Operating Budget (Week 3) Input the sales volume, as projected by your Field Sales managers. Input your forecast product mix, as provided

Deliverable #1

The Operating Budget (Week 3)

  1. Input the sales volume, as projected by your Field Sales managers.
  2. Input your forecast product mix, as provided by the Marketing department.
  3. Input the efficiency rate for your direct labor, as projected by the VP of Manufacturing.
  4. Input the sales commission, as provided by the VP of Sales and the department's fixed costs.
  5. Input the administrative fixed costs as provided by the CFO.
  6. Calculate and comment on the break-even point.
  7. Calculate the sales volume needed to reach a target profit and comments.

The Cash Budget (Week 3)

  1. If needed, copy the sales from the first quarter operating budget.
  2. Using an aging of the collection period, as provided by the credit manager, forecast cash receipts by month.
  3. Calculate the cash disbursements by month.
  4. Determine the timing of a capital expenditure.
  5. Determine the timing and amount of financing.

Deliverable #2

Performance Evaluation (Week 5)

  1. Given actual results and the operating budgeted rates, prepare a flexible budget for 1 month.
  2. Explain deviations from plan.

Incremental Analysis: Do We Outsource? (Week 5)

  1. Using the operating budget for the quarter, prepare the relevant costs for in-house production.
  2. Given avoidable costs, calculate whether the outsourcing decision will save costs in total.

*EDIT* The Operating and Cash Budgets

Background: The Business Plan

  • Marketing: You have a segmentation analysis of the lifestyle variables for the adult learners market. The results support the need to invigorate and refuel with comfort food that is also nourishing. What better choice for adults than pizza? What will fill this need and differentiate Buzz-Time Pizza is the caffeinated tomato sauce.
  • Operations: You and a couple of friends will make, bake, and deliver made-to-order pizzas in two sizes. There is room in a garage for refrigeration, preparation tables, and a wood-fired oven. The sales manager will make all deliveries in his or her vehicle.
  • Financing: Clearly, this is a long-term investment. The three founders, who will each contribute an equal amount, have agreed not to draw a salary for 3 years. You have hired a sales manager and a part-time office manager.

The Operating Budget

Input the sales volume, as projected by your Field Sales managers. The sales manager has provided the following sales estimates and price points for the first quarter of 2020. Input these values in the worksheet labelled Operating Budget.

January

February

March

Number of pizzas sold

40,000

42,500

45,000

Sales Price for Large: $10.50

Sales Price for Medium: $8.50

Sales Estimates and Price Points for the First Quarter of 2020

  1. Input your forecast product mix, as provided by the Marketing Department. There is much debate as to the probable sales mix between the two sizes. Input a product mix of your choice (make sure these two add up to 100%).
  2. Input the efficiency rate for your direct labor, as projected by the VP of Manufacturing. Direct labor consists of your chef, Pepe, who has agreed to a low wage as long as cigarette and bathroom breaks are included. DO NOT CHANGE THIS LABOR RATE. However, you must calculate the chef's efficiency rate. Input your standard rate of how many minutes it will take to make one pizza from order to box.
  3. Input the sales commission, as provided by the VP of Sales and the department's fixed costs. Ingredient and material costs, based on quotes after an extensive request for proposal (RFP), are good for two years. DO NOT CHANGE THE DIRECT MATERIAL RATES. The only variable selling cost is a sales commission based on a percent of sales revenue. Input the percent you wish to pay your sales manager.
  4. Input the administrative fixed costs as provided by the CFO. Selling expenses include a fixed component for the sales manager's salary and car allowance for any company business use of their personal auto. Input the monthly salary you wish to pay for compensation.
  5. Calculate the break-even sales.
  6. Calculate sales volume needed to make $200,000 in net income.

The Cash Budget

  1. If needed, copy the sales from the first quarter operating budget. Ensure that the sales revenues for January through March is the same as that on the operating budget.
  2. Using an aging of the collection period, as provided by the credit manager, forecast cash receipts by month. Calculate total cash receipts for January through March. For each month's cash disbursements, you are to assume that half of the direct material payments are from the current month's purchases and half are from the prior month's purchases. All selling and administrative expenses are paid in the month incurred.
  3. Calculate the forecast direct material cash payments by month.
  4. Determine the timing of a capital expenditure. You are to decide in which month to invest a significant amount of cash in a facility expansion.
  5. Determine the timing and amount of financing. You are also to decide on the timing and amount of financing that will cover any month in which you do not meet the compensating cash balance requirement.

Performance Evaluation

The data for the February master budget columns should come over from the operating budget worksheet. The corrected operating budget worksheet will be provided. Please do not use your submission from Deliverable #1. Verify that the master budget Net Income is the same as that reported in Sheet #1. Complete a flexible budget for February, showing what net income should have been using the operating budget revenue rate, variable expenses' rates, and fixed costs.

  1. Given actual results and the operating budgeted rates, prepare a flexible budget for 1 month. Actual operating results for the month are provided. DO NOT CHANGE THIS DATA.
  2. Calculate the variances between the flexible budget and actual results, as being either F for favorable or U for unfavorable. Determine how much of the Net Income variance was due to volume and how much was rate-related. Explain deviations from plan. How would you evaluate the actual results? What steps would you take to further investigate and possibly adjust your budget for the rest of the year?

Incremental Analysis: Do We Outsource?

  1. Given the relevant costs from the operating budget, prepare a worksheet comparing the relevant data to a vendor's price quote for doing the production currently done in-house.
  2. Given avoidable costs, calculate whether the outsourcing decision will save costs in total. Provide an opinion as to whether this business deal is acceptable. Are there any nonfinancial considerations?

image text in transcribedimage text in transcribed

| + Screenshot 1 L 4. ? PivulTable Recommended Table Pivot Tables Tablas Online Pictures Shapes SmartArt illustration: I Get Add-ins My Add-ins - JI Eq- Syr Pictures Recommended Charts 30 Pive Chat .. Slicel Timeline . Link Line Column Wirt lass Siparklines Text Box Header & Footer Map Add-ins Chats Links N24 G --...............H LI KLM 1 Prepare a Master Operating Budget for the First Quarter DLEF --- Buzz Pizza Target Market: adult learners, timestarved, poor time management lan Feb April Total Qtr 5 Sales : 6 Units (B) Wgtd. average Price/unit $ Average Sales Price/unit (A) 1 Sales 10.50 8.50 Sales Price (all 2 topping): Large $ Medium $ Total Sales Revenue (A) Input the p (B) input the fa (c) input then (D) Input the sa (E) input these (F) input theo (G) Calculate to (H) Calculate to $200,000 2.55 $ 2.55 $ 2.55 13 Direct Labor: (C) efficiency rate as minutes per pizza hourly latxor cost plus fringe benefits $ Total direct labor per pizza $ 2.55 $ 12.00 . $ 10 Variable Costs (stated as per unit) 11 Production $ 12 Selling Total Variable Costs per unit $ Contribution Margin CM per unit $ 16 CM Ratio 17 Fixed Costs 18 Selling (E) 19 Administration (F) Total Fixed Costs $ 2.55 $ . S (2.55) $ IV/! 2.55 - (2.551 #DIV/0! (2.55) $ IV/AI D Direct Materials: $ Dough cheese toppings caffinated tomato sauce Boxes Total direct material per pizza $ . S 0.25 0.50 0.75 1.00 0.05 2.55 S . $ Net Income $ Breakeven Point in sales units Sales comissions (D) Total Variable Selling Expenses per pizza S . 26 Sales units for a Target Profit of $200,000 27 + 29 Operating Budget Cash Budget Variances Decisions Capital Select destination and press ENTER ar choose Faste a Search the web and your PC ch the web and your PC Display Sectings = h - - Pictures ES Slice Timeline My Add-ins - 3 Shapes Smart serat ons Pivot Tables Returnered Tables Pivolatiles lables Link PivoCharl - Recorriended Chants 3D Map lours Line Colurriri Wind Luss Sparklines Text Header Bok & Fuole Text Add-ins Charts 121 - X fx D E G H I J K L M N O 1 Prepare a Cash Budget B C ACTUAL Nov Dec $ 300,000 $ 200,000 $ F BUDGET March Jan Feb Total Qur - $ - $ - $ - Cash Receipts 4 Sales Revenues 5 Cash Receipts from: 6 2 months ago (10%) 7 1 month ago (60% 8 current month (25%) total cash receipts 10 Cash Disbursements 11 Raw Materials 12 Selling Expenses 13 Administrative Expenses 14 total disbursements 15 Net Operating Cash (A) Calculate, based on the collection history, the cash receipts from customers (B) Calculate the cash disbursements for raw material purchases, assumine 1/2 of the previous month's purchases are paid in the current month Total Purchases December January February March #DIV/0! $ - $ $ - $ . $ . $ (C) Decide in which month you will make a capital investment of $300,000 (D) Determine the appropriate financing activities so as to keep at least the required minimum cash balance of $ 75,000 and payoff any amount borrowed. - . 5 $ . 16 17 Investrients: 18 Expand Business 19 20 Financing: 21 New Debt 22 Repay debt $ 24 Net Cash Flow 25 Beginning Cash Balance 26 Ending Cash Balance - $ 75,000 75,000 $ - $ 75,000 75,000 $ . $ 75,000 75,000 $ 75,000 75,000 $ 28 THE MINIMUM CASH BALANCE IS $ 75,000 Operating Budget Cash Budget Variances Decisions Capital + Display Settings Search the web and your PC OD 9 e | + Screenshot 1 L 4. ? PivulTable Recommended Table Pivot Tables Tablas Online Pictures Shapes SmartArt illustration: I Get Add-ins My Add-ins - JI Eq- Syr Pictures Recommended Charts 30 Pive Chat .. Slicel Timeline . Link Line Column Wirt lass Siparklines Text Box Header & Footer Map Add-ins Chats Links N24 G --...............H LI KLM 1 Prepare a Master Operating Budget for the First Quarter DLEF --- Buzz Pizza Target Market: adult learners, timestarved, poor time management lan Feb April Total Qtr 5 Sales : 6 Units (B) Wgtd. average Price/unit $ Average Sales Price/unit (A) 1 Sales 10.50 8.50 Sales Price (all 2 topping): Large $ Medium $ Total Sales Revenue (A) Input the p (B) input the fa (c) input then (D) Input the sa (E) input these (F) input theo (G) Calculate to (H) Calculate to $200,000 2.55 $ 2.55 $ 2.55 13 Direct Labor: (C) efficiency rate as minutes per pizza hourly latxor cost plus fringe benefits $ Total direct labor per pizza $ 2.55 $ 12.00 . $ 10 Variable Costs (stated as per unit) 11 Production $ 12 Selling Total Variable Costs per unit $ Contribution Margin CM per unit $ 16 CM Ratio 17 Fixed Costs 18 Selling (E) 19 Administration (F) Total Fixed Costs $ 2.55 $ . S (2.55) $ IV/! 2.55 - (2.551 #DIV/0! (2.55) $ IV/AI D Direct Materials: $ Dough cheese toppings caffinated tomato sauce Boxes Total direct material per pizza $ . S 0.25 0.50 0.75 1.00 0.05 2.55 S . $ Net Income $ Breakeven Point in sales units Sales comissions (D) Total Variable Selling Expenses per pizza S . 26 Sales units for a Target Profit of $200,000 27 + 29 Operating Budget Cash Budget Variances Decisions Capital Select destination and press ENTER ar choose Faste a Search the web and your PC ch the web and your PC Display Sectings = h - - Pictures ES Slice Timeline My Add-ins - 3 Shapes Smart serat ons Pivot Tables Returnered Tables Pivolatiles lables Link PivoCharl - Recorriended Chants 3D Map lours Line Colurriri Wind Luss Sparklines Text Header Bok & Fuole Text Add-ins Charts 121 - X fx D E G H I J K L M N O 1 Prepare a Cash Budget B C ACTUAL Nov Dec $ 300,000 $ 200,000 $ F BUDGET March Jan Feb Total Qur - $ - $ - $ - Cash Receipts 4 Sales Revenues 5 Cash Receipts from: 6 2 months ago (10%) 7 1 month ago (60% 8 current month (25%) total cash receipts 10 Cash Disbursements 11 Raw Materials 12 Selling Expenses 13 Administrative Expenses 14 total disbursements 15 Net Operating Cash (A) Calculate, based on the collection history, the cash receipts from customers (B) Calculate the cash disbursements for raw material purchases, assumine 1/2 of the previous month's purchases are paid in the current month Total Purchases December January February March #DIV/0! $ - $ $ - $ . $ . $ (C) Decide in which month you will make a capital investment of $300,000 (D) Determine the appropriate financing activities so as to keep at least the required minimum cash balance of $ 75,000 and payoff any amount borrowed. - . 5 $ . 16 17 Investrients: 18 Expand Business 19 20 Financing: 21 New Debt 22 Repay debt $ 24 Net Cash Flow 25 Beginning Cash Balance 26 Ending Cash Balance - $ 75,000 75,000 $ - $ 75,000 75,000 $ . $ 75,000 75,000 $ 75,000 75,000 $ 28 THE MINIMUM CASH BALANCE IS $ 75,000 Operating Budget Cash Budget Variances Decisions Capital + Display Settings Search the web and your PC OD 9 e

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Options Futures And Other Derivatives

Authors: John C. Hull

3rd Edition

0131864793, 9780306457555

More Books

Students also viewed these Finance questions