IMPORTANT INFORMATION: IGNORE THE FOLLOWING ON FIRST TAB (AS YOU DO NOT HAVE QUESTIONS TO ANSWER) After developing the performance reports, you will answer questions about the variances and determine whether the variances are consistent with management's explanation about operational changes that took place during the period.

Please GIve the Formulas That Were Used AS WELL!!

M 0 1 K 1 M 2 3 1 Flexible Budget Performance Report Project Upload your completed project to Blackboard using "Flex Budget_Last Name_TA" as the file name. Kelsey's Frozen Confectionaries buys and distributes single-serve ice cream treats to convenience stores, ballparks, and amusement parks. In this project, you will create 1) a master budget performance report, and 2) a flexible budget performance report for Kelsey's Frozen Confectionaries. Your performance reports should be developed in such a way that any changes to the original assumptions will correctly ripple through the entire spreadsheet. After developing the performance reports, you will answer questions about the variances and determine whether the variances are consistent with management's explanation about operational changes that took place during the period. 1 2 3 5 6 7 1 2 > + 5 5 7 Part 1) DIRECTIONS for Master Budget Performance Report 1) Use the budget mumptions, along with Excel formula, to populate the Master Budget column. Note: Your formules must work such that it ANY of the budget assumption change the new assumptions ripple through the entire budget. Part of your grade will be based on whether you correctly formulate the cells. DO NOT TYPE A NUMBER IN ANY CELLU 2) Use a formula to calculate the variance" in cell HT: (Actual - Budgetl. Copy and paste for use the handle to drag the formula to the rest of the cells in the column Leave as positive or negative, rather than absolute values 3) Use a formule to calculate the "Varlance percentage. NOTE The percentage is the variance as a percent of the Master Budget. Copy and paste for drag the formula to the rest of the cells in the column 4) Format cells appropriately. Attention to detail makes a report look more professional. For example, percentages shown as , dollar signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places for dollar amounts, etc.). 5) Use the statement function to show the variances as U or F. The statement can be found under "Formulas, Logical". Example! -FH7>"F","U"). This formula means of cell 7>0 or H7-0, then mark as "F: # not greater than or equal to 0, mark as "U". Be careful with revenues and expense variances since they should be opposite of one another. ALSO The formule you use should mark any wariance of "0 an" since o rere variance means that budget expectations have been met. After using the function, check each line to make sure it a going in the direction you believe it should go 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) Use management by exception to determine which variances to investigate. HINT: In column K, use the 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: $A$21. For example, "IF(ABS071158$211."yes", "no. After using the function, check each line to see whether your formule worked properly. 81 Use the conditional Formatting feature to automatically highlight any variance that says "yes to investigate. First, select all of the answers in the investigate ?"column with your cursor. Next select "Conditional formatting from the home tab on the ribbon. From the drop-down choices select "highlight cells rules then "Text that contains.... A dialogue box will open. Type in "yes as the text you want. Leave the default Highlight color, or choose a different color from the options listed. Then click OK. Notice how every cell containing "yes" is now highlighted! 91 Center the U/F and Yes/No in the middle of the columns for easier readability 10) Now check to see 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.No. 11) Change the sales volume assumption back to $20,000 and the shipping expense assumption back to $2.00 before continuing Part 21 DIRECTIONS for Flexible Budee Performance Report 1) Copy and paste all of the line items account names) from the master budget performance report into the Flexible Budget Performance report 21 Copy and paste all of the actual figures to the actual column of the Flexible Budert Performance report 3) Formulate the Master Budget Figures by referencing the appropriate cells in the master budget performance report (ex-7). By doing so, if any assumptions change they will ripple through BOTH performance reports. 4 Build the Florible 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 SI Try using the accounting or currency setting to get doll Number Format on negative numbers. Add underlines and double underlines as needed. Remove any decimals. 6 Head the appropriate column for the volume Valance and then use Excel formulas to populate the cells in that column (ex. +33-33). Once you get the first cell done, copy and paste for drag the formula to the other cells. 7Head the appropriate column for the Flexible Budget Variance and then use Excel formulas to populate the cells in that column tex F33 H33). Again, copy and paste the formulas to the other cells. 8 Use the following check figures to check your formulas so far: Shipping Expense: 544.213 $1,293 $42.920 $2,920 $40,000 + 2 2 5 6 7 3 9 Format cells appropriately. Attention to detail makes a report look more professional. For example, dollar signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places for dallar amounts, etc. 10 To save time, we won't add the U and 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 5 6 7 3 0 1 2 5 5 A D H M N 0 P Budget assumptions: Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 Volume Sales Revenue 20,000 cases 50.00 per case $ MASTER BUDGET Percent sold on credit VARIANCE VAR.X ACTUAL 21.460 Foru Investigate? NOTE: The most common mistake in this project involves the F/U formulas (Step 5 of the directions) Remember, revenues and expenses go in opposite directions, so your formula needs to be adjusted for that fact. Also, be sure any variance of $0 would be would end up being shown as a favorable variance Finally, think about which direction CM and Operating income should got the same direction as revenue, of the same direction as expenses $ 1,131,020 Variable Expenses CGS Sales Commission Shipping Expense Bad Debt Expense $ $ $ 90% peront of sales Volume in cases 30.00 per case Increase Decimal Revenue 2.50 per case Less Variable Expenses: 2.00 per case Cost of Goods Sold 1.00% percent of credit sales Sales Commissions Shipping Expense Bad debt expense 40,000 Contribution Margin 17,000 Less Fred Expenses 12,000 Salaries 10,000 Lease on Distn center 11,000 Deprec on feet/equip Advertising 15% Investigation rule Officerent, phone, net Operating Income $ $ $ $ $ 582.880 58,585 44 213 16,930 328,312 Flued Monthly Ewenses. Salaries Lease on Distribution center Depreciation on fleet & Equip Advertising Officerent, phone, internet $ $ $ $ $ $ $ $ $ $ $ 43,000 15.500 12,000 7,750 12.300 237,762 Management by exception HINTS on EXCEL 1) if you get a series of hashmarks (W) it just means your column width is not wide enough to accomodate the number. Increase column width and/or delete extra decimal places ) Kelsey's Frozen Confectionaries Fexible Budget Performance Report For the month ended June 10 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 ACTUAL FLEXIBLE BUDGET MASTER BUDGET 3. To multiply, use the symbol. To divide, use the / symbol . . 4. Make use of copy/paste commands for the fill handle) 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 M 0 1 K 1 M 2 3 1 Flexible Budget Performance Report Project Upload your completed project to Blackboard using "Flex Budget_Last Name_TA" as the file name. Kelsey's Frozen Confectionaries buys and distributes single-serve ice cream treats to convenience stores, ballparks, and amusement parks. In this project, you will create 1) a master budget performance report, and 2) a flexible budget performance report for Kelsey's Frozen Confectionaries. Your performance reports should be developed in such a way that any changes to the original assumptions will correctly ripple through the entire spreadsheet. After developing the performance reports, you will answer questions about the variances and determine whether the variances are consistent with management's explanation about operational changes that took place during the period. 1 2 3 5 6 7 1 2 > + 5 5 7 Part 1) DIRECTIONS for Master Budget Performance Report 1) Use the budget mumptions, along with Excel formula, to populate the Master Budget column. Note: Your formules must work such that it ANY of the budget assumption change the new assumptions ripple through the entire budget. Part of your grade will be based on whether you correctly formulate the cells. DO NOT TYPE A NUMBER IN ANY CELLU 2) Use a formula to calculate the variance" in cell HT: (Actual - Budgetl. Copy and paste for use the handle to drag the formula to the rest of the cells in the column Leave as positive or negative, rather than absolute values 3) Use a formule to calculate the "Varlance percentage. NOTE The percentage is the variance as a percent of the Master Budget. Copy and paste for drag the formula to the rest of the cells in the column 4) Format cells appropriately. Attention to detail makes a report look more professional. For example, percentages shown as , dollar signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places for dollar amounts, etc.). 5) Use the statement function to show the variances as U or F. The statement can be found under "Formulas, Logical". Example! -FH7>"F","U"). This formula means of cell 7>0 or H7-0, then mark as "F: # not greater than or equal to 0, mark as "U". Be careful with revenues and expense variances since they should be opposite of one another. ALSO The formule you use should mark any wariance of "0 an" since o rere variance means that budget expectations have been met. After using the function, check each line to make sure it a going in the direction you believe it should go 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) Use management by exception to determine which variances to investigate. HINT: In column K, use the 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: $A$21. For example, "IF(ABS071158$211."yes", "no. After using the function, check each line to see whether your formule worked properly. 81 Use the conditional Formatting feature to automatically highlight any variance that says "yes to investigate. First, select all of the answers in the investigate ?"column with your cursor. Next select "Conditional formatting from the home tab on the ribbon. From the drop-down choices select "highlight cells rules then "Text that contains.... A dialogue box will open. Type in "yes as the text you want. Leave the default Highlight color, or choose a different color from the options listed. Then click OK. Notice how every cell containing "yes" is now highlighted! 91 Center the U/F and Yes/No in the middle of the columns for easier readability 10) Now check to see 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.No. 11) Change the sales volume assumption back to $20,000 and the shipping expense assumption back to $2.00 before continuing Part 21 DIRECTIONS for Flexible Budee Performance Report 1) Copy and paste all of the line items account names) from the master budget performance report into the Flexible Budget Performance report 21 Copy and paste all of the actual figures to the actual column of the Flexible Budert Performance report 3) Formulate the Master Budget Figures by referencing the appropriate cells in the master budget performance report (ex-7). By doing so, if any assumptions change they will ripple through BOTH performance reports. 4 Build the Florible 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 SI Try using the accounting or currency setting to get doll Number Format on negative numbers. Add underlines and double underlines as needed. Remove any decimals. 6 Head the appropriate column for the volume Valance and then use Excel formulas to populate the cells in that column (ex. +33-33). Once you get the first cell done, copy and paste for drag the formula to the other cells. 7Head the appropriate column for the Flexible Budget Variance and then use Excel formulas to populate the cells in that column tex F33 H33). Again, copy and paste the formulas to the other cells. 8 Use the following check figures to check your formulas so far: Shipping Expense: 544.213 $1,293 $42.920 $2,920 $40,000 + 2 2 5 6 7 3 9 Format cells appropriately. Attention to detail makes a report look more professional. For example, dollar signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places for dallar amounts, etc. 10 To save time, we won't add the U and 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 5 6 7 3 0 1 2 5 5 A D H M N 0 P Budget assumptions: Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 Volume Sales Revenue 20,000 cases 50.00 per case $ MASTER BUDGET Percent sold on credit VARIANCE VAR.X ACTUAL 21.460 Foru Investigate? NOTE: The most common mistake in this project involves the F/U formulas (Step 5 of the directions) Remember, revenues and expenses go in opposite directions, so your formula needs to be adjusted for that fact. Also, be sure any variance of $0 would be would end up being shown as a favorable variance Finally, think about which direction CM and Operating income should got the same direction as revenue, of the same direction as expenses $ 1,131,020 Variable Expenses CGS Sales Commission Shipping Expense Bad Debt Expense $ $ $ 90% peront of sales Volume in cases 30.00 per case Increase Decimal Revenue 2.50 per case Less Variable Expenses: 2.00 per case Cost of Goods Sold 1.00% percent of credit sales Sales Commissions Shipping Expense Bad debt expense 40,000 Contribution Margin 17,000 Less Fred Expenses 12,000 Salaries 10,000 Lease on Distn center 11,000 Deprec on feet/equip Advertising 15% Investigation rule Officerent, phone, net Operating Income $ $ $ $ $ 582.880 58,585 44 213 16,930 328,312 Flued Monthly Ewenses. Salaries Lease on Distribution center Depreciation on fleet & Equip Advertising Officerent, phone, internet $ $ $ $ $ $ $ $ $ $ $ 43,000 15.500 12,000 7,750 12.300 237,762 Management by exception HINTS on EXCEL 1) if you get a series of hashmarks (W) it just means your column width is not wide enough to accomodate the number. Increase column width and/or delete extra decimal places ) Kelsey's Frozen Confectionaries Fexible Budget Performance Report For the month ended June 10 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 ACTUAL FLEXIBLE BUDGET MASTER BUDGET 3. To multiply, use the symbol. To divide, use the / symbol . . 4. Make use of copy/paste commands for the fill handle) 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