Please use excel formulas for answers

High-Low Method; Income Statement - Excel ? FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In 1 EE Calibri - 11 AA % Paste BIU Alignment Number Conditional Format as Cell Formatting Table Styles Cells Clipboard Font Styles B53 =C50*B9 D E F G H i Jay Corporation has decided to prepare income statements for internal planning. 2 3 Budgeted information for Quarter 1 of Year 3: Sales in units 17,000 Sales price per unit $ 48.00 4 6 7 After analyzing expenses, the company has determined the following cost patterns. 8 Cost of Goods Sold (per unit) $ 29.00 Sales Commissions (per dollar of sales) 9.50% 9 10 11 12 Administrative Salaries (per quarter) Rent Expense (per quarter) Depreciation Expense (per quarter) $ $ $ 45,000.00 27.000.00 36,000.00 13 14 15 Shipping has been determined to be a mixed cost with the following total costs and units: 16 17 Year 1 Total Cost Units 18 Quarter 1 $ 67,000.00 12,500 19 Quarter2 $ 94,000.00 21,000 20 Quarter 3 $ 89,800.00 13,800 21 Quarter 4 S 92,600.00 20,000 22 Year 2 23 Quarter 1 $ 72,500.00 13,700 24 Quarter2 $ 80,000.00 14,000 25 Quarter 3 $ 84,000.00 14,300 26 Quarter 4 S 100,000.00 22,500 27 28 Use the data to answer the following. Replace every ? with a formula. 30 1. Using the high-low method, determine a cost formula for shipping expenses. 31 Sheet1 + 20% READY Attempt(s) 0/3 Hint Show Me D Units A B 32 Total Cost 33 High level of activity 34 Low level of activity 35 Change in activity 36 37 Variable cost per unit 38 39 Fixed cost portion: 40 Total cost at high level of activity 41 Less: Variable cost element 42 Total fixed cost 43 44 2. Prepare an income statement for Quarter 1 of Year 3. 45 Jay Corporation 47 Income Statement 48 For Year 3, Quarter 1 49 46 50 Sales $ 816,000.00 493,000.00 77,520.00 51 Variable expenses: 52 Cost of goods sold 53 Sales commissions 54 Shipping expense 55 Total variable expenses 56 Contribution margin 57 Fixed expenses: 58 Administrative salaries 59 Rent expense 60 Shipping expense 61 Depreciation expense 62 Total fixed expenses 63 Net operating income