Question

Fill in the yellow cells with the correct cell references/formulas

A || IU diu la por Jay Cu p- А D BC 1 To aid in planning, Jay Corporation is preparing a contribution format income statement. 3 Budgeted information for Quarter 1 of Year 3: 4 Sales in units 5 Sales price per unit 17,000 48.00 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 Administrative salaries Rent expense Depreciation expense 45,000.00 per quarter 27,000.00 per quarter 36,000.00 per quarter 15 Management has concluded that shipping expense is a mixed cost. Units shipped and the related shipp the last eight quarters are: 17 Year 1 18 Quarter 1 Quarter 2 20 Quarter 3 Quarter 4 22 Year 2 Total Shipping Cost 67,000.00 94,000.00 89,800.00 92,600.00 Units 12,500 21,000 13,800 20,000

Answer 1

Budgeted Info
Sales in Units	17000
Sales price per unit	48
Cost of goods sold	29	per unit
Sales Commissions	9.50%	per Dollar of sales
Admin Salaries	45000	per quarter
Rent Expense	27000	per quarter
Depreciation	36000	per quarter
Year 1	Shipping cost	Units
Q1	67000	12500
Q2	94000	21000
Q3	89800	13800
Q4	92600	20000
Year 2
Q1	72500	13700
Q2	80000	14000
Q3	84000	14300
Q4	100,000	22500
	Shipping cost	Units
High level	100,000	22,500
Low level	67000	12500
Change	33,000	10,000
Variable cost	3.3	per unit
Total cost at high level	100,000
Less: variable	74250
Fixed cost	25,750
Sales		816000
Variable expenses
COGS	493000
Sales Commission	77520
Shipping Expense	56100
Total variable expenses		626620
Contribution Margin		189380
Fixed Expenses:
Admin Salaries	45000
Rent Expense	27000
Shipping Expense	25,750
Depreciation	36000
Total Fixed expenses		133750
Net Operating income		55630

There cell references will be used:

32 33 High level 34 Low level 35 Change Shipping cost Units =B26 =C26 =B18 =C18 =B33-B34 =C33-C34 36 37 Variable cost =B35/C35 per unit 38 39 40 Total cost at high level =B33 41 Less: variable =B37*033 42 Fixed cost =B40-141 43 44 45 =R48RS 48 49 50 Sales 51 Variable expenses 52 COGS =B4*B8 53 Sales Commission =C50*39 54 Shipping Expense =B37*B4 55 Total variable expenses 56 Contribution Margin 57 Fixed Expenses: 58 Admin Salaries =B11 59 Rent Expense =B12 60 Shipping Expense =B42 61 Depreciation =B13 62 Total Fixed expenses 63 Net Operating income =SUM(B52:B54) I=C50-C55 =SUM(B58:361) =C56-C62