Case Study #2 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, healthy, and local as possible, extracted through environmentally friendly processes, and keeps the ingredients to 10 or fewer.

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 atPeace 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 is cleaned (the honey, grass, and debris is rinsed off), melted down, and purified as a main ingredient for body care products.

The following budgeted price and cost information is available for 2022:

Note 1: Manufacturing overhead is allocated at $8.00/machine hour. 40% of the manufacturing overhead costs are considered variable.

Note 2: 10% of the selling and admin costs are variable. The remaining is fixed.

Monthly demand information is as follows:

The monthly machine hour capacity is limited to 1,800 hours.

Question 1: Prepare a product mix analysis. Determine the ranking of the products in order of their contribution margin per constrained resource. (10 marks)

Question 2: Using the linear programming model under Solver, compute the optimal number of each product to make and sell monthly to maximize the profit. (Include a screenshot of your LP models and results). Interpret the Answer Report and Sensitivity Report. What are the binding constraints? If the company can acquire more machine hours, comment on what price the company would be willing to pay with your rationale. (20 marks)

Your accounting manager would like you to assist with the preparation of the sales variance analysis for the 3^rd quarter of 2022. Budgeted and actual sales information for the company's third quarter of 2022 is as follows:

Question 3: For the third quarter of 2022, calculate total sales-volume, sales-mix, and sales-quantity, market-share and market-size variances for PNBC. (25 marks)

Question 4: Prepare a comprehensive variance analysis report for the CEO of PNBC to debrief her on the revenue performance for the third quarter. Ensure your recommendation is aligned with Questions 1 to 3. (15 marks)

Question 5:TheCEO of PNBC is wondering what other information and analysis would help improve the sales and profit performance for the body care products. Utilize your knowledge of data analytics, and provide management with at least two recommendations (internal and external) that can be considered for future analysis. (10 marks)

Question 6:To help estimate the plant utilities costs, the accounting manager has pulled the following data for 2021 - 2022:

Month	Machine hours	Plant utilities
Jan-21	1,650	$ 7,164
Feb-21	1,625	$ 7,051
Mar-21	1,629	$ 6,864
Apr-21	1,745	$ 6,963
May-21	1,664	$ 6,752
Jun-21	1,748	$ 6,880
Jul-21	1,653	$ 6,724
Aug-21	1,656	$ 6,732
Sep-21	1,699	$ 6,843
Oct-21	1,701	$ 6,949
Nov-21	1,610	$ 6,612
Dec-21	1,571	$ 6,512
Jan-22	1,620	$ 6,638
Feb-22	1,665	$ 6,955
Mar-22	1,662	$ 7,047
Apr-22	1,679	$ 6,691
May-22	1,656	$ 6,632
Jun-22	1,761	$ 7,015
Jul-22	1,365	$ 6,157
Aug-22	1,367	$ 6,210
Sep-22	1,690	$ 6,926
Oct-22	1,671	$ 6,871

In Excel,prepare a regression analysis for the utility costs. (Copy and paste a screenshot of your regression analysis into 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 for the accounting manager. In addition, comment on other qualitative cost estimation methods to be considered. (15 marks)