Question

FILE HOME INSERT P AGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In Calibri 11 AÁ - A Paste B I U , Alignment Number Cells Editing Conditional Format as Cell Formatting Table Styles Styles Clipboard Font X A1 for To aid in planning, Jay Corporation is preparing a contribution format income o aid in planning, Jay Corporation is preparing a contribution format income statement. udgeted information for Quarter 1 of Year 3: Sales in units Sales price per unit 17,000 48.00 5 .fter analyzing expenses, the company has determined the following cost patterns. Cost of goods sold 29.00 per unit Sales commissions 9.50% per dollar of sales Administrative salaries Rent expense Depreciation expense 45,000.00 per quarter 27,000.00 per quarter 36,000.00 per quarter 30 Management has concluded that shipping expense is a mixed cost. Units shipped and the related shipping cost over he last eight quarters are: ear 1 Quarter 1 Quarter2 Quarter 3 Quarter 4 22 ear 2 23 Quarter 1 ... Total Shipping Cost 67,000.00 94,000.00 89,800.00 92,600.00 Units 12,500 21,000 13,800 20,000 72 son 13700 Sheet1 Sheet1 ... + @ D -- READY Attempt(s) + 100% Show Me Hint

Answer 1

B E F G H I J K L Total Shipping Cost Units 100,000 $ $ 67,000 $ $ 33,000 $ Cell Reference 22,500 MAX(B18:326) MAX(C18:C26) 12,500 MIN(B18:326) MIN(C18:026) 10,000 B33-B34 C33-C34 choose highest units from above 8 quarters and cost against highest units choose lowest units from above 8 quarters and cost against lowest units Difference between high and low activity and units А 30 1. High-Low 31 32 33 High Level of activity 34 low level of activity 35 Change in activity 36 37 Variable Cost 38 39 Compute Fixed Cost 40 Total Cost at high level of activity 41 Less : Variable cost element 42 Total Fixed Cost 43 44 Prepare Contribution format 3.30 Per Unit B35/C35 Divide difference in cost / difference in units $ 100,000.00 74,250.00 25,750.00 B26 (B37*C33) B40-141 take total cost of highest activity calculate variable cost by multiplying variable cost per unit by no of units in high activity total cost - variable cost $ Budgeted Contribution Format Income Statement $ 816,000.00 B4*B5 Units * selling price per unit 493,000.00 77,520.00 74,250.00 B4*B8 C50*B9 B41 SUM(B52:B54) C50-C55 units * COGS per unit Sales * 9.50% Calculated above $ 644,770.00 $ 171,230.00 50 Sales 51 Variable Expense: 52 Cost of Goods Sold 53 Sales Commision 54 Shipping Expense 55 Total Variable Expense 56 Contribution Margin 57 Fixed Expense: 58 Administrative Salaries Rent Expense 60 Shipping Expense 61 Depreciation Expense 62 Total Fixed Expense 63 Net Operating Income 59 45,000.00 27,000.00 25,750.00 36,000.00 B11 B12 B42 B13 SUM(B58:361) C56-C62 given given Calculated above given $ $ 133,750.00 37,480.00 66 Blue color cells are reference and formula, put = sign before these to get answer. Yellow cells are answers that you will get when to put = sign before blue color cell referrences