Because of the importance of fluency in Excel for many, if not all business majors, ACCTG 231 has replaced two Comprehensive Problems with Excel Problems this semester. This assignment is based on material covered in Chapter 6 and requires you to compute your answers, using a few very basic Excel functions, on the spreadsheet provided. The spreadsheet includes information related to a company's current unit sales and total dollar amounts for sales, variable costs and contribution margin. It also includes a space for you to input the last 5 digits of your student number, which is total fixed costs. There are templates with cells highlighted in gray (and referenced by cell location in the directions) for you to input all answers. To begin, please enter the last 5 digits of your student number in the yellow cell indicated (D8). Five of the 10 points can be earned by correctly determining the answers to the questions below. NOTE: when preparing the 3 income statements, the starting point is the original information provided (i.e. cells D5 through D8) - do not incorporate changes made in scenario #1 into scenario #2, etc. The other five points are earned by correctly setting up the spreadsheet so that when the grader changes the number of units sold from 10,000 to 20,000, the spreadsheet recomputes the answers correctly. REQUIREMENTS 1. After inserting the last 5 digits of your student number into the yellow cell (D8) for total fixed costs, enter formulas in the gray cells (E5, E6 and E7) to compute the per unit amounts for Sales, Variable Expenses, and Contribution Margin. (1 POINT) 2. Insert formulas in the gray cells (G5, G6, and G7) to compute the percentage of sales for Sales, Variable Expenses, and Contribution Margin. (1 POINT) 3. Scenario #1 Insert formulas in the gray cells (G12 through G16) to prepare an income statement, assuming sales increased by 1,000 units. (1 POINT) 4. Scenario #2 Insert formulas in the gray cells (112 through 116) to prepare an income statement, assuming advertising increased by $10,000, and unit sales increased by 20%. (1 POINT) 5. Scenario #3 Insert formulas in the gray cells (K12 through K16) to prepare an income statement, assuming variable expenses increased by $5.00 per unit and unit sales increased by 15%. (1 POINT) 6. When your assignment is being graded, the grader will change the number of units sold (cell B3) from 10,000 to 20,000 to verify that the formulas you entered will correctly compute the required amounts with this change in data. (5 POINTS) Number of Units sold PER UNIT PERCENTAGE OF SALES Sales Variable Expenses Contribution Margin Fixed Expenses Net Operating Income 10,000 TOTAL $ $ $ 250,000.00 100,000.00 150,000.00 Insert the last 5 digits of your student number $ 150,000.00 Scenario #1 Scenario #2 Scenario #3 Sales Variable Expenses Contribution Margin Fixed Expenses Net Operating Income
