B A Part 1) 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 formulate the cells. Do NOT TYPE A NUMBER IN ANY CELL!!! 2) Use a formula to calculate the "variance" in cell H7: (Actual - Budget). Copy and paste for use the fill 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 formula to calculate the "Variance percentage". NOTE: The percentage is the variance as a percent of the Master Budget. Copy and paste (or 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 "It statement can be found under "Formulas, Logical Example: =1F[H7>0,"P","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 they should be opposite of one another. ALSO- The formula you use should mark any variance of "O as an "F" since a zero variance means that budget expectations have been met. After using the function, check each line to make sure it is going in the direction you believe it should go. 6) Check your answers using the following check figures: Budgeted CM=5301,000, Budgeted Op Inc = $211,000, Variance for Commissions = 58,685, Variance percentage for Commissions = 17.4%, Unfavorable I 7) Use management by exception to determine which variances to investigate. HINT: In column 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: SBS21. For example, -1F[ABS(17)>(SBS21). "Yes","no"). After using the function, check each line to see whether your formula worked property. 8) 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! 9) Center the U/F and Yes/No in the middle of the columns for easier readability 10) 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 521,287,0 9.8% variance, F,N Directions Performance Report Analysis, nans 11 Analisis man? A B C 1 2 Budget assumptions: 3 4 Volume 5 Sales Revenue 20,000 cases 50.00 per case $ 90% percent of sales is is 30.00 per case 2.50 per case 2.00 per case 1.00% percent of credit sales $ 6 Percent sold on credit 7 8 Variable Expenses: 9 CGS 10 Sales Commission 11 Shipping Expense 12 Bad Debt Expense 13 14 Fixed Monthly Expenses: 15 Salaries 16 Lease on Distribution center 17 Depreciation on fleet & Equip 18 Advertising 19 Office rent, phone, internet 20 21 Management by exception 22 $ 40,000 $ 17,000 $ 12,000 $ 10,000 $ 11,000 15% Investigation rule A Insert Delete Format Metge a Center - Clear $ - % &- Conditional Formatas Cell Formatting Table Styles Number Style Sort & Find Filter Select Anye Data Alignment ty 5 CL Edong Analyse H M N Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 MASTER BUDGET VARIANCE VAR.X Foru Investigate? Volume (in cases) ACTUAL 21.460 NOTE: The most common mistake in this project lovalves the FR formudas Steps of the direction Remember revenues and expenses in opposite directions, so you formula needs to be adjunted for that fact. Also, be sure any variance of So would be would end up being shows favorable variance Finally, think about which direction and Operating income should Ao the same direction revenue or the same direction $1,131,020 dit sales $ $ $ $ S 682,880 58,685 44213 16930 328,312 Sales Revenue Less Variable Expenses Cost of Goods Sold Sales Commissions Shipping Expense Bad debt expense Contribution Martin Less Fed Expenses: Salaries Leave on Distcenter Deprec. on fleet/eeship Advertisine Officerent, phone, not Operating Income 43,000 15,500 12.000 5 5 5 $ $ 5 7.750 rule 12.100 237702 ur ber Feren fleeting B A Part 1) 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 formulate the cells. Do NOT TYPE A NUMBER IN ANY CELL!!! 2) Use a formula to calculate the "variance" in cell H7: (Actual - Budget). Copy and paste for use the fill 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 formula to calculate the "Variance percentage". NOTE: The percentage is the variance as a percent of the Master Budget. Copy and paste (or 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 "It statement can be found under "Formulas, Logical Example: =1F[H7>0,"P","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 they should be opposite of one another. ALSO- The formula you use should mark any variance of "O as an "F" since a zero variance means that budget expectations have been met. After using the function, check each line to make sure it is going in the direction you believe it should go. 6) Check your answers using the following check figures: Budgeted CM=5301,000, Budgeted Op Inc = $211,000, Variance for Commissions = 58,685, Variance percentage for Commissions = 17.4%, Unfavorable I 7) Use management by exception to determine which variances to investigate. HINT: In column 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: SBS21. For example, -1F[ABS(17)>(SBS21). "Yes","no"). After using the function, check each line to see whether your formula worked property. 8) 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! 9) Center the U/F and Yes/No in the middle of the columns for easier readability 10) 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 521,287,0 9.8% variance, F,N Directions Performance Report Analysis, nans 11 Analisis man? A B C 1 2 Budget assumptions: 3 4 Volume 5 Sales Revenue 20,000 cases 50.00 per case $ 90% percent of sales is is 30.00 per case 2.50 per case 2.00 per case 1.00% percent of credit sales $ 6 Percent sold on credit 7 8 Variable Expenses: 9 CGS 10 Sales Commission 11 Shipping Expense 12 Bad Debt Expense 13 14 Fixed Monthly Expenses: 15 Salaries 16 Lease on Distribution center 17 Depreciation on fleet & Equip 18 Advertising 19 Office rent, phone, internet 20 21 Management by exception 22 $ 40,000 $ 17,000 $ 12,000 $ 10,000 $ 11,000 15% Investigation rule A Insert Delete Format Metge a Center - Clear $ - % &- Conditional Formatas Cell Formatting Table Styles Number Style Sort & Find Filter Select Anye Data Alignment ty 5 CL Edong Analyse H M N Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 MASTER BUDGET VARIANCE VAR.X Foru Investigate? Volume (in cases) ACTUAL 21.460 NOTE: The most common mistake in this project lovalves the FR formudas Steps of the direction Remember revenues and expenses in opposite directions, so you formula needs to be adjunted for that fact. Also, be sure any variance of So would be would end up being shows favorable variance Finally, think about which direction and Operating income should Ao the same direction revenue or the same direction $1,131,020 dit sales $ $ $ $ S 682,880 58,685 44213 16930 328,312 Sales Revenue Less Variable Expenses Cost of Goods Sold Sales Commissions Shipping Expense Bad debt expense Contribution Martin Less Fed Expenses: Salaries Leave on Distcenter Deprec. on fleet/eeship Advertisine Officerent, phone, not Operating Income 43,000 15,500 12.000 5 5 5 $ $ 5 7.750 rule 12.100 237702 ur ber Feren fleeting