Required information Part 4 Analytics Assignment (Excel Budgeting and Variance Analysis) [The following information applies to...

Transcribed Image Text:

Required information Part 4 Analytics Assignment (Excel Budgeting and Variance Analysis) [The following information applies to the questions displayed below.) These questions relate to the Integrated Analytics Case: Bene Petit. Select the appropriate eBook link to open the Case Overview, Case Background, and Part 4: Planning and Control. For this assignment, you will use the mostly completed Excel sheet provided below to input different numbers and compute a flexible budget. Part 4 Master Budget (student version) PartA-C.xlsx Budgeting and Variances Assume Bene Petit is in its third year of operations and is on track to report a small profit during Year 3 after barely breaking even in Year 2. For this assignment, you will help Taylor budget for Bene Petit's operations in Year 4 so that she can plan for operating expenditures and determine how much (if any) profit she can withdraw from the business or reinvest to fuel future growth. Based on the upward trend in Bene Petit's sales over the first three years, industry estimates of growth in the meal- preparation service sector, and Taylor's plans to invest more heavily in online advertising to expand Bene Petit's customer base, Taylor has estimated Bene Petit's production and sales for each quarter of Year 4 as follows: Single-Serving Meals Dual-Serving Meals Family-Sized Meals Total Customer Meals Total Donated Meals Quarter 1 Quarter 2 Quarter 3 Quarter 41 Total 750 900 975 1,125 3,750 1,250 1,500 1,625 1,875 6,250 3,000 3,600 3,900 4,500 15,000 5,000 6,000 6,500 7,500 25,000 5,000 6,000 6,500 7,500 25,000 Other operating plans including the following: Pricing The retail price for customer meals is $6 per serving. However, after promotional discounts and other advertising promotions are factored in, Taylor estimates that her average sales price will be $5 per serving in Year 4. Manufacturing Costs (Customer Meals only) promotions are factored in, Taylor estimates that her average sales price will be $5 per serving in Year 4. Manufacturing Costs (Customer Meals only) Customer meals are produced "just in time" for delivery to the customer, so there is no Finished Goods Inventory. Purchases of raw materials (ingredients) are based on the number of servings in each meal (single-serving = one serving, dual-serving two servings, family-sized four servings). Each serving should require about one-half pound of raw ingredients at an average standard cost of $2 per pound. Bene Petit maintains a small inventory of staple ingredients equal to 2% of the next quarter's production needs. Assume Bene Petit will have 200 pounds of raw material on hand at the beginning and end of Year 4. Direct labor wages vary with the number of customer meals, regardless of serving size. Each worker can make and package about 25 customer meals per hour and the average labor rate (including taxes and benefits) is $20 per direct hour. Variable production overhead costs (for power, packaging materials, etc.) are applied at a rate equal to 50% of direct labor Annual fixed manufacturing costs of $37,500 are spread evenly across the total number of customer meals sold. Selling and Administrative Costs The standard cost of donated meals is treated as a variable selling expense. The standard cost of a donated meal is $1.25 per meal. Delivery expenses for customer meals should average $2 per customer order with an average order size of 4 customer meals per delivery. Donated meals are delivered to community partners in batch sizes of 500 at a standard cost of $125 per delivery. Fixed selling expenses for website hosting and advertising are budgeted at $7,250 per quarter. Fixed administrative expenses are budgeted at $26,000 per year. Part 4 Analytics Assignment A: Prepare the Master Budget 1. Use the A. Master Budget tab in the Excel sheet provided to complete the master budget for Bene Petit in a contribution margin format. Link all cells to the starting assumptions so that you can easily update the budget for changes in the underlying assumptions. Required: a. What is Year 4's budgeted operating income? Required information Part 4 Analytics Assignment (Excel Budgeting and Variance Analysis) [The following information applies to the questions displayed below.) These questions relate to the Integrated Analytics Case: Bene Petit. Select the appropriate eBook link to open the Case Overview, Case Background, and Part 4: Planning and Control. For this assignment, you will use the mostly completed Excel sheet provided below to input different numbers and compute a flexible budget. Part 4 Master Budget (student version) PartA-C.xlsx Budgeting and Variances Assume Bene Petit is in its third year of operations and is on track to report a small profit during Year 3 after barely breaking even in Year 2. For this assignment, you will help Taylor budget for Bene Petit's operations in Year 4 so that she can plan for operating expenditures and determine how much (if any) profit she can withdraw from the business or reinvest to fuel future growth. Based on the upward trend in Bene Petit's sales over the first three years, industry estimates of growth in the meal- preparation service sector, and Taylor's plans to invest more heavily in online advertising to expand Bene Petit's customer base, Taylor has estimated Bene Petit's production and sales for each quarter of Year 4 as follows: Single-Serving Meals Dual-Serving Meals Family-Sized Meals Total Customer Meals Total Donated Meals Quarter 1 Quarter 2 Quarter 3 Quarter 41 Total 750 900 975 1,125 3,750 1,250 1,500 1,625 1,875 6,250 3,000 3,600 3,900 4,500 15,000 5,000 6,000 6,500 7,500 25,000 5,000 6,000 6,500 7,500 25,000 Other operating plans including the following: Pricing The retail price for customer meals is $6 per serving. However, after promotional discounts and other advertising promotions are factored in, Taylor estimates that her average sales price will be $5 per serving in Year 4. Manufacturing Costs (Customer Meals only) promotions are factored in, Taylor estimates that her average sales price will be $5 per serving in Year 4. Manufacturing Costs (Customer Meals only) Customer meals are produced "just in time" for delivery to the customer, so there is no Finished Goods Inventory. Purchases of raw materials (ingredients) are based on the number of servings in each meal (single-serving = one serving, dual-serving two servings, family-sized four servings). Each serving should require about one-half pound of raw ingredients at an average standard cost of $2 per pound. Bene Petit maintains a small inventory of staple ingredients equal to 2% of the next quarter's production needs. Assume Bene Petit will have 200 pounds of raw material on hand at the beginning and end of Year 4. Direct labor wages vary with the number of customer meals, regardless of serving size. Each worker can make and package about 25 customer meals per hour and the average labor rate (including taxes and benefits) is $20 per direct hour. Variable production overhead costs (for power, packaging materials, etc.) are applied at a rate equal to 50% of direct labor Annual fixed manufacturing costs of $37,500 are spread evenly across the total number of customer meals sold. Selling and Administrative Costs The standard cost of donated meals is treated as a variable selling expense. The standard cost of a donated meal is $1.25 per meal. Delivery expenses for customer meals should average $2 per customer order with an average order size of 4 customer meals per delivery. Donated meals are delivered to community partners in batch sizes of 500 at a standard cost of $125 per delivery. Fixed selling expenses for website hosting and advertising are budgeted at $7,250 per quarter. Fixed administrative expenses are budgeted at $26,000 per year. Part 4 Analytics Assignment A: Prepare the Master Budget 1. Use the A. Master Budget tab in the Excel sheet provided to complete the master budget for Bene Petit in a contribution margin format. Link all cells to the starting assumptions so that you can easily update the budget for changes in the underlying assumptions. Required: a. What is Year 4's budgeted operating income?