The Operating and Cash Budgets

Hey team, I am having a little trouble with this budget. Help is greatly appreciated. A, B, C and D are already completed

The Operating Budget

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.

At the end of the first quarter, management desires to have any new debt for this expansion paid off (meaning a 0 balance).

Target Market: adult learners, timestarved, poor time management Jan Feb March Total Qtr Sales : Units (B) 127,500 Price/unit $ $ 40,000 9.50 $ 380,000 $ 42,500 9.50 $ 403,750 $ 45,000 9.50 427,500 $ Average Sales Price/unit (A) wgtd. % Sales average 10.50 50% $ 5.25 8.50 50% $ 4.25 100% $ 9.50 Total Sales Revenue Sales Price (all 2 topping): Large $ Medium $ 1,211,250 (A) Input the planned product mix (B) Input the forecast sale volume (C) Input the number of pizza made per hour (D) Input the sales commission as a % of revenue (E) Input the sales manager's salary (5) Input the office manager's salary (G) Calculate the breakeven point in sales units (H) Calculate the sales units needed to reach a $200,000 net income target, 5.55 719,738 5.55 $ 0.10 5.65 $ 154,200 $ 3.86 $ 40.6% 5.55 $ 0.10 5.65 $ 163,838 $ 3.86 $ 40.6% Direct Labor: (C) efficiency rate as minutes per pizza hourly labor cost plus fringe benefits $ Total direct labor per pizza $ Variable Costs (stated as per unit) Production $ Selling Total Variable Costs per unit $ Contribution Margin $ CM per unit $ CM Ratio Fixed Costs Selling (E) Administration (F) Total Fixed Costs S 0.10 5.65 173,475 $ 3.86 40.6% 15 12.00 3.00 491,513 $ $ - S - Direct Materials: Dough $ cheese toppings caffinated tomato sauce Boxes Total direct material per pizza $ 0.25 0.50 0.75 1.00 0.05 2.55 $ $ $ - Net Income $ 154,200 $ 163,838 $ 173,475 $ 491,513 110 Breakeven Point in sales units Sales comissions (D) Total Variable Selling Expenses per pizza $ 1% 0.10 Sales units for a Target Profit of $200,000 (H) Prepare a Cash Budget ACTUAL Nov Dec BUDGET March Jan Feb Total Otr $ 300,000 $200,000 $ 380,000 $ 403,750 $ 427,500 $ 1,211,250 $ Cash Receipts Sales Revenues Cash Receipts from: 2 months ago (10%) 1 month ago (60% current month (25%) total cash receipts Cash Disbursements Raw Materials Selling Expenses Administrative Expenses total disbursements Net Operating Cash (A) 38,000 242,250 106,875 387,125 $ (A) Calculate, based on the collection history, the cash receipts from customers (B) Calculate the cash disbursements for raw material purchases, assuming 1/2 of the previous month's purchases are paid in the current month Total Purchases December January February March $ 53,684 $ 102,000 $ 108,375 $ 114,750 38,000 242,250 302.813 583,063 95,000 95,000 $ 100.938 100,938 $ $ (B) 105,188 4,038 111,563 4,275 216,750 12,113 3,800 (C) Decide in which month you will make a capital investment ******* (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. $ S 3,800 $ 91,200 $ 109,225 $ (8,288) $ 115,838 $ 271,288 $ 228,863 354,200 Investments: Expand Business (C) Financing New Debt Repay debt (D) $ Net Cash Flow Beginning Cash Balance Ending Cash Balance 91,200 $ 75,000 166,200 $ (8,288) $ 166,200 157,913 $ 271,288 $ 157,913 429,200 $ 354,200 75,000 429,200 $ THE MINIMUM CASH BALANCE IS $ 75,000 Target Market: adult learners, timestarved, poor time management Jan Feb March Total Qtr Sales : Units (B) 127,500 Price/unit $ $ 40,000 9.50 $ 380,000 $ 42,500 9.50 $ 403,750 $ 45,000 9.50 427,500 $ Average Sales Price/unit (A) wgtd. % Sales average 10.50 50% $ 5.25 8.50 50% $ 4.25 100% $ 9.50 Total Sales Revenue Sales Price (all 2 topping): Large $ Medium $ 1,211,250 (A) Input the planned product mix (B) Input the forecast sale volume (C) Input the number of pizza made per hour (D) Input the sales commission as a % of revenue (E) Input the sales manager's salary (5) Input the office manager's salary (G) Calculate the breakeven point in sales units (H) Calculate the sales units needed to reach a $200,000 net income target, 5.55 719,738 5.55 $ 0.10 5.65 $ 154,200 $ 3.86 $ 40.6% 5.55 $ 0.10 5.65 $ 163,838 $ 3.86 $ 40.6% Direct Labor: (C) efficiency rate as minutes per pizza hourly labor cost plus fringe benefits $ Total direct labor per pizza $ Variable Costs (stated as per unit) Production $ Selling Total Variable Costs per unit $ Contribution Margin $ CM per unit $ CM Ratio Fixed Costs Selling (E) Administration (F) Total Fixed Costs S 0.10 5.65 173,475 $ 3.86 40.6% 15 12.00 3.00 491,513 $ $ - S - Direct Materials: Dough $ cheese toppings caffinated tomato sauce Boxes Total direct material per pizza $ 0.25 0.50 0.75 1.00 0.05 2.55 $ $ $ - Net Income $ 154,200 $ 163,838 $ 173,475 $ 491,513 110 Breakeven Point in sales units Sales comissions (D) Total Variable Selling Expenses per pizza $ 1% 0.10 Sales units for a Target Profit of $200,000 (H) Prepare a Cash Budget ACTUAL Nov Dec BUDGET March Jan Feb Total Otr $ 300,000 $200,000 $ 380,000 $ 403,750 $ 427,500 $ 1,211,250 $ Cash Receipts Sales Revenues Cash Receipts from: 2 months ago (10%) 1 month ago (60% current month (25%) total cash receipts Cash Disbursements Raw Materials Selling Expenses Administrative Expenses total disbursements Net Operating Cash (A) 38,000 242,250 106,875 387,125 $ (A) Calculate, based on the collection history, the cash receipts from customers (B) Calculate the cash disbursements for raw material purchases, assuming 1/2 of the previous month's purchases are paid in the current month Total Purchases December January February March $ 53,684 $ 102,000 $ 108,375 $ 114,750 38,000 242,250 302.813 583,063 95,000 95,000 $ 100.938 100,938 $ $ (B) 105,188 4,038 111,563 4,275 216,750 12,113 3,800 (C) Decide in which month you will make a capital investment ******* (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. $ S 3,800 $ 91,200 $ 109,225 $ (8,288) $ 115,838 $ 271,288 $ 228,863 354,200 Investments: Expand Business (C) Financing New Debt Repay debt (D) $ Net Cash Flow Beginning Cash Balance Ending Cash Balance 91,200 $ 75,000 166,200 $ (8,288) $ 166,200 157,913 $ 271,288 $ 157,913 429,200 $ 354,200 75,000 429,200 $ THE MINIMUM CASH BALANCE IS $ 75,000