1.Answer the following questions:

a.What is the total revenue/spending variance for operating income? $_____________ F or U?

b.What is the Activity variance for operating income? $____________F or U?

c.Does the combination of the revenue/spending variance and activity variance sum to the master budget variance for operating income? Y or N?Should it always sum to the master budget variance? Y or N? Yes

d.How much of the master budget variance for Cost of Goods Sold was caused by an unanticipated increase in volume?__________________ F or U?

e.How much of the master budget variance for Shipping Exp. was caused by an unanticipated increase in volume?_____ ____________ F or U?

f.How much of the master budget variance for Salary Expenses was caused by an unanticipated increase in volume?____ F or U?

g.How much of the master budget variance for COGS was caused by some factor other than volume? (Hint: this is NOT a manufacturing business). $_________ F or U?What could account for this variance?

h.How much of the master budget variance for Sales Revenue was caused by some factor other than volume? $________ F or U?What could account for this variance?

i.How much of the master budget variance for Salary Exp. was caused by some factor other than volume? $__________ F or U?What could account for this variance?

Budget assumptions: Kelsey's Frozen Confectionaries Master Budget Performance Report Volume 18,000 cases For the month ended June 30 Sales Revenue 55.00 per case MASTER Percent sold on credit 95% percent of sales ACTUAL BUDGET VARIANCE VAR. % For U Investigate? (Y/N) Volume (in cases) 20,38 18,000 2,380 13.2% F NO Variable Expenses: CGS 35.00 per case Sales Revenue $ 1,137,630 $ 990,000 $ 147,630 14.9% F NO Sales Commission 2.50 per case Less Variable Expenses: Shipping Expense 2.00 per case Cost of Goods Sold 685,970 $ 630,000 $ 55,970 8.9% U NO Bad Debt Expense 1.50% percent of credit sales Sales Commissions 60,000 45,000 15,000 33.3% U YES Shipping Expense 43,215 $ 36,000 7,215 20.0% U YES Fixed Monthly Expenses: Bad debt expense 15,930 $ 14,108 1,823 12.9% U NO Salaries 45,000 Contribution Margin 332,515 S 264,893 67,623 25.5% F YES Lease on Distribution center 23,000 Less Fixed Expenses Depreciation on fleet & Equip 10,000 Salaries 46,000 $ 45,000 1,000 2.2% U NO Advertising 13,000 Lease on Distn center 15,500 23,000 (7,500) -32.6% F NO Office rent, phone, internet 10,000 Deprec. on fleet/equip 10,000 10,000 0.0% U NO Advertising 8,750 13,000 (4,250) -32.7% F NO Management by exception 20% Investigation rule Office rent, phone, net 12,300 2,300 23.0% U YES Operating Income 239,965 S 163,893 $ 76,073 46.4% F YES HINTS on EXCEL: 1) If you get a series of hashmarks (######) it just means your column width is not wide enough to accomodate the number. Increase column width and/or delete extra decimal places. 2. To add numbers, put your cursor on the cell where you want the answer to be. Kelsey's Frozen Confectionaries Then keystroke the addition sign (+) and then click on the cell you want add, then Fexible Budget Performance Report keystroke the addition sign again (+) and click on the second cell you want to add. For the month ended June 30 Continue in the same fashion for multiple cells. Use the subtraction sign (-) fo subtracting cells. If you have an array of cells to add, you can use the AutoSum button. REVENUE/SPEN FLEXIBLE ACTIVITY 3. To multiply, use the * symbol. To divide, use the / symbol. ACTUAL DING VARAINCE BUDGET VARIANCE MASTER BUDGET Volume (in cases) 20,38 20,380 18,000 4. Make use of copy/paste commands when you want similar forumulas in different cells. Sales Revenue $ 1,137,630 $ 16,730 $ 1,120,900 $ 130,900 $ 990,000 Less Variable Expenses: 5. Always check the logic of your results after entering formulas to make sure the Cost of Goods Sold 685,970 $ (27,330) $ 713,300 $ 83,300 $ 630,000 spreadsheet is doing what you really want it to do. Sales Commissions 60,000 9,050 $ 50,950 5,950 45,000 Shipping Expense 43,215 $ 2,455 40,760 4,760 36,000 Bad debt expense 15,930 $ (43) $ 15,973 1,865 14,108 Contribution Margin 332,515 32,598 299,917 35,025 264,893 Less Fixed Expenses Salaries 46,000 $ 1,000 $ 45,000 $ 45,000 Lease on Distn center 15,500 (7,500) 23,000 23,000 Deprec. on fleet/equip 10,000 10,000 10,000 Advertising 8,750 (4,250) 13,000 13,000 Office rent, phone, net 12,300 2,300 10,000 10,000 Operating Income 239,965 41,048 198,917 35,025 163,893