FILE HOME INSERT P AGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In Calibri 11 AÁ -...

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