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.

Remember This

In Week 3, the first two deliverables involve preparing the necessary financial budgets in order to satisfy creditors and control business operations.

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.

Sales Estimates and Price Points for the First Quarter of 2020

	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

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.

   Remember This

   You are to look over this financial plan and make adjustments in the data you have input in order to obtain the most reasonable and not excessively optimistic results.

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.

- Excel Derry Moua - o ACCT301CourseProjec15predshes Tell me what you want to do x Share File ViewH elp Team > v san A1 9 Home Insert Page Layout Formulas Date Review Calitri V11 VA A -- Copy Format Painter BIUTA Clacard XR Ceneral 5 % Pasle , Normal Conditional Forma, as Good Formattingtable- Neutral KIK Delete Format Clear Morge & Centar - Wigment Sort & Find & Filter - Salart - Editing Colls N17 H K L M N O P 0. sh p ril Tatal citr 5 Sales: Average Sales Price/unit Units (BI (A) Sales watd. average Price'unit $ Total Sales Revenue Sales Price (all 2 topping): Large $ Medium $ 1050 (A) Inaut the parned praructii () Inaut the forecast sale volume (C) Ingut the number of pizza made aer hour (DJ Inaut the sales commission as a 's of revenue (E) Ingut the sales manager's salary (F) Inaut the office manager's salary (G] Calculate the bree kaven point in sales units CHI Calculate the sale units needed to reecha $200.000 net income target 2.55 $ 2.55 $ 2.55 Direct Lalo (C) etfidency rate as minutes per pizza hourly labor cos. plus fringe bereits $ Total direct labor per pizza $ 12.00 S 10 Variable Coats (stated as per unit 11 Production $ 12 Selling 13 Tolal Variable Couls ver uril $ 11 Contribution lvlar $ CM per unit $ 15 CM Ratio 17 -xod Cost: 19 Selling (E) 19 Administration ) Total Fixed Costs 2.55 $ - $ (2.551_$ OVO 2.55 $ - $ 2.551 $ #DIV/01 2.55 . 2.55) /01 Direct Materials: Laugh choose toapings caffinated tomato sauce Boxes Total direct material per altra 1.00 0.05 Net Income 24 Breakever Point in sales units Sales comissions in) Total Variable Selling Expenses per vicces 28 Salesurillor Tante Pruli ul $200.000 Operating Budget Cash Budget Variances Decisions Capital 4 Display Stics L I - 918 - Type here to search Oo9e 1017 PM A - Excel Derry Moua - o ACCT301CourseProjec15predshes Tell me what you want to do x Share ViewH elp Team >> Wrap Text Merge & Centar - Wigment v File Home Insert Page Layout Formulas Date Review X Cut | Calieri v1 v 4 8 = -- Bor Copy Paale Format Painter BIUTA Ciscard 121 - x x IT Ceneral 5 % Normal Conditional Forma, as Good Formatting table- Styles Neutral TIK Delete Founat san A1 9 Fill Sort & Find & Clear Filter - Salart - Editing K L M N O Q R S T W LA 1 Prepare a Cash Budget C TUAL Noi Doc $ 300,000 $ 200.000 S Feb an . BUDGET March . Ictal atr . 5 . Cash Receipts 4 Sales Revenues 5 Cast Receipts from: 2 months ago 10 i manth age GIY IAL Calculate, based on the collection History, the cash receipts from customes B) Calculate the cash disbursements fer raw material purchases, assuming 1/2 of the previous month's purchase are paid in the current month Total Purchases Der m any Fly Man ADIV/0! $ 9 u Lalchh receipts in Cruisbursements 11 Raw Materials 12 Seling Expenses 1) Adrinistrative perses 11 total disbursements 15 Net Operating Cash 16 17 Investirenls: 18 Capend Business ICI Decice in which month you wil make a capital investment of $300,000 ID Determine the appropriate fnancing actvities so as ta kees at least the required minimum cash talarce of 5 75.000 end payoff any amount borrowed. 20 Financing: 21 New Debt 24 Noi Cash Flux 25 Hepinning av al 96 Ending Cash Balan 75.000 75,000 $ 75.00 75,000 $ 75 OD 75,000 $ 75.000 75,000 $ 29 THE MINIMUM CASH BALANCES $ 2, 31 Opreating Budget Cash Budget Variances Decisions Capital Display Stics L I - 35% - Type here to search 10:17 PM 1/26/21320 - Excel Derry Moua - o ACCT301CourseProjec15predshes Tell me what you want to do x Share Insert ViewH elp Team File A Home X Cut > Page Layout Formulas Date Review Calitri 11 VA A -- BIUTA . v san A1 9 Copy Wrap Text Morge & Centar - Ceneral 5 % , Normal Conditional Forma, as Good Formatting Table Style Neutral KIK Delele Format Pale . Format Painter Cocard Fill Clear Sort & Find & Filter - Salart - Editing Alignment K L M N O P Q R S - 1 Prepare a flexible Budget 2 Conduct Variance Analysis Master Budget February per Unit Actual February per Unit Flex Budest Februaryper Unit Variances iflex vs. actual) Total per unit 6 Units 9.85 35,000 344.750 Total Sales Revenue $ (A) Verify that the net income is the same as the Op Budget (B) input the flex budget sales units and the per unit price (C) Input the flex budget pruduction cost per unit and calculate the total flex pruduction costs. (D) input the flex budget variable selling costs per unit (E) input the flex budget fxed selling and administration costs (F) Calculate the variances between the actual and flex budget (G) Input the spendina variance (H) Calculate the volume variance using the budgetted contribution inai $ $ S - 2.55 190,750 $ 9,850 2.55 $ 194,600 $ 5.45 0.11 5.56 (C) (D) (F) (F) $ . $ $ . 9 Variable costs: 10 Production 11 Selling 12 Total Variable Costs 13 Contribution Margin 15 15 Fixed Costs: 17 Selling 18 Administration 19 Total Fixed Costs . - $ $ $ $ . - $ $ $ '$ (2.551 $ 150,150 $ 4.29 $ 60,000 58,500 118.500 - (A) $ 31,650 21 Net Income $ 22 23 Master Bucaet vs. Actual Net Income Variance 24 what is the volume variance 25 What is the spending rate variance 25 $ $ 31,650 - 27 28 Opreating Budget Cash Burgrt Variances Decisions Capital Display Stics L I - - 100% - o Type here to search 9 L 10:17 PM 126210 e x - Excel Berry Moue - o ACCT301CourseProjec15predshes Tell me what you want to do x Share Insert ViewH elp Team File A Home X Cut Page Layout Formulas Date Review Calitri 11 VA A -- BIUTA . v san A1 9 Copy Wrap Text Morge & Centar - Ceneral 5 % Number Normal Conditional Forma, as Good Formatting table- Sytes Neutral Pusle Fontat Painter Cilacard TIK Delete Founat Fill Clear Sort & Find & Filter - Salart - Editing rrent B C D E F G H I J K L M N O P R S 1 Evaluate an outsourcine decision Master Budget Relevant Costs . (A) Outsource Relevant Costs Units to Produce 6 Variable: Costs stated as per unit) 7 Production & Selling Total Variable Costs per unit (A) Input the number of units to the produced same as the sales for the quarter) (B) from the master budget. input the variable costs per unit for production. (c) Frumri the master budget, input the variable custs per unit for selling (D) Input the total fixed costs from the master budget (E) Input the vendor's price: $ 7.50 (F) Input a $.05 incremental variable selling costs due to the outsourcing decision (G) Enter the total fixed costs that will NOT be avoided due to oursourcina Selling: $50,000 Adrrim $40,000 $ 11 Fixed Costs 72 Selling 13 Administration Total Fixed Custs 15 16 Total costs $ . Cost per unit produced' #DIV/0! #DIV/0! Oprating Budgrt Cash Burgrt Variances Decisions Capital Display Stics L I - L 10:17 PM 1262100 - 100% - Type here to search Derry Moua - o x Share Insert File A Home X Cut ACCT30 Course Projectpresteeds - Excel Tell me what you want to do Custoru Custom v Comma 5 % Corditional Forma as Currency Formatting Table - Sytes san u A1 9 Copy Comma l Currency to For Pusle . Fill- Clear ..., Page Layout Formulas Date Review ViewH elp Team Calieri 11 V A *-= Wc Text BIUTA Margeix Centar - Port X B C D E Yrar Year 1 Year 2 Year 3 (300,000) Format Painter ClShoard Sort & Find & Filter - Salart - Editing rrent 616 - X N O P 0 1 Prepare a discounted cash flow analysis F Year 4 G Year 5 H Yearfi I Year 7 J Year 8 K Year L Year 10 M Totals (300,000) 3 Investment 135,000 135,000 135,000 135,000 135.000 200.000 200.000 200.000 200.000 200.000 1.675,000 (50,000) (50,000) (135,000) (1,150,000) 195,000) (95,000) (95,000) (05,000) (95,000) (135,000) (135,000) (135,000) (135,000) 5 Cash Flows 6 Sales 7 Salvape value 8 Operating costs 9 Major Maintenance 10 Net Cash Flow 11 12 Cummulative CF 13 1200,000) 40,000 40,000 40,000 40,000 40,000 65,000 65,000 65,000 65,000 15,000 175,000 (300.000) (260,000) (220,000) (180,000) (140,000) (100,000) (35.000) 30,000 95,000 150,000 175,000 Hurdle Rate 5% (A) Use the Excel function=NPV in order to determine the value of the future cash flows 15 Net Present Value (=NPV) 17 18 Internal Rate of Return l-IRRI (B) Use the IRR function in order to determine the "interest" earned on the cash flows from year to year 10 20 Payback Period (C) Look at the cummulative cash flow. How long before the initial Investment is pald off? 22 Profitability Index (D) Calculate the ratio of the project's PV of cashflows from years 1-10/ Initial Investment Major Maintenance 5 8 5.000) (E) Decide in which year (5-7) this expenditure should be made. Add $5,000 for every year after year 5 Oprating Budgrt Cash Burgrt Variances Decisions Capital Display Stics L I - L 10:17 PM - 100% - Type here to search A - Excel Derry Moua - o ACCT301CourseProjec15predshes Tell me what you want to do x Share File ViewH elp Team > v san A1 9 Home Insert Page Layout Formulas Date Review Calitri V11 VA A -- Copy Format Painter BIUTA Clacard XR Ceneral 5 % Pasle , Normal Conditional Forma, as Good Formattingtable- Neutral KIK Delete Format Clear Morge & Centar - Wigment Sort & Find & Filter - Salart - Editing Colls N17 H K L M N O P 0. sh p ril Tatal citr 5 Sales: Average Sales Price/unit Units (BI (A) Sales watd. average Price'unit $ Total Sales Revenue Sales Price (all 2 topping): Large $ Medium $ 1050 (A) Inaut the parned praructii () Inaut the forecast sale volume (C) Ingut the number of pizza made aer hour (DJ Inaut the sales commission as a 's of revenue (E) Ingut the sales manager's salary (F) Inaut the office manager's salary (G] Calculate the bree kaven point in sales units CHI Calculate the sale units needed to reecha $200.000 net income target 2.55 $ 2.55 $ 2.55 Direct Lalo (C) etfidency rate as minutes per pizza hourly labor cos. plus fringe bereits $ Total direct labor per pizza $ 12.00 S 10 Variable Coats (stated as per unit 11 Production $ 12 Selling 13 Tolal Variable Couls ver uril $ 11 Contribution lvlar $ CM per unit $ 15 CM Ratio 17 -xod Cost: 19 Selling (E) 19 Administration ) Total Fixed Costs 2.55 $ - $ (2.551_$ OVO 2.55 $ - $ 2.551 $ #DIV/01 2.55 . 2.55) /01 Direct Materials: Laugh choose toapings caffinated tomato sauce Boxes Total direct material per altra 1.00 0.05 Net Income 24 Breakever Point in sales units Sales comissions in) Total Variable Selling Expenses per vicces 28 Salesurillor Tante Pruli ul $200.000 Operating Budget Cash Budget Variances Decisions Capital 4 Display Stics L I - 918 - Type here to search Oo9e 1017 PM A - Excel Derry Moua - o ACCT301CourseProjec15predshes Tell me what you want to do x Share ViewH elp Team >> Wrap Text Merge & Centar - Wigment v File Home Insert Page Layout Formulas Date Review X Cut | Calieri v1 v 4 8 = -- Bor Copy Paale Format Painter BIUTA Ciscard 121 - x x IT Ceneral 5 % Normal Conditional Forma, as Good Formatting table- Styles Neutral TIK Delete Founat san A1 9 Fill Sort & Find & Clear Filter - Salart - Editing K L M N O Q R S T W LA 1 Prepare a Cash Budget C TUAL Noi Doc $ 300,000 $ 200.000 S Feb an . BUDGET March . Ictal atr . 5 . Cash Receipts 4 Sales Revenues 5 Cast Receipts from: 2 months ago 10 i manth age GIY IAL Calculate, based on the collection History, the cash receipts from customes B) Calculate the cash disbursements fer raw material purchases, assuming 1/2 of the previous month's purchase are paid in the current month Total Purchases Der m any Fly Man ADIV/0! $ 9 u Lalchh receipts in Cruisbursements 11 Raw Materials 12 Seling Expenses 1) Adrinistrative perses 11 total disbursements 15 Net Operating Cash 16 17 Investirenls: 18 Capend Business ICI Decice in which month you wil make a capital investment of $300,000 ID Determine the appropriate fnancing actvities so as ta kees at least the required minimum cash talarce of 5 75.000 end payoff any amount borrowed. 20 Financing: 21 New Debt 24 Noi Cash Flux 25 Hepinning av al 96 Ending Cash Balan 75.000 75,000 $ 75.00 75,000 $ 75 OD 75,000 $ 75.000 75,000 $ 29 THE MINIMUM CASH BALANCES $ 2, 31 Opreating Budget Cash Budget Variances Decisions Capital Display Stics L I - 35% - Type here to search 10:17 PM 1/26/21320 - Excel Derry Moua - o ACCT301CourseProjec15predshes Tell me what you want to do x Share Insert ViewH elp Team File A Home X Cut > Page Layout Formulas Date Review Calitri 11 VA A -- BIUTA . v san A1 9 Copy Wrap Text Morge & Centar - Ceneral 5 % , Normal Conditional Forma, as Good Formatting Table Style Neutral KIK Delele Format Pale . Format Painter Cocard Fill Clear Sort & Find & Filter - Salart - Editing Alignment K L M N O P Q R S - 1 Prepare a flexible Budget 2 Conduct Variance Analysis Master Budget February per Unit Actual February per Unit Flex Budest Februaryper Unit Variances iflex vs. actual) Total per unit 6 Units 9.85 35,000 344.750 Total Sales Revenue $ (A) Verify that the net income is the same as the Op Budget (B) input the flex budget sales units and the per unit price (C) Input the flex budget pruduction cost per unit and calculate the total flex pruduction costs. (D) input the flex budget variable selling costs per unit (E) input the flex budget fxed selling and administration costs (F) Calculate the variances between the actual and flex budget (G) Input the spendina variance (H) Calculate the volume variance using the budgetted contribution inai $ $ S - 2.55 190,750 $ 9,850 2.55 $ 194,600 $ 5.45 0.11 5.56 (C) (D) (F) (F) $ . $ $ . 9 Variable costs: 10 Production 11 Selling 12 Total Variable Costs 13 Contribution Margin 15 15 Fixed Costs: 17 Selling 18 Administration 19 Total Fixed Costs . - $ $ $ $ . - $ $ $ '$ (2.551 $ 150,150 $ 4.29 $ 60,000 58,500 118.500 - (A) $ 31,650 21 Net Income $ 22 23 Master Bucaet vs. Actual Net Income Variance 24 what is the volume variance 25 What is the spending rate variance 25 $ $ 31,650 - 27 28 Opreating Budget Cash Burgrt Variances Decisions Capital Display Stics L I - - 100% - o Type here to search 9 L 10:17 PM 126210 e x - Excel Berry Moue - o ACCT301CourseProjec15predshes Tell me what you want to do x Share Insert ViewH elp Team File A Home X Cut Page Layout Formulas Date Review Calitri 11 VA A -- BIUTA . v san A1 9 Copy Wrap Text Morge & Centar - Ceneral 5 % Number Normal Conditional Forma, as Good Formatting table- Sytes Neutral Pusle Fontat Painter Cilacard TIK Delete Founat Fill Clear Sort & Find & Filter - Salart - Editing rrent B C D E F G H I J K L M N O P R S 1 Evaluate an outsourcine decision Master Budget Relevant Costs . (A) Outsource Relevant Costs Units to Produce 6 Variable: Costs stated as per unit) 7 Production & Selling Total Variable Costs per unit (A) Input the number of units to the produced same as the sales for the quarter) (B) from the master budget. input the variable costs per unit for production. (c) Frumri the master budget, input the variable custs per unit for selling (D) Input the total fixed costs from the master budget (E) Input the vendor's price: $ 7.50 (F) Input a $.05 incremental variable selling costs due to the outsourcing decision (G) Enter the total fixed costs that will NOT be avoided due to oursourcina Selling: $50,000 Adrrim $40,000 $ 11 Fixed Costs 72 Selling 13 Administration Total Fixed Custs 15 16 Total costs $ . Cost per unit produced' #DIV/0! #DIV/0! Oprating Budgrt Cash Burgrt Variances Decisions Capital Display Stics L I - L 10:17 PM 1262100 - 100% - Type here to search Derry Moua - o x Share Insert File A Home X Cut ACCT30 Course Projectpresteeds - Excel Tell me what you want to do Custoru Custom v Comma 5 % Corditional Forma as Currency Formatting Table - Sytes san u A1 9 Copy Comma l Currency to For Pusle . Fill- Clear ..., Page Layout Formulas Date Review ViewH elp Team Calieri 11 V A *-= Wc Text BIUTA Margeix Centar - Port X B C D E Yrar Year 1 Year 2 Year 3 (300,000) Format Painter ClShoard Sort & Find & Filter - Salart - Editing rrent 616 - X N O P 0 1 Prepare a discounted cash flow analysis F Year 4 G Year 5 H Yearfi I Year 7 J Year 8 K Year L Year 10 M Totals (300,000) 3 Investment 135,000 135,000 135,000 135,000 135.000 200.000 200.000 200.000 200.000 200.000 1.675,000 (50,000) (50,000) (135,000) (1,150,000) 195,000) (95,000) (95,000) (05,000) (95,000) (135,000) (135,000) (135,000) (135,000) 5 Cash Flows 6 Sales 7 Salvape value 8 Operating costs 9 Major Maintenance 10 Net Cash Flow 11 12 Cummulative CF 13 1200,000) 40,000 40,000 40,000 40,000 40,000 65,000 65,000 65,000 65,000 15,000 175,000 (300.000) (260,000) (220,000) (180,000) (140,000) (100,000) (35.000) 30,000 95,000 150,000 175,000 Hurdle Rate 5% (A) Use the Excel function=NPV in order to determine the value of the future cash flows 15 Net Present Value (=NPV) 17 18 Internal Rate of Return l-IRRI (B) Use the IRR function in order to determine the "interest" earned on the cash flows from year to year 10 20 Payback Period (C) Look at the cummulative cash flow. How long before the initial Investment is pald off? 22 Profitability Index (D) Calculate the ratio of the project's PV of cashflows from years 1-10/ Initial Investment Major Maintenance 5 8 5.000) (E) Decide in which year (5-7) this expenditure should be made. Add $5,000 for every year after year 5 Oprating Budgrt Cash Burgrt Variances Decisions Capital Display Stics L I - L 10:17 PM - 100% - Type here to search A