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 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 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\mathrm{.}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$: The CEO 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 $20212022$:

Month Machine hours Plant utilities

Jan$-211,650$ $ $7,164$

Feb$-211,625$ $ $7,051$

Mar$-211,629$ $ $6,864$

Apr$-211,745$ $ $6,963$

May$-211,664$ $ $6,752$

Jun$-211,748$ $ $6,880$

Jul$-211,653$ $ $6,724$

Aug$-211,656$ $ $6,732$

Sep$-211,699$ $ $6,843$

Oct$-211,701$ $ $6,949$

Nov$-211,610$ $ $6,612$

Dec$-211,571$ $ $6,512$

Jan$-221,620$ $ $6,638$

Feb$-221,665$ $ $6,955$

Mar$-221,662$ $ $7,047$

Apr$-221,679$ $ $6,691$

May$-221,656$ $ $6,632$

Jun$-221,761$ $ $7,015$

Jul$-221,365$ $ $6,157$

Aug$-221,367$ $ $6,210$

Sep$-221,690$ $ $6,926$

Oct$-221,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. $(1$The following budgeted price and