3. Edwards and Shaw expect to sell 6,500 units in October. Prepare an absorption income statement for October (assume we produce and sell the same number of units).

Please use Excel Formulas

17 - x fx A B C D E F G H 1 2 3 Edwards & Shaw Income Statement For the Three Months Ending September 30th 4 5 6 July August September 7 8 Sales in Units 4,500 5,000 6,000 $ 675,000 $ 275,000 400,000 750,000 $ 300,000 450,000 900,000 350,000 550,000 9 10 11 12 13 14 15 16 17 18 19 20 Sales Revenue Cost of Goods Sold Gross Margin Operating Expenses: Advertising expense Shipping expense Salaries and commissions Legal expense Depreciation expense Total Operating Expense Operating Income 11,000 27,000 127,500 7,000 10,000 182,500 217,500 $ 11,000 30,000 135,000 7,000 10,000 193,000 257,000 $ 11,000 36,000 150,000 7,000 10,000 214,000 336,000 $ 21 22 23 Income Statement Template Sheet3 + R13 fx A B C D E F G H I J L M . N o A A A A 1) Identify each of the company's expenses as either variable, fixed, or mixed. For the FC list the total fixed costs per month for each 1 month. For VC list the variable cost per unit for each month. For MC list the total average cost per unit for each mixed cost per month. 2 July August September 3 Fixed Costs 4 Advertising Expense $ 11,000.00 $ 11,000.00 $ 11,000.00 5 Legal Expense 7,000.00 $ 7,000.00 $ 7,000.00 6 Depreciation Expense $ 10,000.00 $ 10,000.00 $ 10,000.00 7 Total Fixed Cost $ 28,000.00 $ 28,000.00 $ 28,000.00 8 Variable Costs 9 Shipping Expense $ 27,000.00 $ 30,000.00 $ 36,000.00 10 Total Varible Cost $ 27,000.00 $ 30,000.00 $ 36,000.00 11 Sales in units 4,500 5,000 6,000 12 Varible Cost per unit 6.00 $ 6.00 $ 6.00 13 Mixed Costs 14 Cost of Goods Sold $ 275,000.00 $ 300,000.00 $ 350,000.00 15 Salaries and Commissions $ 127,500.00 $ 135,000.00 $ 150,000.00 16 Total Mixed Costs $ 402,500.00 $ 435,000.00 $ $ 500,000.00 17 Sales in units 4,500 5,000 6,000 18 Total Average Cost Per Unit $ 89.44 $ 87.00 $ 83.33 $ $ A A A 2) Using the high-low method, separate each of the mixed expenses into variable and fixed elements. State the cost equation for each 19 mixed cost. You may have more than one mixed cost. 20 Cost of Goods Sold Salaries and Commisions 21 Variable Rate $ 50.00 $ 15.00 Per Unit It's Ok to type the cost formula directly into the cells, although you will not receive credit if the variable rate and fixed 22 Fixed Cost $ 50,000.00 $ 60,000.00 costs are not calculated using Excel cell references and formulas. 23 Cost Formula y= $50,000+ $50x y=$60,000+$15x 24 R13 x v fx A B C D E F G H K L M N 0 2) Using the high-low method, separate each of the mixed expenses into variable and fixed elements. State the cost equation for each 19 mixed cost. You may have more than one mixed cost. 20 Cost of Goods Sold Salaries and Commisions 21 Variable Rate $ 50.00 $ 15.00 Per Unit It's Ok to type the cost formula directly into the cells, although you will not receive credit if the variable rate and fixed 22 Fixed Cost $ 50,000.00 $ 60.000.00 costs are not calculated using Excel cell references and formulas. 23 Cost Formula y= $50,000 + $50x y=$60,000+$15x 24 25 26 27 28 3. Edwards and Shaw expect to sell 6,500 units in October. Prepare an absorption Income statement for October (assume we produce 29 and sell the same number of units). 30 31 Sales in Units 6,500 32 33 Sales Revenue 34 35 36 37 38 39 40 41 42 43 44 45 Operating Income 46 47 4) Prepare a Contribution Margin Income Statement based on the October sales of 6,500 units (assume we produce and sell the 48 same number of units.) Do not combine expenses but show each expense separately in the appropriate category. 49 FACUL CA