How do I answer this and can you show what formulas to use at each step. can you post answer in excel format?
COTTargu y una CURCUMA Edwards & Shaw is a merchandising company that is the sole distributor of a product that is increasing in popularity. The company's income statement for the three most recent months is listed below. Edwards and Shaw Income Statement For the Three Months Ending September July 4,500 August 5,000 September 6,000 Sales in Units $675,000 275,000 400,000 $750,000 300,000 450,000 $900,000 350,000 550,000 Sales Revenue Cost of Goods Sold Gross Margin Operating Expenses: Advertising Expense Shipping Expense Salaries and Commissions Legal Expense Depreciation Expense Total Operating Expenses 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 1. Identify each of the company's individual expenses as either a variable, fixed or mixed cost. 2. Using the high-low method, separate each of the individual mixed cost into the variable and fixed elements. State the cost equation for each individual mixed costs. 3. Edwards and Shaw expect to sell 6,500 units in October. Prepare an absorption income statement for October. 4. Prepare a Contribution Margin Income Statement based on October sales of 6,500 units. Do not combine expenses but show each expense separately in the appropriate category. 5. Calculate the contribution margin per unit and the variable cost ratio. 6. How many units would need to be sold to generate $350,000 in target income? (Round your answer to the nearest unit using the Excel Round Up function.) 7. Give one example of how Edwards and Shaw could increase projected operating income without increasing total sales revenue. 8. Edwards and Shaw are considering a multimedia advertising campaign that should increase sales by $50,000 per month. The ad campaign will cost an additional $1,500 per month and will be considered a fixed cost. How will the ad campaign affect product cost? How will the increase in fixed costs affect the break-even point? Explain. Arial 11 A ab Wrap Text General Paste a. Av V C Merge & Center $ v % ) 60 ,00 00 0 Clipboard Font Alignment Number J78 x fx A B E F G H T L M N 0 P Q R 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 month. For VC list the variable cost per unit for each month. For 1 MC list the total average cost per unit for each mixed cost per month. 2 July August September 3 Fixed Costs 4 5 6 7 8 Variable Costs 9 10 11 12 13 Mixed Costs 14 15 16 17 18 2) Using the high-low method, separate each of the mixed expenses into variable and fixed elements. 19 State the cost equation for each mixed cost. You may have more than one mixed cost. 20 21 Variable Rate 22 Fixed Cost 23 Cost Formula 24 25 It's Ok to type the cost formula directly into the cells, although you will not receive credit if the variable rate 26 and fixed costs are not calculated using Excel cell references and formulas. 27 28 3. Edwards and Shaw expect to sell 6,500 units in October. Prepare an absorption income 29 statement for October (assume we produce 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 same number of units.) Do not combine expenses 48 but show each expense separately in the appropriate category. Income Statement Template Sheet3 + Home Insert Draw Page Layout Formulas Data Review View Tell me X V Arial 11 Ai ab Wrap Text General y Paste BI U av A. == + E Merge & Center $ v % ) 60 .00 00 2.0 Clipboard Font Alignment Number J78 x fx A B D E F G H K L M N 0 P 0 R 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 18 but show each expense separately in the appropriate category. 29 50 Sales in Units 6,500 51 52 Sales Revenue 53 54 55 56 57 58 59 50 51 52 53 54 Operating Income 55 56 57 5) Calculate the contribution margin per unit. Calculate the variable cost ratio. 58 59 20 21 26) Calculate how many units would need to be sold to generate $350,000 in target income. 23 Round up to the nearest unit using the Excel Round Up function. 24 Target Income $ 350,000 75 76 77 78 29 7) Give one example of how Edwards and Shaw could increase projected operating income without increasing 30 total sale revenue. 31 32 33 34 35 8) Edwards and Shaw are considering a multimedia advertising campaign that should increase sales by 36 $50,000 per month. The ad campaign will cost an additional $1,500 per month and will be considered a 37 fixed cost. How will the ad campaign affect product cost? How will the increase in fixed costs affect the 38 break-even point? Explain. 39 0 91 32 93 34 35 96 27 28 39 00 01 02 Income Statement Template Sheet3 +
