Case Study Prairie Natural Body Care Co. Located in the beautiful Rocky Mountains of Alberta, Prairie Natural Body Care Co. (PNBC) embraces the philosophy of environmental sustainability. The natural standard is at the core of the company's value. "We truly believe that simple, fresh, and fewer ingredients are better. Keeping it simple allows us to choose each ingredient deliberately for its quality, safety, and benefits. Using organic whenever possible and working with local farmers, growers and wild foragers across Canada." (Cited from the company's website). The company ensures that all natural ingredients are organic, safe, and healthy, and local as possible, extracted through environmentally friendly processes, and keeps the ingredients to 10 or less. Three main skin care products are offered by PNBC: Body Butter, Hand Cream, and Lip Butter. The main ingredient beeswax is sourced from a local bee farm at Peace River, Alberta. The Peace River region is well-known for its vast fields of clover. With its long warm summer days and cool summer nights, it is idyllic to farm bees for honey and beeswax. During winter while the bees are hibernating, the beeswax are cleaned, rinsed off the honey, grass, and debris, before melted down and purified as a main ingredient for body care products. The following budgeted price and unit cost information is available for 2023: Body Butter Hand Cream Lip Butter 16.00 $ 12.00 $ 7.50 $ Selling price Costs: Beeswax Other natural seed/plant oil Natural flavour Labour costs Manufacturing overhead costs (Note 1) Selling and admin costs (Note 2) Total costs Profit $ Max Demand Min Demand 2.00 1.50 0.75 2.84 3.00 2.46 12.55 3.45 $ Body Butter 1.50 1.25 0.68 1.67 5,000 3,000 2.00 1.80 Note 1: Manufacturing overhead costs is allocated @ $8.00/machine hour to all three products. 40% of the manufacturing overhead costs is considered variable. Remaining are fixed costs. Note 2: 10% of the selling and admin costs are variable. The remaining are fixed. PNBC monthly machine hour capacity is limited to 1,800 hours. Monthly demand information is as follows: 8.90 3.10 $ 0.50 0.25 0.32 0.36 1.00 1.20 3.63 3.87 Hand Cream 2,000 Lip Butter 1,500 Question 1: Prepare a product mix analysis. Determine the machine hours required for each of the product. Determine the ranking of the products in order of its contribution margin per constrained resource. (11 marks) Question 2: Using the linear programming model under Solver, determine the optimal number of units of each product PNBC should make and sell each month to maximize the profit. (Include the screen shot of your LP models and results). (10 marks) Interpret the Answer Report and Sensitivity Report. What are the binding constraints? Explain why the constraints are binding or not binding. If the company can acquire more machine hours, what price would the company be willing to pay? (5 marks) Your accounting manager would like you to assist with the preparation of the sales variance analysis for the 2nd quarter of 2023. Budgeted and actual sales information for the company are as follows: Body Butter Hand Cream Lip Butter Total Market size Actual Sales Volume 9,600 3,600 4,200 17,400 180,000 Budget Sales Volume 9,300 5,100 4,500 18,900 160,000 Question 3: For the second quarter of 2023, calculate total sales-volume, sales-mix, and sales- quantity, market-share and market-size variances for PNBC. (20 marks) Question 4: Prepare a comprehensive variance analysis report to the CEO of PNBC to debrief her on the revenue performance for the second quarter. Ensure your recommendation is aligned with Questions 1 to 3. (10 marks) Question 5: The CEO of PNBC is wondering other than the variance analysis, what other information and analysis would help improve the sales and profit performance for the body care products. Utilize your knowledge on data analytics as introduced earlier in the course, provide management with at least two recommendations (internal and external analysis) that can be considered for future studies. (6 marks) In July 2023, PNBC was approached by a customer to place a one-time special order for 600 units of Hand Cream for $10/unit. The product is similar to PNBC's current product except that it requires less natural seed oil resulting in reduced cost from $1.25 to $1.00 per unit. If PNBC accepts this order, it is expected that it would not be able to fulfill 100 units of Hand Cream for its regular order in July. Question 6: Identify and calculate the relevant costs for the special order. What would be the minimum price PNBC would accept? (10 marks) What are the other factors for PNBC to consider in terms of the special order decision? Present your recommendation and explain why or why not you would recommend PNBC to accept the special order. (5 marks) To help estimate the plan utilities costs, the accounting manager has pulled the following data for 2021 2022: Month Jan-21 Feb-21 Mar-21 Apr-21 May-21 Jun-21 Jul-21 Aug-21 Sep-21 Oct-21 Nov-21 Dec-21 Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Machine hours 1,650 1,625 1,629 1,745 1,664 1,748 1,653 1,656 1,699 1,701 1,610 1,571 1,620 1,665 1,662 1,679 1,656 1,761 1,365 1,367 1,690 1,671 Plant utilities $ 7,164 7,051 6,864 6,963 $ $ $ A A A A A A A A LA LA LA LA LA LA LA LA LA LA LA $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 6,752 6,880 6,724 6,732 6,843 6,949 6,612 6,512 6,638 6,955 7,047 6,691 6,632 7,015 6,157 6,210 6,926 6,871 Question 7: In Excel, prepare a regression analysis for the utility costs. (Copy and paste a screenshot of your regression analysis in your word document). Assess the regression analysis result using the criteria of economic plausibility, goodness of fit, and significance of the independent variable. Prepare the cost estimation formula of the utility costs for the accounting manager. (10 marks) In addition, explain why regression analysis method is chosen over high-low method, and comment on three other cost estimation methods to be considered. (5 marks)