100%- 50 DO 123- Delta- 11 G - DO Y.. 0 Volume Isales Revenue 20.000 $50.00 perce For the month ended July 30 Percent sold on credit 90 Dercent of sales MASTER BUDGET 20.000 ACTUAL 21.460 VARIANCE difference) 1.460 Woma VAR 7.10 Foru F No 5 1.131,020 $1.000.000 131020 Cost of Goods Sold Sales Commission Shipping Depente Bad Oet Espen 13.10 $ 0.00 5 2.50 per 5 2.00 per case 100 percent of credite 112.880 X.RS Ed Monthly Cup 682 5865 420 16,90 378312 Sleeve Les Variable Expo Cost of Goods Sold Sales Commons Shipping Expen debt een Contribution Margin Cess Foxed Expenses Salaries Leon Destincent Depreconfeet/equip Advertising Oficerent, phone, net Operating income 600,000 S000 40000 20000 290.000 Lease on Distribution center 12 Depreciation on feet & Eque 18 Advertising Ocerent, phone, internet 540,000 5 17 000 $ 12.000 $ 10,000 5 11.000 41000 15.500 12,000 7,750 12.300 227,762 40.000 17000 12000 10000 13000 20 Management by exception 15 Investigatione 3 TE AMICHETE Instructions MasterBudgetPerformance Report - D 2 Calculate the variance. 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 Calculate the "Variance %"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 Determine if the variance is Favorable (F) or Unfavorable (U). The use of a function in this column is OPTIONAL. However, here are the instructions to use the "IF" function: Use the "If' statement function to show the variances as U (unfavorable) or F (favorable). For the IF statement, go to Insert/function/logical/IF Example: -IFH7>-0"F"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 you'll want them to be opposite of one another. Any variance of "0" should be shown as "F" since it meets budget expectations. 5 Format cells appropriately (dollar signs using the accounting or currency format, underlines, double underlines, zero decimal places for dollar amounts, etc.). Go to the "123" in the menu to select the formats. 6 Check your answers using the following check figures: Budgeted Contribution Margin $301.000 Budgeted Operating Income $211.000 Variance for Commissions $8.685 Variance percentage for Commissions 17.40% Unfavorable cence Commissions 17.40% Unfavorable Answer the following questions: % U or F? A. What is the master budget variance for Shipping exp? Input your answers in the cells to the right. What does it mean? Type your answer to this question below. 4.213 $ U or F? B. What is the master budget variance for Lease exp.? Input your answers in the cells to the right. What does it mean? Type your answer to this question below. 100%- 50 DO 123- Delta- 11 G - DO Y.. 0 Volume Isales Revenue 20.000 $50.00 perce For the month ended July 30 Percent sold on credit 90 Dercent of sales MASTER BUDGET 20.000 ACTUAL 21.460 VARIANCE difference) 1.460 Woma VAR 7.10 Foru F No 5 1.131,020 $1.000.000 131020 Cost of Goods Sold Sales Commission Shipping Depente Bad Oet Espen 13.10 $ 0.00 5 2.50 per 5 2.00 per case 100 percent of credite 112.880 X.RS Ed Monthly Cup 682 5865 420 16,90 378312 Sleeve Les Variable Expo Cost of Goods Sold Sales Commons Shipping Expen debt een Contribution Margin Cess Foxed Expenses Salaries Leon Destincent Depreconfeet/equip Advertising Oficerent, phone, net Operating income 600,000 S000 40000 20000 290.000 Lease on Distribution center 12 Depreciation on feet & Eque 18 Advertising Ocerent, phone, internet 540,000 5 17 000 $ 12.000 $ 10,000 5 11.000 41000 15.500 12,000 7,750 12.300 227,762 40.000 17000 12000 10000 13000 20 Management by exception 15 Investigatione 3 TE AMICHETE Instructions MasterBudgetPerformance Report - D 2 Calculate the variance. 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 Calculate the "Variance %"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 Determine if the variance is Favorable (F) or Unfavorable (U). The use of a function in this column is OPTIONAL. However, here are the instructions to use the "IF" function: Use the "If' statement function to show the variances as U (unfavorable) or F (favorable). For the IF statement, go to Insert/function/logical/IF Example: -IFH7>-0"F"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 you'll want them to be opposite of one another. Any variance of "0" should be shown as "F" since it meets budget expectations. 5 Format cells appropriately (dollar signs using the accounting or currency format, underlines, double underlines, zero decimal places for dollar amounts, etc.). Go to the "123" in the menu to select the formats. 6 Check your answers using the following check figures: Budgeted Contribution Margin $301.000 Budgeted Operating Income $211.000 Variance for Commissions $8.685 Variance percentage for Commissions 17.40% Unfavorable cence Commissions 17.40% Unfavorable Answer the following questions: % U or F? A. What is the master budget variance for Shipping exp? Input your answers in the cells to the right. What does it mean? Type your answer to this question below. 4.213 $ U or F? B. What is the master budget variance for Lease exp.? Input your answers in the cells to the right. What does it mean? Type your answer to this question below