1 X ACTUAL G H 2 Budget assumptions: Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended Rune 30 volume s Sales Revenue 20,000 cases DO por case ACTUAL MASTER BUDGET VARIANCE VAR. Foru vestigate? 6 percent sold on credit 90% percent of sales Volume in cases) 8 Variable Expenses: OGS 10 Sales Commission 11 Shipping Expense 12 Bad Dobe Expense 30.00 percase 2.50 percase 2.00 per case 1.00% percent of credit sal SB/585 13 Sales Revenue Vanable Expenses Cost of Goods Sold Sales Comissions Shipping Expense Haddebtespense Contribution Man Fixed pence Solaris Lease not center Deprecated 14 Fixed Ma turenses is salaries 16 Lease on Distribution center 17 Depreciation on fleet & Equip 18 Advertising 19 Officerent, phone, internet 32312 $ S $ $ $ 40,000 17,000 13,000 10.000 11,000 21 Management by exception 15 investitorul Office ont phone niet HINTS OR EXCEL: 1) if you get a series of hashmarks w i tmans your column width is not wide enough to accomodate the number Increase column width and/or delete extra decimal places 29 Keley's Frozen Confectionaries Fexible Budget Performance Report For the month ended Rune 30 3 2. Tad numbers, put your color on the cell where you want the answer to be. Then teystroke the addition and then dick on the cell you want add the keystroke the additions again and click on the second cell you want to continue in the same fashion for multiple colls. Use the subtraction sin() for subtracting cells. If you have an array of cells to add, you can use the Autohum button FLEXIBLE BUDGET MASTER BUDGET 3. To multiply use the symbol To divide use the symbol Make use of copy/paste commands when you wantlar Performance Report DIRECTIONS for Master Budget Performance Report: 1. Use the budget assumptions, along with Excel formulas, to populate the Master Budget column Note: Your formulas must work such that if ANY of the budget assumptions change, the new assumptions ripple through the entire budget. Part of your grade will be based on whether you correctly formulated the cells. YOU MUST PUT FORMULAS INTO CELLSH! DO NOT TYPE A NUMBER IN ANY CELL!!! 2. Use a formula to calculate the "variance" in cell H7 (Actual - Budget). Copy and paste the formula to the rest of the cells in the column Leave as positive or negative, rather than absolute values 3. Use a formula to calculate the "Variance percentage" NOTE The percentage is the variance as a percent of the Master Budget. Copy and paste the formula to the rest of the cells in the column 4. Format cells appropriately (dollar signs using the accounting or currency format, underlines, double underlines, zero decimal places for dollar amounts, etc.). 5. Use the "it" statement function to show the variances as U or F. The "If statement can be found under "Formulas, Logical". Example: IF(H7>=0,"F","U"). This formula means "If cell H7>0 or H7-0, then mark as "F". If not greater than or equal to 0, mark as "U" Be careful with revenues and expense variances since you'll want them to be opposite of one another. Any variance of "0" should be shown as "F" since it meets budget expectations. 6. Check your answers using the following check figures: Budgeted CM $301,000, Budgeted Op Inc. = $211,000; Variance for Commissions = $8,685; Variance percentage for Commissions = 17.4%, Unfavorable. 7. Answer the following questions: a. What is the master budget variance for Shipping exp.? $ does it mean? |_%, U or F? What %. U or F ? What b. What is the master budget variance for Lease Exp? $ does it mean? Page 1 of 4