4) Prepare a Contribution Margin Income Statement based on the October sales of 6,500 units (assume we produce and sell the same number of units.) Do not combine expenses but show each expense separately in the appropriate category.

5) Calculate the contribution margin per unit. Calculate the variable cost ratio.

please use excel formulas thank you

C46 x fx A B C D E F G J ZT 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 $ 975,000.00 34 Cost of Goods Sold $ 375,000.00 35 Gross Profit $ 600,000.00 36 Operating Expenses 37 Advertising Expenses 11,000.00 38 Shipping Expense $ 39,000.00 39 Salaries and Commissions 157,500.00 40 Legal Expense $ 7,000.00 41 Depreciation Expense $ 10,000.00 42 Total Operating expenses $ (224,500.00) 43 44 45 Operating Income $ 375,500.00 46 47 4) Prepare a Contribution Margin Income Statement based on the October sales of 6,500 units (assume we produce and sell 48 the same number of units.) Do not combine expenses but show each expense separately in the appropriate category. 49 50 Sales in Units 6,500 51 52 Sales Revenue 53 54 55 56 57 58 59 60 61 62 63 64 Operating Income 65 66 67 5) Calculate the contribution margin per unit. Calculate the variable cost ratio. 68 69 Income Statement Template Sheet3 + Ready 9 + 100% 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 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 +