BYU Student Author: @James_Gerstner
Reviewers: @Jacob_Dutton, @Millie_K_B
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Excel
- Power BI
Need a program? Click here.
Overview
Artisan Loaves and Bagels, inc. (A&B) has issues with their product mix. It seems to be making a profit from both products they produce, A and B, but profits are lower than A&B thinks they should be. Management at A&B hired you, a CMA (Certified Management Accountant), to help them out.
Ultimately, they want you to help them find the product mix that will maximize their profits (i.e., how many units of A and B should they sell each quarter) and the amount of additional profit the new mix will generate. They inform you that both of their products use the same direct materials, and they can use only up to $430,000 of materials quarterly.
One place they need help is with accurately allocating fixed costs (rent, utilities, etc.) to avoid the much-dreaded “death spiral.” Currently, they use a standard per-unit rate across both products, but they have a hunch that factory floorspace would be a more accurate allocation system of their fixed costs.
Are you ready to put your CMA skills to the test?
Instructions
- Download the data for this challenge (located below).
- In Excel, fill in the missing values in the data table using formulas.
a. Once you have one of the data sheets filled in, copy those values into the other data sheet so you have a record to refer back to later. - Using Solver, maximize the total profit value by changing the number of units sold. Here are some hints for parameters: Total RM costs cannot exceed 430,000 and total production for each unit cannot exceed the given production capacity.
a. If you still need help with Solver, check the solution image below. - Create a new file in Power BI and select the data, both solved and unsolved, to upload into Power BI.
a. Using both the before and after data will help you show management the differences you’ve suggested. - Select Transform Data, then clean the data in both tables using Power Query to prepare it for visualizations. Measures and columns can be recreated so focus on getting the “meat” of the data.
Power Query Suggestions
- To remove blank columns and clean the data, you can demote headers (Use Headers as First Row), remove blank rows, transpose table, promote headers (Use First Row as Headers), then remove all but the top two rows of data.
- Be sure the data columns are the appropriate types (decimal, string, etc.).
- Finally, create some measures and visuals that will show management your results. Should they prioritize one product over the other? Tell them how much their revenue changed, then wow them with your creative analytical capacity.
a. For example, create a measurement and related card for total profit (both original and optimized data), bar charts for each point to show sales and profits, and a slicer to switch between products.
Data Files
Suggestions and Hints
- Contribution Margin per unit = Selling Price per unit – total variable costs per unit (DM, DL, and VOH)
- Total Profit = (CM per unit * Units Sold) – Fixed Costs
- To find percentages (optimal product mix % and floorspace %), divide the value for each product over the value of the sum of the two products.
- Reallocated fixed costs = Floorspace % * Total Fixed Costs
Solution
Solution Video: Challenge 225|EXCEL&POWERBI – Managerial Margin Maximization