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 containing Kelsey's budget assumptions and actual figures for June. The file also contains a draft for both a 1) master budget performance report (planning budget vs. actuals and variances), and 2) a flexible budget performance report. You will be submitting a quiz to record your answers to the completed directions packet (this file) and uploading the completed excel file to a drop box by the date indicated on ICON. DIRECTIONS for Master Budget Performance Report: 1. Use the budget assumptions, along with Excel formulas, to populate the Master Budget column (also known as planning budget or static budget). 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. Do NOT TYPE A NUMBER IN ANY CELL!!! 2. Use a formula to calculate the variance in cell I7: (Actual - Budget). Copy and paste the formula to the rest of the cells in the column. Use the absolute value function as part of your formula so that all variances are positive numbers. Since these numbers do not total down, do not include any underlines in column I. 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. Use three decimals for your percentage. For example, 33.3%. Again, do not include any underlines in column J. 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 "If" statement function to show the variances as U or F. The "If" statement can be found under "Formulas, Logical". Example: =IF(F9>=G9,"F","U"). This formula means "If cell F9 is greater than zero put in "F" for favorable. If this condition is not true, then 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 (normally we show this as "N" but it works better with the IF statement this way.) 6. Check your answers using the following check figures: Budgeted CM= $250,000; Budgeted Op Inc. $149,500; Variance for Commissions = $8,400; Variance percentage for Commissions = 28.0%, Unfavorable. 7. Use management by exception to determine which variances to investigate. HINT: In column M and N, use the "If function" to find the variances that are larger than the decision rule. For the first decision rule you should investigate everything over a certain % of the master budget. So, since the decision rule is in B22 and you want Excel to always compare to B22, so use an absolute reference $B$22. For example, =IF(J9>$B$22,"yes","no"). Complete something similar for column N, but now you are looking at the dollar value variance.