Accounting 2020 - Introduction to Managerial Accounting Fall 2019 Excel Project - Cost/Volume/Profit Analysis Soundbytes, Inc. is a manufacturer of personal sound systems. During 2018, the company sold 28,000 MP3 players at a sales price of $104 each. Following are the 2018 financial results: Soundbytes, Inc. Contribution Format Income Statement For the Year 2018 $ 2,912,000 Sales Less variable costs: Direct materials Direct labor Variable overhead Total variable costs Contribution Margin Less fixed costs: Operating Income 504,000 448,000 616,000 1,568,000 $ 1,344,000 880,000 $ 464,000 You are the top managerial accountant at Soundbytes, Inc., and are scheduled to meet with the company's CEO next week to discuss the financial outlook for 2019. In particular, the CEO will want to know what steps the company might take to improve its 2019 income performance. You expect to be asked questions about the impact that various changes in sales price, sales volume, and costs would have on the company's contribution margin, breakeven point, margin of safety, and income. In addition, the CEO is likely to ask this question: if we decrease our labor costs by increasing automation, can we drop our price on MP3 players and increase our sales volume and be better off than in 2019? You obviously won't be able to do these calculations by hand during the meeting, so you have decided to construct an Excel program to do them for you. Page 2 is an example of how the output of your program should look. Please note that the only area of the program that may contain hard-coded numbers is Section A, the only fields that you may change when doing "what if's" are the shaded ones in Section B, and all cells containing a "?" on the attached example (including those in Section C) must contain only formulas. No hard-coded numbers are allowed in any cell containing a "?"! You will be expected to turn in the following: 1. A file containing your program should be submitted via e-mail to "wkwak@unomaha.edu" by the due date (Oct 20, 11:50 p.m.). Please include your name on the program itself, as well as naming the file according to established policy (last name, first initial project #.xls). Also keep a copy of the file in case of transmission failure. 2. Please identify your class either Monday or Online at the Answer Key worksheet. a "2019 what-if?" scenario based on these change factors: '10% 5% Sales in units Sales price per unit Direct materials cost per unit Direct labor cost per unit Variable overhead cost per unit Fixed costs +5% -20% +5% +30% Section C: 2019 What If? Inputs: Section A: 2018 Actual Sales in units 28,000 Sales price per unit 104.00 Direct materials cost per unit 18.00 Direct labor cost per unit 16.00 Variable overhead cost per unit 22.00 Fixed costs $ 880,000 Section B: Change Factors 0% 0% 0% 0% $ 0% 0% 2018 Actual 2019 What-If? Income Projections: Sales Less variable expenses: Direct materials Direct labor Variable overhead Total variable expenses Contribution margin Less fixed expenses Operating income Contribution margin per unit Contribution margin ratio Break-even sales in units Break-even sales in $$ Margin of safety in units Margin of safety in sales $$ Degree of operating leverage Soundbytes, Inc. Financial Projections - 2019 Check figures 2019 Operating income 2019 Break-even sales revenue Accounting 2020 - Introduction to Managerial Accounting Fall 2019 Excel Project - Cost/Volume/Profit Analysis Soundbytes, Inc. is a manufacturer of personal sound systems. During 2018, the company sold 28,000 MP3 players at a sales price of $104 each. Following are the 2018 financial results: Soundbytes, Inc. Contribution Format Income Statement For the Year 2018 $ 2,912,000 Sales Less variable costs: Direct materials Direct labor Variable overhead Total variable costs Contribution Margin Less fixed costs: Operating Income 504,000 448,000 616,000 1,568,000 $ 1,344,000 880,000 $ 464,000 You are the top managerial accountant at Soundbytes, Inc., and are scheduled to meet with the company's CEO next week to discuss the financial outlook for 2019. In particular, the CEO will want to know what steps the company might take to improve its 2019 income performance. You expect to be asked questions about the impact that various changes in sales price, sales volume, and costs would have on the company's contribution margin, breakeven point, margin of safety, and income. In addition, the CEO is likely to ask this question: if we decrease our labor costs by increasing automation, can we drop our price on MP3 players and increase our sales volume and be better off than in 2019? You obviously won't be able to do these calculations by hand during the meeting, so you have decided to construct an Excel program to do them for you. Page 2 is an example of how the output of your program should look. Please note that the only area of the program that may contain hard-coded numbers is Section A, the only fields that you may change when doing "what if's" are the shaded ones in Section B, and all cells containing a "?" on the attached example (including those in Section C) must contain only formulas. No hard-coded numbers are allowed in any cell containing a "?"! You will be expected to turn in the following: 1. A file containing your program should be submitted via e-mail to "wkwak@unomaha.edu" by the due date (Oct 20, 11:50 p.m.). Please include your name on the program itself, as well as naming the file according to established policy (last name, first initial project #.xls). Also keep a copy of the file in case of transmission failure. 2. Please identify your class either Monday or Online at the Answer Key worksheet. a "2019 what-if?" scenario based on these change factors: '10% 5% Sales in units Sales price per unit Direct materials cost per unit Direct labor cost per unit Variable overhead cost per unit Fixed costs +5% -20% +5% +30% Section C: 2019 What If? Inputs: Section A: 2018 Actual Sales in units 28,000 Sales price per unit 104.00 Direct materials cost per unit 18.00 Direct labor cost per unit 16.00 Variable overhead cost per unit 22.00 Fixed costs $ 880,000 Section B: Change Factors 0% 0% 0% 0% $ 0% 0% 2018 Actual 2019 What-If? Income Projections: Sales Less variable expenses: Direct materials Direct labor Variable overhead Total variable expenses Contribution margin Less fixed expenses Operating income Contribution margin per unit Contribution margin ratio Break-even sales in units Break-even sales in $$ Margin of safety in units Margin of safety in sales $$ Degree of operating leverage Soundbytes, Inc. Financial Projections - 2019 Check figures 2019 Operating income 2019 Break-even sales revenue