(((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 their 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 three years. You have hired a sales manager and a part-time office manager - These deliverables Involve preparing the necessary financial budgets In order to satisfy creditors and control business operations. 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 40,000 42,500 45,000 Sales price for large $10.50 Sales price for medium $8.50 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%). 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 how many minutes it will take to make one pizza from order to box. 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 sale manager. 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, including the car allowance .!!!Input the monthly salary for your office manager.!!!.!.!.!!! Calculate the!|||||||||||!!!! breakeven sales as well as the sales needed to make $200,000 in net income!!!!!!!!!!!!!! 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 optlmlstic results. (((The Cash Budget)))) !!!!!!!!!!!!!Once you are satisfied with your operating budget, you must prepare a cash budget based on estimated cash flows from collections on customer receivables and cash payments!!!!!!!!!!. Ensure that the sales revenues for January |||||||||!!!March is the same as that on the operating budget!!!!!!!!!!!!! Using the aging of cash collections, calculate total cash receipts for January - 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. !!!!!!!!!!!!!!!Calculate the forecast direct material cash payments by month!!!!!!! You are to decide in which month to invest a significant amount of cash in a facility expansion. 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 pald-off. cccccccccccce Identify and explain varlations from the budget and make a decision for continuous Improvement.))))))))) Performance Evaluation The data for the February master budget columns should come over from the operating budget worksh 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 operatin budget revenue rate, variable expenses' rates and fixed costs. Actual operating results for the month are provided. DO NOT CHANGE THIS DATA Calculate the variances between the flexible budget and actual results. As being either F for favorable or for unfavorable. Determine how much of the Net income variance was due to volume and how much was rate-related. How would you evaluate the actual results? What steps would you take to further Investigate and possi adjust your budget for the rest of the year? Incremental Analysis - Do we outsource? Given the relevant costs from the operating budget, prepare a worksheet comparing the relevant data to vendor's price quote for doing the production currently done in-house. Provide an opinion as to whether this business deal is acceptable. Are there any non-financial considerations? (CCCCCC Project Justification)}}}) It is now time to prepare a capital budget. The idea is to prepare a list of acceptable projects in which to spend in the first quarter in order to increase the value of operations. Recall that the cash budget include funds for plant expansion. The estimated future cash flows arising from sales, net of operating costs are given. The hurdle rate represents the interest rate to borrow these funds plus a 2% risk factor. Use this percent in order to disco the cash flows. The project cost and the salvage value, as well, have already been input into the worksheets. Input these cash flows: Sales revenues: Years 1 - 5 $135,000 per year; Years 6-10 $200,000 per year Operating costs: Years 1-5 $ 95,000 per year; Years 6-10 $ 135,000 per year Decide in which year (5-7) to spend the $85,000 of major maintenance Complete the five (5) measures at the bottom of the worksheet. Provide a recommendation as to whether this project is acceptable. Prepare a Master Operating Budget for the First Quarter Buzz Pizza Target Market: adult learners, timestarved, poor time management Jan Feb Mar Total Otr Sales: : Units (A) Price/unit $ S - $ $ Average Sales Price/unit (B) wgtd. % Sales average 10 50 $ 8.50 $ 0% S Total Sales Revenue $ - Sales Price (all 2 topping): Large $ Medium $ S (A) Input the forecast sale volume (B) Input the planned product mix (0) Input the number of pizza made per hour (D) Input the sales commission as a % of revenue (E) input the sales manager's salary (F) 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, , 2.55 S 2.55$ 2.55 Direct Labor: (C) efficiency rate as minutes per pizza hourly labor cost plus fringe benefits $ Total direct labor per pizza $ 2.55 12.00 S Variable Costs (stated as per unit) Production $ Selling Total Variable Costs per unit s Contribution Margin $ CM per units CM Ratio Fixed Costs Selling (E) Administration (F) Total Fixed Costs $ 2.55 $ $ (2.55) $ #DIV/0! 2.55 $ $ (2.55) $ #DIV/0! (2.55) #DIV/0! $ $ Direct Materials: Dough $ cheese toppings caffinated tomato sauce Boxes Total direct material per pizza $ $ 0.25 0.50 0.75 $ $ S 1.00 0.05 2.55 Net Income $ $ $ $ s Breakeven Point in sales units (6) Sales comissions (D) () Total Variable Selling Expenses per pizza $ Sales units for a Target Profit of $200,000 (H) t M - M IN Prepare a Cash Budget ACTUAL Nov Dec BUDGET March Jan Feb Total Otr $ 300,000 $200,000 $ $ $ $ $ Cash Receipts Sales Revenues Cash Receipts from: 2 months ago (10%) 1 month ago (60% current month (25%) total cash receipts Cash Disbursements Sie Raw Materials mas Selling Expenses Administrative Expenses total disbursements Net Operating Cash (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 #DIV/0! $ $ $ (A) S S S S (B) (a (C) Decide in which month you will make a capital investment $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. $ $ $ $ $ $ $ Investments: Expand Business (C) ! Financing: New Debt ! Repay debt (D) 1 $ Net Cash Flow Beginning Cash Balance Ending Cash Balance $ 75,000 75,000 $ S 75,000 75,000 $ $ 75,000 75,000 $ 75,000 75,000 $ THE MINIMUM CASH BALANCE IS $ 75,000 ! - ! 1 . .- Operating Budget Cash Budget Variances Decisions Capital + Conduct Variance Analysis Master Budget February Actual February per Unit Flex Budget February Variances flex. vs. Actual) Total per Unit Sales: per Unit per Unit Units $ 9.85 () (F) 35,000 $ 34,750 Total Sales Revenue 5 $ (A) Verify that the net income is the same as the Op Rudget (0) Input the flex budget sales units and the per unit price (C) Input the flex budget production cost per unit and calculate the total flex production costs. ID Input the flex budget variable selling costs per unit (6) input the flex budget fixed selling and administration costs (F) Calculate the variances between the actual and flex budget (G) Input the spending variance (H) Calculate the volume variance using the budgetted contribution margin $ $ Variable Costs: Production Selling Total Variable Costs 190,750 $ 5.45 0.11 2.55 $ 194,500 $ 5.56 (C) $ (D) $ (F) (F) g $ $ $ $ $ Contribution Margin $ $ 12.55 $ 150,150 $ 4.29 $ $ S $ Fixed Costs: Selling Administration Total Fixed Costs 60,000 58,500 118,500 LE) IF) (F) (F) Net Income S IAI $ 31,650 $ $ (F) Master Budget vs. Actual Net Income Variance What is the volume variance What is the spending rate variance S $ 31,450 IG) THI $ Operating Budget Cash Budget Variances Decisions Capital + Tell 6 C D E F 6 H 1 K M N p R S A Evaluate an outsourcing decision Master Budget Relevant Costs (A) Outsource Relevant Costs Units to produce Variable costs stated as per unit) Production Selling Total Variable costs per unit $ (8) 1) (F) (F) (A) Input the number of units to be produced (same as the sales for the quarter) (B) From the master budget, input the variable costs per unit for production. (C) From the master budget, input the variable costs per unit for selling (D) input the total fixed costs from the master budget (F) 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 oursourcing Selling: $50,000 Admin $40,000 $ Fixed Costs Selling Administration Total Fixed Costs ID) (D) (G) (G) Total Costs $ S Cost per unit produced #DIV/0! #DIV/0! Operating Budget Cash Budget Variances Decisions Capital # R B D E F G H K L M Prepare a discounted cash flow analysis Year 1 Year 2 Year 3 Year 4 Years Year 6 Year 7 Year 8 Year 2 Year 10 Year 0 (300,000) Totals (300,000) Investment 135,000 135,000 135,000 135,000 135,000 200,000 200,000 200,000 Cash Flows Sales Salvage value Operating costs Major Maintenance ) Net Cash Flow 200,000 200,000 1,675,000 (50,000) (50,000) (135,000) (135,000) (1,150,000) (95,000) (95,000) (95,000) (95,000) (135,000) (95,000) (135,000) (135,000) (E) 40,000 65,000 65,000 (300,000) 40,000 40,000 40,000 40,000 65,000 65,000 15,000 175,000 ! Cummulative CF (300,000) (260,000) (220,000) (180,000) (140,000) (100,000) (35,000) 30,000 95,000 160,000 175,000 3 Hurdle Rate 5% - . Net Present Value (=NPV) (A) Use the Excel function=NPV in order to determine the value of the future cash flows, net of the initial investment Internal Rate of Return (=IRR) (B) Use the IRR function in order to determine the "interest" earned on the cash flows from year o to year 10 Payback Period (c) Look at the cummulative cash flow. How long before the initial investment is paid off? (o) Calculate the ratio of the project's PV of cashflows from years 1-10/ initial investment ! Profitability Index ! Major Maintenance $ (85,000) (E) Decide in which year (5-7) this expenditure should be made. Add $5,000 for every year after year 5 ) Operating Budget Cash Budget Variances Decisions Capital (((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 their 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 three years. You have hired a sales manager and a part-time office manager - These deliverables Involve preparing the necessary financial budgets In order to satisfy creditors and control business operations. 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 40,000 42,500 45,000 Sales price for large $10.50 Sales price for medium $8.50 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%). 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 how many minutes it will take to make one pizza from order to box. 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 sale manager. 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, including the car allowance .!!!Input the monthly salary for your office manager.!!!.!.!.!!! Calculate the!|||||||||||!!!! breakeven sales as well as the sales needed to make $200,000 in net income!!!!!!!!!!!!!! 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 optlmlstic results. (((The Cash Budget)))) !!!!!!!!!!!!!Once you are satisfied with your operating budget, you must prepare a cash budget based on estimated cash flows from collections on customer receivables and cash payments!!!!!!!!!!. Ensure that the sales revenues for January |||||||||!!!March is the same as that on the operating budget!!!!!!!!!!!!! Using the aging of cash collections, calculate total cash receipts for January - 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. !!!!!!!!!!!!!!!Calculate the forecast direct material cash payments by month!!!!!!! You are to decide in which month to invest a significant amount of cash in a facility expansion. 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 pald-off. cccccccccccce Identify and explain varlations from the budget and make a decision for continuous Improvement.))))))))) Performance Evaluation The data for the February master budget columns should come over from the operating budget worksh 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 operatin budget revenue rate, variable expenses' rates and fixed costs. Actual operating results for the month are provided. DO NOT CHANGE THIS DATA Calculate the variances between the flexible budget and actual results. As being either F for favorable or for unfavorable. Determine how much of the Net income variance was due to volume and how much was rate-related. How would you evaluate the actual results? What steps would you take to further Investigate and possi adjust your budget for the rest of the year? Incremental Analysis - Do we outsource? Given the relevant costs from the operating budget, prepare a worksheet comparing the relevant data to vendor's price quote for doing the production currently done in-house. Provide an opinion as to whether this business deal is acceptable. Are there any non-financial considerations? (CCCCCC Project Justification)}}}) It is now time to prepare a capital budget. The idea is to prepare a list of acceptable projects in which to spend in the first quarter in order to increase the value of operations. Recall that the cash budget include funds for plant expansion. The estimated future cash flows arising from sales, net of operating costs are given. The hurdle rate represents the interest rate to borrow these funds plus a 2% risk factor. Use this percent in order to disco the cash flows. The project cost and the salvage value, as well, have already been input into the worksheets. Input these cash flows: Sales revenues: Years 1 - 5 $135,000 per year; Years 6-10 $200,000 per year Operating costs: Years 1-5 $ 95,000 per year; Years 6-10 $ 135,000 per year Decide in which year (5-7) to spend the $85,000 of major maintenance Complete the five (5) measures at the bottom of the worksheet. Provide a recommendation as to whether this project is acceptable. Prepare a Master Operating Budget for the First Quarter Buzz Pizza Target Market: adult learners, timestarved, poor time management Jan Feb Mar Total Otr Sales: : Units (A) Price/unit $ S - $ $ Average Sales Price/unit (B) wgtd. % Sales average 10 50 $ 8.50 $ 0% S Total Sales Revenue $ - Sales Price (all 2 topping): Large $ Medium $ S (A) Input the forecast sale volume (B) Input the planned product mix (0) Input the number of pizza made per hour (D) Input the sales commission as a % of revenue (E) input the sales manager's salary (F) 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, , 2.55 S 2.55$ 2.55 Direct Labor: (C) efficiency rate as minutes per pizza hourly labor cost plus fringe benefits $ Total direct labor per pizza $ 2.55 12.00 S Variable Costs (stated as per unit) Production $ Selling Total Variable Costs per unit s Contribution Margin $ CM per units CM Ratio Fixed Costs Selling (E) Administration (F) Total Fixed Costs $ 2.55 $ $ (2.55) $ #DIV/0! 2.55 $ $ (2.55) $ #DIV/0! (2.55) #DIV/0! $ $ Direct Materials: Dough $ cheese toppings caffinated tomato sauce Boxes Total direct material per pizza $ $ 0.25 0.50 0.75 $ $ S 1.00 0.05 2.55 Net Income $ $ $ $ s Breakeven Point in sales units (6) Sales comissions (D) () Total Variable Selling Expenses per pizza $ Sales units for a Target Profit of $200,000 (H) t M - M IN Prepare a Cash Budget ACTUAL Nov Dec BUDGET March Jan Feb Total Otr $ 300,000 $200,000 $ $ $ $ $ Cash Receipts Sales Revenues Cash Receipts from: 2 months ago (10%) 1 month ago (60% current month (25%) total cash receipts Cash Disbursements Sie Raw Materials mas Selling Expenses Administrative Expenses total disbursements Net Operating Cash (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 #DIV/0! $ $ $ (A) S S S S (B) (a (C) Decide in which month you will make a capital investment $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. $ $ $ $ $ $ $ Investments: Expand Business (C) ! Financing: New Debt ! Repay debt (D) 1 $ Net Cash Flow Beginning Cash Balance Ending Cash Balance $ 75,000 75,000 $ S 75,000 75,000 $ $ 75,000 75,000 $ 75,000 75,000 $ THE MINIMUM CASH BALANCE IS $ 75,000 ! - ! 1 . .- Operating Budget Cash Budget Variances Decisions Capital + Conduct Variance Analysis Master Budget February Actual February per Unit Flex Budget February Variances flex. vs. Actual) Total per Unit Sales: per Unit per Unit Units $ 9.85 () (F) 35,000 $ 34,750 Total Sales Revenue 5 $ (A) Verify that the net income is the same as the Op Rudget (0) Input the flex budget sales units and the per unit price (C) Input the flex budget production cost per unit and calculate the total flex production costs. ID Input the flex budget variable selling costs per unit (6) input the flex budget fixed selling and administration costs (F) Calculate the variances between the actual and flex budget (G) Input the spending variance (H) Calculate the volume variance using the budgetted contribution margin $ $ Variable Costs: Production Selling Total Variable Costs 190,750 $ 5.45 0.11 2.55 $ 194,500 $ 5.56 (C) $ (D) $ (F) (F) g $ $ $ $ $ Contribution Margin $ $ 12.55 $ 150,150 $ 4.29 $ $ S $ Fixed Costs: Selling Administration Total Fixed Costs 60,000 58,500 118,500 LE) IF) (F) (F) Net Income S IAI $ 31,650 $ $ (F) Master Budget vs. Actual Net Income Variance What is the volume variance What is the spending rate variance S $ 31,450 IG) THI $ Operating Budget Cash Budget Variances Decisions Capital + Tell 6 C D E F 6 H 1 K M N p R S A Evaluate an outsourcing decision Master Budget Relevant Costs (A) Outsource Relevant Costs Units to produce Variable costs stated as per unit) Production Selling Total Variable costs per unit $ (8) 1) (F) (F) (A) Input the number of units to be produced (same as the sales for the quarter) (B) From the master budget, input the variable costs per unit for production. (C) From the master budget, input the variable costs per unit for selling (D) input the total fixed costs from the master budget (F) 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 oursourcing Selling: $50,000 Admin $40,000 $ Fixed Costs Selling Administration Total Fixed Costs ID) (D) (G) (G) Total Costs $ S Cost per unit produced #DIV/0! #DIV/0! Operating Budget Cash Budget Variances Decisions Capital # R B D E F G H K L M Prepare a discounted cash flow analysis Year 1 Year 2 Year 3 Year 4 Years Year 6 Year 7 Year 8 Year 2 Year 10 Year 0 (300,000) Totals (300,000) Investment 135,000 135,000 135,000 135,000 135,000 200,000 200,000 200,000 Cash Flows Sales Salvage value Operating costs Major Maintenance ) Net Cash Flow 200,000 200,000 1,675,000 (50,000) (50,000) (135,000) (135,000) (1,150,000) (95,000) (95,000) (95,000) (95,000) (135,000) (95,000) (135,000) (135,000) (E) 40,000 65,000 65,000 (300,000) 40,000 40,000 40,000 40,000 65,000 65,000 15,000 175,000 ! Cummulative CF (300,000) (260,000) (220,000) (180,000) (140,000) (100,000) (35,000) 30,000 95,000 160,000 175,000 3 Hurdle Rate 5% - . Net Present Value (=NPV) (A) Use the Excel function=NPV in order to determine the value of the future cash flows, net of the initial investment Internal Rate of Return (=IRR) (B) Use the IRR function in order to determine the "interest" earned on the cash flows from year o to year 10 Payback Period (c) Look at the cummulative cash flow. How long before the initial investment is paid off? (o) Calculate the ratio of the project's PV of cashflows from years 1-10/ initial investment ! Profitability Index ! Major Maintenance $ (85,000) (E) Decide in which year (5-7) this expenditure should be made. Add $5,000 for every year after year 5 ) Operating Budget Cash Budget Variances Decisions Capital