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: Note 1: Manufacturing overhead costs is allocated @ $6.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: 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: Question 3: For the second quarter of 2023, calculate total sales-volume, sales-mix, and salesquantity, 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 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 $9/ 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 200 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 2022-2023: 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) Case written and submission instruction: Writing \& Grammar (8 marks): Prepare the case study report in a professional way. Include a brief introduction and conclusion for the case study. Do not submit the report aS Q&A paper. Instruction: Students are to perform analysis of the assigned case and submit a written essay in a Word document (Do not submit a pdf file). Writing should be set font size 12 and double spacing. The essay will answer the above assignment questions for the case. Students can prepare all the quantitative work in Excel. When preparing the quantitative analyses, students are expected to clearly identify their assumptions and label the figures clearly. The Excel tables and reports are to be copied and pasted to be include in the Word document as a complete report. Please ensure that information provided in the tables are detailed so that marker does not need to check your formula in Excel. The maximum length of the paper (including exhibits) is 12 pages. Submission: Please submit both your Word document and Excel document in the Moodle Dropbox as a group before the due date. The Word document would be marked, and the Excel document is for validation only. Late submission would not be accepted. Note: Individual students are also required to submit a Peer Evaluation Form