8. Use management by exception to determine which variances to investigate. HINT: In column K use the "If function", along with "Absolute value function to find the variances that are larger than the decision rule. Also, since the decision rule is in B21 and you want Excel to always compare to B21, you need to make it an absolute reference: $B$21. For example, IF(ABS(K7)>($B$21)."yes","no"). 9. Now check to see if everything ripples through the budget if you change an assumption. changing the assumed sales volume to 20,500 and Shipping expense to $2.10 per case. should have a new total variance for operating income of $21,287,9.8% variance, F. Try You 10. Change the sales volume assumption back to $20,000 and the shipping expense assumption back to $2.00. DIRECTIONS for Flexible Budget Performance Report: 1. Copy and paste all of the line items (account names) from the master budget into the Flexible Budget Performance report. 2. Copy and paste all of the actual figures from the master budget to the actual column of the Flexible Budget Performance report. 3. Formulate the Master Budget figures by referencing (e.g.=B7) the appropriate cells in the first performance report. By doing so, if any assumptions change, they will ripple through BOTH performance reports. 4. Build the Flexible Budget by using the ACTUAL SALES VOLUME achieved along with the original budget assumptions. This is very similar to building a master budget, except you are using actual sales volume. Assume the actual sales volume achieved is in the same relevant range as the original budget assumptions. Try using the "accounting" or "currency" settings to get dollar signs, commas, and brackets on negative numbers. Add underlines and double underlines as needed. 5. Head the appropriate column for the Volume Variance and then use Excel formulas to populate the cells in that column (e.g.. +H33-333). Once you get the first cell done, copy and paste the formula to the other cells 2 Budget assumptions: Kelsey's Frozen Confectionaries Master budget Performance Report For the month ended une 30 20,000 cases 50,00 perc 5 Sales Revenue MASTER ACTUAL VARIANCE VAR % Foru investigate? 6 Percent sold on credit 90% percent of sales Volume (in cases) 8 Variable Expenses: 9 OGS 10 Sales Commission 11 Shipping Expense 12 Bad Debt Expense $ $ 0.00 per case 2.50 per case 2.00 per case 1.00% percent of credit sales SR Lots Variable Expenses Cost of Goods Sold Sales Commissions Shipping Expense Bad debt expense Contribution Margin Les Fixed Expenses 14 Fixed Monthly expenses: 15 salaries 16 Lease on Dutribution center 17 Depreciation on feeq uip 18 Advertung 19 Officerent, phone, internet $ $ $ $ $ 60,000 17,000 12,000 10,000 11,000 Stres 15.500 12.00 2.750 Deprec. on fleet/equip Advertising Officerent, phone, at Operating income 21 Management by exception 19 vestigation rule HINTS on EXCEL 1) if you get a series of thashmarks its means your column width is not wide enough to accomodate the number increase column width and/or delete extra decimal places Kebey's Frozen Confectionaries Fexible Budget Performance Report For the month ended June 30 2. To add numbers, put your cursor on the cell where you want the answer to be. Then leystroke the additions and then dick on the cell you want add, the 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 subtractions ) for subtracting cells. If you have an array of cells to add, you can use the Autosum button ACTUM FLEXIBLE BUDGET MASTER BUDGET 3. To multiply use the symbol. To divide, use the symbol 4. Make use of copy/paste commands when you want similar Performance Report