Flexible Budget Performance Report Project Kelsey's Frozen Confectionaries buys and distributes single-serve ice cream treats, popsicles, etc., such as those sold at convenience stores, sporting events and amusement parks. Download the file Ch 23 Excel Activity containing Kelsey's budget assumptions and actual figures for June. Directions for 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. Do not type a number in any cell. Practice using absolute cell referencing as appropriate in the Master Budget so cells can be easily copied. 2. Use a formula to reference the actuals in the actual column. 3. Build the Flexible Budget by using the actual sales volume achieved along with the original budget assumptions. This is 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. 4. In the Variance column use Excel formulas to calculate the flexible budget variance from actuals. Leave as positive or negative, rather than absolute values. 5. Format the report appropriately (Dollar signs, underscores, double underscores, etc.,) Use the "accounting" or "currency" settings to get dollar signs, commas, and brackets on negative numbers. Add underlines and double underlines as needed. Numbers should be rounded to 0 decimal places. 6. Use the "It" statement function to show the variances as U or F. The "If" statement can be found under "Formulas, Logical". Example for Sales Revenue: =IF(18>=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 will want them to be opposite of one another. Any variance of "o" should be shown as "F" since it meets budget expectations. (Hint:Just change the > to ) 7. Check your answers using the following check figures: Master Budget Operating Income = $211,000 Actual Operating Income = $234,040 Flexible Budget Operating Income = $232,973 Direct Materials Variance 1.750 U 8. Now check to see if everything ripples through the budget if you change an assumption. Change budgeted sales price to $52.00 per case and direct materials to $11.50 per case. New check figures: Master Budget Operating Income = $220,640 Actual Operating Income = $234,040 Flexible Budget Operating Income = $243,317 Direct Materials Variance (30,440) F 9.0Change the sales price assumption back to $50.00 and the direct materials expense assumption back to $10.00. Budget assuptions : Master Budget 20,000 cases 50.00 per case Volume Sales Revenue Kelsey's Frozen Confectionaries Budget Perforrance Report For the ronth ended June 30 Actuals 21, 460 $1,080, 250 $ IASTER BUDGET FLEXIBLE BUDGET Percent sold on credit 90% percent of sales ACTUAL VARIANCE For U Volume (in cases) $ $ Variable Expenses : Direct materials Direct labor Sales Commission Shipping Expense Bad Debt Expense $ $ 10.00 per case 20.00 per case 2. 50 per case 2.00 per case 1.00% percent of credit sal 216, 350 435, 350 53, 650 40, 213 9, 722 $ Fixed Monthly Expenses: Salaries Lease on Distribution cente: $ Depreciation on fleet & Equ: $ Advertising $ Office rent, phone, interne $ 40,000 17,000 12,000 10,000 11,000 43, 350 15, 525 12,000 7, 750 12, 300 Sales Revenue Variable Expenses : Direct materials Direct labor Sales Commissions Shipping Expense Bad debt expense Total Variable Costs Contribution Margin Fixed Expenses : Salaries Lease on Distn center Deprec. on fleet/equip Advertising Office rent, phone, net Total Fixed Costs Operating Income HINTS on EXCEL: 1) if you get a series of hashmarks (######) it just means your column width is wide enough 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 equal or addition sign and then click on the cell you want add, then keystroke the addition sign 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. 3. To multiply, use the * symbol. To divide, use the / symbol. 4. Make use of copy/paste commands when you want similar forumulas in different cells. 5. Always check the logic of your results after entering formulas to make sure the spreadsheet is doing what you really want it to do