Task: Cost-Volume-Profit Analysis
Data related to the expected sales of ventriloquist dummies names Groucho, Daphne, and Tex for the Puppet Barn for December are as follows:
Product Selling Price/per Unit Variable Cost/per Unit Sales Mix
Groucho $45 $35 50%
Daphne $100 $65 30%
Tex $70 $40 20%
Estimated fixed costs for December are $107,500, estimated sales are $500,000, and Puppet Barn wants to realize an operating profit of $64,500.
Question 1.) Develop a worksheet that will compute Puppet Barn's break-even point and the level of sales required to achieve its desired operating profit. The answers should be expressed both in units and in dollar, and they should be computed for the company as a whole and for the individual products, assuming they are sold in the proportions shown in the table.
Question 2.) Make sure to include a section for the above data and reference these in the rest of your worksheet. Use cell references whenever possible, use two decimal places for dollar amounts and three decimal places for your ratio, and adjust column widths.
Question 3.) Next, create a pie chart (on a chart sheet) that shows the sales mix proportion allocated to each product.
Question 4.) Finally, format the worksheet to provide a professional look...something that you would be proud to show a client paying you to create this workbook. Make sure all values are represented by labels, headings, proper dollar signs, lines and double lines are used.