Flexible Budget l'erformance Report Project Kelscy's Frozen Confectionarica buys and distributes singlo-scrve ice cream treats, popsicles, etc., such as those sold at convenience stores, sporting events and amusement packs. Download the file containing Kelscy's budget assumptions and actual figures for Jace. The file also contains a draft for both a 1) master budget performance report (planning budget vs. actuals and variabees), 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 assumptious, along with Excel formulas, to populate the Master Budget column (also knowu as planning budget or static budget). Note: Your formulas must work such that if ANY of the budget assumptious change, the new assumptious ripple through the cutire budget. Part of your grade will be based on whcthce you correctly formulated the cells. DO NOT TYPE A NUMBER IN ANY CELL!!! Use o formula to calculate the "varin ce in cell H7: (Actunl - Budget). Copy and paste the formula to the rest of the cells in the column. Leove os positive or negative, other 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 Fomat cells appropriately (dollar signs using the accounting or currency format, underlines, double underlincs, zero decimal places for dollar amounts, cc..). Use the "If statement function to show the variances as U OTF. The "It" statement can be found under "Formulas, Logical". Example: -IF(H?>-0,"F", "U"), This formule means "If cell H7>0 or H7-0, then mark ns "F". If not grenter than or equal to 0, mark as "U". Be careful with revenues and expense wriances since you "u want them to be opposite of one another. Any wariance of "o" should shown as "F" since it meets budget expectations. Check your answers using the tillewing check figures: Bulgeted CM- $264,K97; Budgeted Op Inc. - $167,N97; Variance for Commissions - $156100); Variance percentage fix Commissiones 33.3%, Unfavorable. Answer the following questions: 2. 4. 5. 6. 7. 2. What is the master budget variance for Shipping Experise.? $ U or F? What kies it mean? b. What is the master budget variance for Lease Exp?$_ %. U or F? What does it mean? Use mnnngement by exception to determine which variances to investigate. HINT; In columu K, use the "If fimction", along with "Absolute value function" to find the variances that are larger than the decision tule. Also, since the decision rule is in B21 and you want Excel to always compare to B21, you need to make it go absolute reference: $B$21. For example, IF(ABS(KT($13$21),"yes"."no")- 8. 9. Now check to see if everything ripples through the budget if you change an assumption. Try changing the assumed sales volume to 19,130 and Shipping expense to $2.10 per casc. You should have a new total variance for operating income of $61,356, 34.4% variance, F. 10. Change the sales volume assumption back to $18,000 and the shipping expense assumption back to $2.00. DIRECTIONS for Flexible Rudget Performance Report: 1. 2. Cupy and paste all of the line iletis (account names from the master buxgetirilor the Flexible Bulget l'erkurmance report. Copy and paste all of the actual figures from the master budgct to the actual colimo of the Flexible Budget Performance report. Formulate the Master Budget figures by referencing (e...-B7) the appropriate cells in the first performance report, By doing so, if any assumptions change, they will ripple through BOTH perfirmance reports. 3. 4. S. Build the Flexible Budget by using tbe ACTUAL SALES VOLUME achieved nlong with the originnl budget assumptions. This is very similar to building a monster budget, except you are using actual sales volume. Assume the actual sales volume achieved is in the same relevant range as the original budget assumptious. Try using the accounting" or "curreccy settings to get dollar signs, commes, and brackets on negative numbers. Add underlines and double underlines as nooded. Head the appropriate column Por the Activity Variance and then use Excel formulas lo pipulate the cells in that column (0.9., H33-133). Once you got the list cell done, copy and paste the larmula la the other cells. Head the appropriate column for the Revenue Spending Variance and then use Excel formulns to populate the cells in that column (ec.+F33-H33). Agnin, copy and paste the fomulas to the other cells. 6. 7. Formar the report appropriately (Dollar signs, underscores, double underscores, etc..) Use the following check figures to cbeck your formulas so far: Shipping Expense $43,215 $2,455 $40,760 $4,760 $36.000 Lease on Distn center $15,500 $17,500) $23,000 $ $23,000 8. To save time, we won't add the U and F designations to the flexible budget performance report. However, you'll need to mentally figure out which way tbcy go. The interpretation of the activity variance is a little problemantic because it compares two budgets, rather than budget vs. actual HINT: Think of the MASTER BUDGET as the company's target when determining whether the activity variance should be designated as a U or F. 9. Answer the following questions: What is the total revenue spending variance for operating income? A For U? b. What is the Activity variance for operating income?! For L? Does the combination of the revenue spending variance and activity variarice suiti ki the master budget variance for operating in wine? Y of N? Should it always surn to the anaster budget variance? Y or N? How much of the master budget variance for Cost of Goods Sold was caused by an upanticipated increase in volume? For U d. How much of the taste budget variarice for Shipping Exp. was caused by an wanilicipated increase in yoluzie? For U? f. How much of the master budget variance for Salary Expetosca was caused by an unauticipated increase in volume? _For U? U? How much of the master hulspet varince for COGS was caused hy scime factor other dan volume? (Hint: this is NOT al manufacturing business). S For U? What could account for this variance? b. i. How much of the master budget variance for Sales Revenue was caused by soene factor other than volume? $_ For U? What could account for this variance? How much of the master hulget variance for Salery Exp. Will causexl hy we factor adher thor volume? $ FOU? What could account fix this variance? Auditors always look at the variances between actual and budget or actual and last year. Say you were auditing this clicut and they gave you the following story clow). Is the story consistcut with the overall pattern of variances? Which clemcuts of the story are consistent with the cxplanations? Which variabocs are inconsistent or left uncxplained? Be sure to analyze cvery linc item on the budget. (SEE NEXT PAGE.) j. Client Story: In order to motivate our sales force to increase sales, we decides to increase our commissions and salaries and marketing at the same time, our spplier increase its prices, and we fede we could pass that case increase our wont eustomers in the forur of price increase. However, with the additional pressure to make sales, cumpled with the increased sulex price, we had w looser credit terms our salex. We alww had to lease a little more distribrerior space and acyreine urnher true to handle the walware increuse. Our shipping expense relatex do gasoline on deliveries. Luckily, gas prices evid down from what we originally expected this year. In the tuble below, classify EACH ACCOUNT on the budget according to whether the variances in the performance report are consistent or inconsistent with the client's story, or unexplained by the client's story. Place an "X" in the appropriate column, If the Revenue/Spending Variance and Activity Variance differ with respect to an account (Lc., one is consistent and one is inconsistent) then indicate which helongs in which column. Consistent Inconsistent Unesplained Sales revenue Coxst of Cox's Sold Commission Shipping Expense Bad debt cxpense Salaries Lcase of distribution center Depreciation of feet and equip Advertising Oficerent, phone, internet Gming Rubric Summary Possible 10 Criteria Direction packet - completed correctly take quiz to record answers to packet Submit excel file in drop box on ICON for following prints: Mastet Budget Performance Report Lotal 4 points): Correct numbers and percentages Correct U/E Currect Investigativa decision Formatting: Underlines:Dollar signs'blanks % dextra decimal places rernavel Flex budget spreadshort.total 6 points) Correct numbers Correct column labels Formatting: Underlines/Dollar signa/Blanka' cxtra decimal places removed Proper use of formulas: All changes to assumptions ripple through the spreadsheet 1.5 1.0 s s 3.0 Total Budget assumptions: Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 Volume Sales Revenue 18,000 cases 55.00 per case $ MASTER BUDGET Percent sold on credit 95% percent of sales VARIANCE VAR. % For U Investigate? ACTUAL 20,380 Volume (in cases) $ 1,137,630 Variable Expenses: CGS Sales Commission Shipping Expense Bad Debt Expense $ $ $ 35.00 per case 2.50 per case 2.00 per case 1.50% percent of credit sales $ $ $ $ $ 685,970 60,000 43,215 15.930 332,515 Fixed Monthly Expenses: Salaries Lease on Distribution center Depreciation on fleet & Equip Advertising Office rent, phone, internet $ 5 $ $ $ 45,000 23,000 10,000 13,000 10,000 Sales Revenue Less Variable Expenses: Cost of Goods Sold Sales Commissions Shipping Expense Bad debt expense Contribution Margin Less Fixed Expenses: Salaries Lease on Distn center Deprec, on fleet/equip Advertising Office rent, phone, net Operating Income $ $ $ $ $ $ $ 46,000 15,500 10,000 8,750 12,300 239.965 Management by exception 20% Investigation rule HINTS on EXCEL: 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. Kelsey's Frozen Confectionaries Fexible Budget Performance Report For the month ended June 30 FLEXIBLE BUDGET ACTUAL MASTER BUDGET 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. Flexible Budget l'erformance Report Project Kelscy's Frozen Confectionarica buys and distributes singlo-scrve ice cream treats, popsicles, etc., such as those sold at convenience stores, sporting events and amusement packs. Download the file containing Kelscy's budget assumptions and actual figures for Jace. The file also contains a draft for both a 1) master budget performance report (planning budget vs. actuals and variabees), 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 assumptious, along with Excel formulas, to populate the Master Budget column (also knowu as planning budget or static budget). Note: Your formulas must work such that if ANY of the budget assumptious change, the new assumptious ripple through the cutire budget. Part of your grade will be based on whcthce you correctly formulated the cells. DO NOT TYPE A NUMBER IN ANY CELL!!! Use o formula to calculate the "varin ce in cell H7: (Actunl - Budget). Copy and paste the formula to the rest of the cells in the column. Leove os positive or negative, other 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 Fomat cells appropriately (dollar signs using the accounting or currency format, underlines, double underlincs, zero decimal places for dollar amounts, cc..). Use the "If statement function to show the variances as U OTF. The "It" statement can be found under "Formulas, Logical". Example: -IF(H?>-0,"F", "U"), This formule means "If cell H7>0 or H7-0, then mark ns "F". If not grenter than or equal to 0, mark as "U". Be careful with revenues and expense wriances since you "u want them to be opposite of one another. Any wariance of "o" should shown as "F" since it meets budget expectations. Check your answers using the tillewing check figures: Bulgeted CM- $264,K97; Budgeted Op Inc. - $167,N97; Variance for Commissions - $156100); Variance percentage fix Commissiones 33.3%, Unfavorable. Answer the following questions: 2. 4. 5. 6. 7. 2. What is the master budget variance for Shipping Experise.? $ U or F? What kies it mean? b. What is the master budget variance for Lease Exp?$_ %. U or F? What does it mean? Use mnnngement by exception to determine which variances to investigate. HINT; In columu K, use the "If fimction", along with "Absolute value function" to find the variances that are larger than the decision tule. Also, since the decision rule is in B21 and you want Excel to always compare to B21, you need to make it go absolute reference: $B$21. For example, IF(ABS(KT($13$21),"yes"."no")- 8. 9. Now check to see if everything ripples through the budget if you change an assumption. Try changing the assumed sales volume to 19,130 and Shipping expense to $2.10 per casc. You should have a new total variance for operating income of $61,356, 34.4% variance, F. 10. Change the sales volume assumption back to $18,000 and the shipping expense assumption back to $2.00. DIRECTIONS for Flexible Rudget Performance Report: 1. 2. Cupy and paste all of the line iletis (account names from the master buxgetirilor the Flexible Bulget l'erkurmance report. Copy and paste all of the actual figures from the master budgct to the actual colimo of the Flexible Budget Performance report. Formulate the Master Budget figures by referencing (e...-B7) the appropriate cells in the first performance report, By doing so, if any assumptions change, they will ripple through BOTH perfirmance reports. 3. 4. S. Build the Flexible Budget by using tbe ACTUAL SALES VOLUME achieved nlong with the originnl budget assumptions. This is very similar to building a monster budget, except you are using actual sales volume. Assume the actual sales volume achieved is in the same relevant range as the original budget assumptious. Try using the accounting" or "curreccy settings to get dollar signs, commes, and brackets on negative numbers. Add underlines and double underlines as nooded. Head the appropriate column Por the Activity Variance and then use Excel formulas lo pipulate the cells in that column (0.9., H33-133). Once you got the list cell done, copy and paste the larmula la the other cells. Head the appropriate column for the Revenue Spending Variance and then use Excel formulns to populate the cells in that column (ec.+F33-H33). Agnin, copy and paste the fomulas to the other cells. 6. 7. Formar the report appropriately (Dollar signs, underscores, double underscores, etc..) Use the following check figures to cbeck your formulas so far: Shipping Expense $43,215 $2,455 $40,760 $4,760 $36.000 Lease on Distn center $15,500 $17,500) $23,000 $ $23,000 8. To save time, we won't add the U and F designations to the flexible budget performance report. However, you'll need to mentally figure out which way tbcy go. The interpretation of the activity variance is a little problemantic because it compares two budgets, rather than budget vs. actual HINT: Think of the MASTER BUDGET as the company's target when determining whether the activity variance should be designated as a U or F. 9. Answer the following questions: What is the total revenue spending variance for operating income? A For U? b. What is the Activity variance for operating income?! For L? Does the combination of the revenue spending variance and activity variarice suiti ki the master budget variance for operating in wine? Y of N? Should it always surn to the anaster budget variance? Y or N? How much of the master budget variance for Cost of Goods Sold was caused by an upanticipated increase in volume? For U d. How much of the taste budget variarice for Shipping Exp. was caused by an wanilicipated increase in yoluzie? For U? f. How much of the master budget variance for Salary Expetosca was caused by an unauticipated increase in volume? _For U? U? How much of the master hulspet varince for COGS was caused hy scime factor other dan volume? (Hint: this is NOT al manufacturing business). S For U? What could account for this variance? b. i. How much of the master budget variance for Sales Revenue was caused by soene factor other than volume? $_ For U? What could account for this variance? How much of the master hulget variance for Salery Exp. Will causexl hy we factor adher thor volume? $ FOU? What could account fix this variance? Auditors always look at the variances between actual and budget or actual and last year. Say you were auditing this clicut and they gave you the following story clow). Is the story consistcut with the overall pattern of variances? Which clemcuts of the story are consistent with the cxplanations? Which variabocs are inconsistent or left uncxplained? Be sure to analyze cvery linc item on the budget. (SEE NEXT PAGE.) j. Client Story: In order to motivate our sales force to increase sales, we decides to increase our commissions and salaries and marketing at the same time, our spplier increase its prices, and we fede we could pass that case increase our wont eustomers in the forur of price increase. However, with the additional pressure to make sales, cumpled with the increased sulex price, we had w looser credit terms our salex. We alww had to lease a little more distribrerior space and acyreine urnher true to handle the walware increuse. Our shipping expense relatex do gasoline on deliveries. Luckily, gas prices evid down from what we originally expected this year. In the tuble below, classify EACH ACCOUNT on the budget according to whether the variances in the performance report are consistent or inconsistent with the client's story, or unexplained by the client's story. Place an "X" in the appropriate column, If the Revenue/Spending Variance and Activity Variance differ with respect to an account (Lc., one is consistent and one is inconsistent) then indicate which helongs in which column. Consistent Inconsistent Unesplained Sales revenue Coxst of Cox's Sold Commission Shipping Expense Bad debt cxpense Salaries Lcase of distribution center Depreciation of feet and equip Advertising Oficerent, phone, internet Gming Rubric Summary Possible 10 Criteria Direction packet - completed correctly take quiz to record answers to packet Submit excel file in drop box on ICON for following prints: Mastet Budget Performance Report Lotal 4 points): Correct numbers and percentages Correct U/E Currect Investigativa decision Formatting: Underlines:Dollar signs'blanks % dextra decimal places rernavel Flex budget spreadshort.total 6 points) Correct numbers Correct column labels Formatting: Underlines/Dollar signa/Blanka' cxtra decimal places removed Proper use of formulas: All changes to assumptions ripple through the spreadsheet 1.5 1.0 s s 3.0 Total Budget assumptions: Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 Volume Sales Revenue 18,000 cases 55.00 per case $ MASTER BUDGET Percent sold on credit 95% percent of sales VARIANCE VAR. % For U Investigate? ACTUAL 20,380 Volume (in cases) $ 1,137,630 Variable Expenses: CGS Sales Commission Shipping Expense Bad Debt Expense $ $ $ 35.00 per case 2.50 per case 2.00 per case 1.50% percent of credit sales $ $ $ $ $ 685,970 60,000 43,215 15.930 332,515 Fixed Monthly Expenses: Salaries Lease on Distribution center Depreciation on fleet & Equip Advertising Office rent, phone, internet $ 5 $ $ $ 45,000 23,000 10,000 13,000 10,000 Sales Revenue Less Variable Expenses: Cost of Goods Sold Sales Commissions Shipping Expense Bad debt expense Contribution Margin Less Fixed Expenses: Salaries Lease on Distn center Deprec, on fleet/equip Advertising Office rent, phone, net Operating Income $ $ $ $ $ $ $ 46,000 15,500 10,000 8,750 12,300 239.965 Management by exception 20% Investigation rule HINTS on EXCEL: 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. Kelsey's Frozen Confectionaries Fexible Budget Performance Report For the month ended June 30 FLEXIBLE BUDGET ACTUAL MASTER BUDGET 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