File Home Insert Draw Page Layout Formulas Calibri 11 v ' ' Paste BIU v Data Review View Help x = F Alignment Custom $ % 500 Number 0 Comments Share Insert 28 Delete Conditional Format as Formatting Table Styles Cell Format Styles Cells Sort & Find & Filter Select Editing Analyze Data Analysis Undo Clipboard Font > J41 X fx =J35-SUM(J37:J40) A B C D E F G 2 Budget assumptions: 3 4 Volume 20,000 cases H Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 K L M N 5 Sales Revenue $ 50.00 per case 6 Percent sold on credit 90% percent of sales ACTUAL MASTER BUDGET VARIANCE VAR.% F or U Investigate? 7 Volume (in cases) 21,460 20,000 1,460 7.30% F No 8 COGS $ 30.00 per case 9 Direct materials per case 300 Treats Sales Revenue $ 1,131,020 $ 1,000,000 $ 131,020 13.10% F 10 $ 0.10 per treat 11 12 Variable Expenses: Less Variable Expenses: Cost of Goods Sold Sales Commissions $ 682,880 $ 600,000 $ 82,880.00 13.81% U $ 58,685 $ 50,000 $ 8,685 13 Sales Commission 2.50 per case Shipping Expense $ 44,213 $ 40,000 $ 4,213 17.37% 10.53% U U 14 Shipping Expense $ 2.00 per case Bad debt expense $ 16,930 $ 15 Bad Debt Expense 1.00% percent of credit sales Contribution Margin $ 328,312 $ 9,000 $ 301,000 $ 27,312 7,930 88.11% U 9.07% F 16 Less Fixed Expenses: 17 Fixed Monthly Expenses: Salaries $ 43,000 $ 40,000 $ 3,000 7.50% U 18 Salaries $ 40,000 Lease on Distn center $ 15,500 $ 17,000 $ (1,500) -8.82% F 19 Lease on Distribution center $ 17,000 Deprec. on fleet/equip 12,000 $ 12,000 $ 0.00% F 20 Depreciation on fleet & Equip $ 12,000 Advertising $ 7,750 $ 10,000 $ (2,250) 21 Advertising 10,000 Office rent, phone, net $ 12,300 $ 22 Office rent, phone, internet $ 11,000 Operating Income $ 237,762 $ 11,000 $ 211,000 $ 1,300 26,762 -22.50% 11.82% 12.68% F U F 2 222 222 22 Yes Yes Yes 23 24 25 Management by exception 15% Investigation rule File Home Insert Draw Page Layout Formulas Data Review View Help 5 Calibri 11 ' ' Custom Paste BIU v v $ % Font > Alignment > Number =J35-SUM(J37:J40) Undo Clipboard J41 A X fx C Conditional Format as Formatting Table Cell Styles Styles V Comments Share Insert 98 Delete Sort & Find & Format Filter Select Analyze Data Cells Editing Analysis E F G H J K M N JL 33 34 35 36 37 38 39 40 41 42 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. Then keystroke the addition sign (+) and then click on the cell you want add, then keystroke the addition sign again (+) and click on the second cell you want to add. Continue in the same fashion for multiple cells. Use the subtraction sign (-) for subtracting cells. If you have an array of cells to add, you can use the AutoSum button. Volume (in cases) 21,460 $ U $ 21,460 $ 1,460 F 20,000 Sales Revenue $ 1,131,020 $ 58,020 F S 1,073,000 $ 73,000 F $ 1,000,000 Less Variable Expenses: Cost of Goods Sold Sales Commissions $ 682,880 $ 39,080 U $ 643,800 $ 43,800 F $ 600,000 $ 58,685 $ 5,035 U $ 53,650 $ 3,650 U $ 50,000 Shipping Expense Bad debt expense $ 44,213 $ 1,293 U $ 42,920 $ 2,920 U $ 40,000 $ 16,930 $ 7,273 F $ 9,657 $ 657 U $ 9,000 Contribution Margin $ 328,312 $ 5,339 F $ 322,973 $ 21,973 F $ 301,000 Less Fixed Expenses: 43 Salaries $ 43,000 $ 3,000 U $ 40,000 $ - U $ 40,000 44 3. To multiply, use the symbol. To divide, use the / symbol. Lease on Distn center $ 15,500 $ (1,500) F $ 17,000 $ U $ 17,000 45 Deprec. on fleet/equip 12,000 $ F $ 12,000 $ U $ 12,000 46 47 4. Make use of copy/paste commands when you want similar forumulas in different cells. Advertising $ 7,750 $ (2,250) F $ 10,000 $ U $ 10,000 Office rent, phone, net $ 12,300 $ 1,300 U $ 11,000 $ U $ 11,000 48 Operating Income $ 237,762 $ 4,789 F $ 232,973 $ 21,973 F $ 211,000 5. Always check the logic of your results after entering formulas to 49 make sure the spreadsheet is doing what you really want it to do. 50 51 52 53 54 55 56 Cases $ Direct materials (COGS) $ Actual Results Price Variance 21,460 682,880 $ For U Actual Input Qty x Budgeted Price Efficiency Variance F or U Flexible Budget 60,540 U $ 622,340 $ 22,340 U $ 600,000 File Home Insert Draw 2. Calibri 11 v ' ' Page Layout Formulas Data Review View Help Custom Paste BIU x v x v Font =J35-SUM(J37:J40) Undo Clipboard J41 X fx B C A 26 27 Actual Results 28 Number of cases sold 29 Direct materials 30 Direct material cost per unit $ 31 21,460 6,223,400 Treats 0.1097 Per Treat D E HINTS on EXCEL: 32 33 34 35 36 37 38 39 40 41 42 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. Then keystroke the addition sign (+) and then click on the cell you want add, then keystroke the addition sign again (+) and click on the second cell you want to add. Continue in the same fashion for multiple cells. Use the subtraction sign (-) for subtracting cells. If you have an array of cells to add, you can use the AutoSum button. 43 44 3. To multiply, use the symbol. To divide, use the / symbol. 45 46 47 4. Make use of copy/paste commands when you want similar forumulas in different cells. 48 49 5. Always check the logic of your results after entering formulas to make cure the enroadcheat is doing wht vou really want it to do Comments Share Insert x Delete $ 3x % 500 0 Conditional Format as Cell Formatting Table Styles Alignment Number Styles Format Cells Filter Sort & Find & Select Analyze Data Editing Analysis F G H Kelsey's Frozen Confectionaries Flexible Budget Performance Report FLEXIBLE For the month ended June 30 K M N BUDGET ACTUAL VARIANCE F or U FLEXIBLE BUDGET SALES-VOLUME VARIANCE F or U MASTER BUDGET Volume (in cases) 21,460 $ U $ 21,460 $ 1,460 F 20,000 Sales Revenue $ 1,131,020 $ 58,020 F $ 1,073,000 $ 73,000 F $ 1,000,000 Less Variable Expenses: Cost of Goods Sold Sales Commissions $ 682,880 $ 39,080 U $ 643,800 $ 43,800 F $ 600,000 $ 58,685 $ 5,035 U $ 53,650 $ 3,650 U $ 50,000 Shipping Expense $ 44,213 $ 1,293 U $ 42,920 $ 2,920 U $ 40,000 Bad debt expense $ 16,930 $ 7,273 F $ 9,657 $ 657 U $ 9,000 Contribution Margin $ 328,312 $ 5,339 F $ 322,973 $ 21,973 F $ 301,000 Less Fixed Expenses: Salaries $ 43,000 $ 3,000 U $ 40,000 $ - U $ 40,000 Lease on Distn center $ 15,500 $ (1,500) F $ 17,000 $ U $ 17,000 Deprec. on fleet/equip 12,000 $ F $ 12,000 $ U $ 12,000 Advertising $ 7,750 $ (2,250) F $ 10,000 $ U $ 10,000 Office rent, phone, net $ 12,300 $ 1,300 U $ 11,000 $ U $ 11,000 Operating Income $ 237,762 $ 4,789 F $ 232,973 $ 21,973 F $ 211,000