Extra credit (1)

Excel SIM: High-Low Method; Contribution Format Income Statement 1. Analyze a mixed cost using a scattergraph plot and the high-low method. 2. Prepare income statements for a merchandising company using the traditional and contribution formats. High-Low Method; Contribution Format Income Statement - Excel ? 0 - 5 X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In Calibri - 11 - A" A' % M Paste BIU - - - A . Alignment Number Conditional Format as Cell Cells Formatting * Table Styles Editing Clipboard Font Styles H13 vix VA B D E 1 To aid in planning, Jay Corporation is preparing a contribution format income statement. 2 Budgeted information for Quarter 1 of Year 3: 4 Sales in units 17,000 5 Sales price per unit 48.00 5 7 After analyzing expenses, the company has determined the following cost patterns. 8 Cost of goods sold 29.00 per unit Sales commissions 9.50% per dollar of sales 0 Administrative salaries 45,000.00 per quarter 12 Rent expense 27,000.00 per quarter 13 Depreciation expense 36,000.00 per quarter References 5 Management has concluded that shipping expense is a mixed cost. Units shipped and the related shipping cost over the last eight quarters are : 7 Year 1 Total Shipping Cost Units 18 Quarter 1 67,000.00 12,500 Quarter2 94,000.00 21,000 Quarter 3 39,800.0 13,800 Quarter 4 92,600.00 20,000 2 Year 2 3 72,500.00 13,700 24 Quarter2 80,000.00 14,000 25 Quarter 3 84,000.00 14,300 26 Quarter 4 100,000.00 22,500 References 27 28 Use the data to answer the following 0 1. Using the high-low method, determine a cost formula for shipping expenses. 32 Total Shipping Cost Units 33 High level of activity 34 Low level of activity 35 Change in activity 6 37 Variable cost per unit 38 39 Compute the fixed cost portion using the high level of activity. 40 Total cost at high level of activity 41 Less: Variable cost element 42 Total fixed cost References 43 44 2. Prepare a contribution format income statement for Quarter 1 of Year 3. 45 46 Jay Corporation 47 Budgeted Contribution Format Income Statement 48 For Year 3, Quarter 1 49 50 Sales 51 Variable expenses: 52 Cost of goods sold 53 Sales commissions 4 Shipping expense 5 Total variable expenses 56 Contribution margin 57 Fixed expenses: 68 Administrative salaries 59 Rent expense Shipping expense 51 Depreciation expense $2 Total fixed expenses 63 Net operating income