SuperStyles, Inc. is a manufacturer of personal grooming products. During 2015, the company S sold 240,000 Model 737 Hair Dryers to retail stores at a sales price of $32 each. Following are the 2015 financial results: SuperStyles, Inc. Contribution Format Income Statement For the Year 2015 3 Ooo On A CS 29 Sales $ 7,680,000 Less variable costs: 3 Direct materials 1.488,000 Direct labor 1,320,000 Variable overhead 1,776,000 16 Total variable costs 4,584,000 17 Contribution Margin $ 3,096,000 18 Less fixed costs: 2,200,000 19 Income before taxes $ 896,000 20 21 22 You are the top managerial accountant at SuperStyles, Inc and are scheduled to meet with the company's 23 CEO next week to discuss the financial outlook for 2016. In particular, the CEO will want to know 24 what steps the company might take to improve upon its 2015 income performance. You expect to be asked 25 questions about the impact that various changes in sales price, sales volume, and costs would have 26 on the company's contribution margin, breakeven point, margin of safety, and income. In addition, 27 the CEO is likely to ask this question: If sales price decreases due to competition but costs increase in 2016, 28 how many dryers would we have to sell to meet our target Income in 2016? 30 You obviously won't be able to do these calculations by hand during the meeting, so you have decided 31 to construct an Excel program to do them for you. Page 2 is an example of how the output of your 32 program should look. Please note that the only area of the program that may contain hard-coded 33 numbers is Section A, the only fields that you may change when doing "what ifs" are the shaded ones 34 in Section B; and all cells containing a "?* on the attached example (including those in Section C) 35 must contain only formulas. No hard-coded numbers are allowed in any cell containing a "?" 36 37 You will be expected to tum in the following: 1. An Excel file containing your program should be submitted to your professor via Canvas prior to the due date. Please include your names on the program itself, as well as naming 40 the file with your Group # as part of the file name. Also keep a copy 41 of the file in case of transmission failure 2. The "2016 what-it?* scenario should be based on these change factors: Sales in units Sales price per unit Direct materials cost per unit Direct labor cost per unit Variable overhead cost per unit Fixed costs 38 39 42 43 44 45 + 46 47 48 49 50 RC 4,8% 5% 44% 51% 2% 43% SuperStyles, Inc. Financial Projections - 2016 5 3 7 Section A: 2015 Actual 240,000 $ 32.00 $ 6.20 $ 5.50 $ 7.40 $ 2.200,000 Section B: Change Factors 0% 0% 0% 0% 0% 0% Section C: 2016 What If? ? ? ? ? ? ? 2015 Actual ? 2016 What If? ? ? ? ? 2 ? 2 7 B Inputs: 3 0 Sales in units 1 Sales price per unit 12 Direct materials cost per unit 13 Direct labor cost per unit 4 Variable overhead cost per unit 15 Fixed costs 16 17 Income Projections: 18 Sales 19 Less variable expenses: 20 Direct materials 21 Direct labor 22 Variable overhead 24 Total variable expenses 26 Contribution margin 27 Less fixed expenses 29 Income before taxes 30 31 32 Contribution margin per unit 33 Contribution margin ratio 34 35 Break-even sales in units 36 Break-even sales in $$ 37 38 Margin of safety in units 39 Margin of safety in sales $$ 40 41 7 ? 7 ? 7 ? ? 7 ? ? ? ? 7 ? 7 7 7
