Budget assumptions: 3 4 5 6 Volume Sales Revenue Percent sold on credit $ 20,000 cases 50.00 per case 90% percent of sales 8 Variable Expenses: 9 CGS $ 30.00 per case 10 Sales Commission $ 2.50 per case 11 Shipping Expense $ 2.00 per case 12 Bad Debt Expense 1.00% percent of credit sales 13 14 Fixed Monthly Expenses: 15 Salaries $ 40,000 16 Lease on Distribution center $ 17,000 17 Depreciation on fleet & Equip $ 12,000 18 Advertising $ 10,000 19 Office rent, phone, internet $ 11,000 20 21 Management by exception 15% Investigation rule 22 23 HINTS on EXCEL: 24 1) If you get a series of hashmarks (Host) it just means your column 25 width is not wide enough to accomodate the number. Increase column width and/or delete extra decimal places. 27 28 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. 3. To multiply, use the symbol. To divide, use the / symbol 37 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 11 26 29 30 31 32 33 34 35 36 38 39 40 41 Performance Report Kelsey's Free Confectionaries Master Budget Performance Report For the month de lune 30 VAR Foru ACTUAL MASTER BUDGET 21.450 20.000 Volume in cases VARIANCE 1.450 Investigate? 7.1% 5 L131.020 1.000.000 DO 5 191020.00 5 5 5 5 5 Sales Revenge Less Variable Expenses Cost of Goods Sold Sales Commons Shipping Lagende Bad debt expense Contribution Margin Less Fed Expenses Salaries Lease on Distcenter Deprecontest/equip Advertising Officerent, phone Operating income 68205 5 40.2135 15,9305 323,312 5 000000005 50.000.00 0,000.00 5 9.000.00 301.000.00 $ 22.330.00 8.695.00 4213.00 7.930.00 27312.00 133 17.4% 10.5% 38.1 9.1 75% 3,000 SOC 5 3,000 5 15.500 5 5 12.000$ 5 7.7505 5 123005 272525 0.0005 17.2005 12.0005 10.0005 11.500 5 2110005 12.2505 1.300 25,762 0.0% -32.5 11. 12.79 Kelsey's Frozen Confectionaries Fexible Budget Performance Report For the month ended ACTUAL REOLE MASTER KEY ID # Names: ID# and 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 I) master budget performance report, and 2) a flexible budget performance report You will work in teams. Turn in ONE project per team. If you or your partner(s) feel you did not share equally in the work, email me for a possible grade adjustment. Otherwise, you will all receive the same grade 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. Do NOT TYPE A NUMBER IN ANY CELLI!! 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, deuble underlines, zero decimal places for dollar amounts, etc..). 5. Use the "Ir" statement function to show the variances as U or F. The "Ir" statement can be found under "Formulas, Logical". Example: =IF(H7--0,"F","U"). This formula means "If cell H720 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 "9" should be shown as "F" since it meets budget expectations. 6. Check your answers using the following check figures: Budgeted CM-S301,000: Budgeted Op Inc = $211,000Variance for Commissions = $8,685; Variance percentage for Commissions 17.4%, Unfavorable 7. Answer the following questions # What is the master budget variance for Shipping exp? _%. U or F What does it mean? b. What is the master budget variance for Lease Exp? S_ %. U or F ? What does it mean? 8. Use management by exception to determine which variances to investigate. HINT: In column 1 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(K745B821)."yes"no") 9. Now check to see if everything ripples through the budget if you change an assumption. Try changing the assumed sales volume to 20,500 and Shipping expense to $2.10 per case. You should have a new total variance for operating income of $21.287.9.8% variance, F. 10. Change the sales volume assumption back to S20,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 (eg.-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 (eg.+H33-333). Once you get the first cell done, copy and paste the formula to the other cells. 6. Head the appropriate column for the Flexible Budget Variance and then use Excel formulas to populate the cells in that column (eg.+F33-H33). Again, copy and paste the formulas to the other cells 7. Format the report appropriately (Dollar signs, underscores, double underscores, etc.) Use the following check figures to check your formulas so far: Shipping expense $44,213 $1293 $42.920 $2,920 $40,000 Lease on Distn center $15,500 S11.500 $17,000 $. $17,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 they go. The interpretation of the volume variance is a little problematic 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 volume variance should be designated as a U or F. 9. Answer the following questions a What is the Flex budget variance for operating income? $_ For U? b. What is the Volume variance for operating income? For U? c. Does the combination of the flex budget variance and volume variance sum to the master budget variance for operating income? Y or N? Should it always sum to the master budget variance? Y or N? 4. How much of the master budget variance for Sales revenue was caused by an unanticipated increase in volume? For U? e How much of the master budget variance for Commissions Exp. was caused by an unanticipated increase in volume? For U? f. How much of the master budget variance for Advertising Expenses was caused by an unanticipated increase in volume? For U? g. How much of the master budget variance for CGS was caused by some factor other than volume? (Hint: this is NOT a manufacturing business). S For U? What could account for this variance? h. How much of the master budget variance for Sales Revenue was caused by some factor other than volume? For U? What could account for this variance? i. How much of the master budget variance for Advertising Exp. was caused by some factor other than volume? S_ For U? What could account for this variance? Auditors always look at the variances between actual and budget or actual and last year. Say you were auditing this client and they gave you the following story (below). Is the story consistent with the overall pattern of variances? Which elements of the story are consistent with the explanations? Which variances are inconsistent, or left unexplained? Be sure to analyze every line item on the budget. (SEE NEXT PAGE.) Client Story: In order to motivate our sales force to increase sales, we decided to increase our commissions and salaries and increase marketing. At the same time, our supplier increased its prices, and we felt we could pass that cost increase on to our customers in the form of price increase. However, with the additional pressure to make sales, coupled with the increased sales price, we had to loosen credit terms on sales. We also had to lease a little more distribution space and acquire another truck to handle the volume increase. Our shipping expense relates to gasoline on deliveries. Luckily, gas prices went down from what we originally expected this year. In the table 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 Flexible Budget Variance and Sales Volume Variance differ with respect to one account (.e., one is consistent and one is inconsistent) then indicate which belongs in which column Consistent Inconsistent Unexplained Sales revenue Cost of Goods Sold Commission Shipping Expense Bad debt expense Salaries Lease of distribution center Depreciation of fleet and equip Advertising Office rent, phone, internet Grading Rubric Criteria Possible Points Lost 20 Direction packet - completed correctly 10 Master Budget Performance Report Correct numbers and percentages Correct U/F Correct Investigation decision Formatting: Underlands Dollar signs/blanks P/extra decimal places removed Flex budget spreadsheet Correct numbers Correct column labels Formatting Underlines Dollar signs Bianks' extra decimal places removed Proper use of formulas: All changes to assumptions nipple through the spreadsheet 10 10 Total