ACCTG 231-FALL 2022 EXCEL PROBLEM 2 Due Friday, October 7th at 11:59 pm The purpose of Excel assignments in ACCTG 231 is to provide students experience using a spreadsheet to solve short accounting applications related to material covered in class. Excel Problem #2 requires you to perform simple mathematical functions (add, subtract, multiply, divide). The accounting in this assignment covers topics from Chapter 6: Using CVP analysis to compute profits when prices, costs and/or sales volume changes Using CVP concepts to fill in missing information (taken from Question 9 in the Chapter 6 Connect homework) To begin, please enter the last 5 digits of your student number in the yellow cell indicated (EB). Five of the 10 points can be earned by correctly determining the answers to the questions below. NOTE: when preparing the 2 income statements, for Q2 and Q3, the starting point is the original information provided (i.e. cells ES through E9)- do not use the adjusted income statement in Scenario 1 as a starting point for Scenario 2. The other five points are completing the spreadsheet using formulas, not numbers, in all gray cells. REQUIREMENTS 1. 2. After inserting the last 5 digits of your student number into the yellow cell (E8) for total fixed costs, enter formulas in the gray cells (F5, F6 and F7) to compute the per unit amounts for Sales, Variable Expenses, and Contribution Margin and in cells (H5, H6, and H7) to compute the percentage of sales for Sales, Variable Expenses, and Contribution Margin. (1 POINT) Scenario #1 Insert formulas in the gray cells (E13 through E17) to prepare an income statement assuming advertising expense increased $7,000 and the per unit selling price of the goods increased by $3.00. (NOTE number of units sold remains 10,000) (1 POINT) 3. Scenario #2 4. Insert formulas in the gray cells (H13 through H17) to prepare an income statement, assuming variable expenses decreased by $5.00 per unit and number of units sold decreased by 20%. (Note: make changes based on original data, NOT the income statement prepared for Scenario 1). (1 POINT) Question 4 is NOT related to any of the information used for Questions 1-3. Question 4 is taken directly from the Chapter 6 CONNECT Homework (Question 9). You are provided income statements with missing amounts. Use the income statements, along with information given about units sold and contribution margin per unit and enter formulas to fill in the missing amounts in the gray cells (E21, E24, E25, H23, H25. H26). (1 POINT) 5. The grader will verify the use of formulas (not numbers) for all gray cells. (5 POINTS) C D E G H J Fall 2022 GQ1 ACCTG 231 Number of Units sold Sales Variable Expenses Contribution Margin EXCEL PROBLEM 2 10,000 PER PERCENTAGE TOTAL UNIT OF SALES $ 350,000 $ 245,000 $ 105,000 B Fixed Expenses $ 66,897 9 Net Operating Income $ 38,103 0 1 12 Q2 & Q3 Scenario #1 Scenario #2 13 Sales Variable Expenses 14 15 16 Contribution Margin Fixed Expenses Net Operating Income 17 18 19 20 Q4 Case 1 21 Units Sold Case 2 20,900 22 23 Sales $ 331,200 24 Variable expenses 188,100 25 Contribution margin 26 Fixed expenses 27 Net operating income (loss) $ 161,000 26,200 $ 202,600 28 29 Contribution margin per unit $ 13 $ 14 30 31 32 Insert the last 5 digits of your student number in the yellow highlighted box (cell E8)