Chapter_10_Applying_Excel.xls
Chapter10: Applying Excel
Data
Revenue $16.50 q
Cost of ingredients $6.25 q
Wages and salaries $10,400
Utilities $800 +$0.20 q
Rent $2,200
Miscellaneous $600 +$0.80 q
Actual results:
Revenue $27,920
Cost of ingredients $11,110
Wages and salaries $10,130
Utilities $1,080
Rent $2,200
Miscellaneous $2,240
Planning budget activity 1,800 meals served
Actual activity 1,700 meals served
Enter a formula into each of the cells marked with a ? below
Review Problem: Variance Analysis Using a Flexible Budget
Construct a flexible budget performance report
Revenue
and
Actual Spending Flexible Activity Planning
Results Variances Budget Variances Budget
Meals served ? ? ?
Revenue ? ? ? ? ?
Expenses:
Cost of ingredients ? ? ? ? ?
Wages and salaries ? ? ? ? ?
Utilities ? ? ? ? ?
Rent ? ? ? ? ?
Miscellaneous ? ? ? ? ?
Total expenses ? ? ? ? ?
Net operating income ? ? ? ? ?
Chapter_10_Applying_Excel.xls
Chapter 10: Applying Excel
Data
Revenue $16.50 q
Cost of ingredients $6.25 q
Wages and salaries $10,400
Utilities $800 +$0.20 q
Rent $2,200
Miscellaneous $600 +$0.80 q
Actual results:
Revenue $27,920
Cost of ingredients $11,110
Wages and salaries $10,130
Utilities $1,080
Rent $2,200
Miscellaneous $2,240
Planning budget activity 1,800 meals served
Actual activity 1,700 meals served
Enter a formula into each of the cells marked with a ? below
Review Problem: Variance Analysis Using a Flexible Budget
Construct a flexible budget performance report
Revenue
and
Actual Spending Flexible Activity Planning
Results Variances Budget Variances Budget
Meals served 1,700 1,700 1,800
Revenue 27,920$ 130$ U 28,050$ 1,650$ U 29,700$
Expenses:
Cost of ingredients 11,110 485 U 10,625 625 F 11,250
Wages and salaries 10,130 270 F 10,400 – 10,400
Utilities 1,080 60 F 1,140 20 F 1,160
Rent 2,200 – 2,200 – 2,200
Miscellaneous 2,240 280 U 1,960 80 F 2,040
Total expenses 26,760 435 U 26,325 725 F 27,050
Net operating income 1,160$ 565$ U 1,725$ 925$ U 2,650$
Chapter_10_Applying_Excel.xls
Chapter 10: Applying Excel
Data
Revenue 16.5 q
Cost of ingredients 6.25 q
Wages and salaries 10400
Utilities 800 +0.2 q
Rent 2200
Miscellaneous 600 +0.8 q
Actual results:
Revenue 27920
Cost of ingredients 11110
Wages and salaries 10130
Utilities 1080
Rent 2200
Miscellaneous 2240
Planning budget activity 1800 meals served
Actual activity 1700 meals served
Revenue
and
Actual Spending Flexible
Results Variances Budget
Meals served =B20 =B20
Revenue =B12 =ABS(B31-E31) =IF(E31>B31,”U”,IF(E31<B31,”F”,”“)) =D4*E30
Expenses:
Cost of ingredients =B13 =ABS(B33-E33) =IF(E33>B33,”F”,IF(E33<B33,”U”,””)) =D5*E30
Wages and salaries =B14 =ABS(B34-E34) =IF(E34>B34,”F”,IF(E34<B34,”U”,””)) =B6
Utilities =B15 =ABS(B35-E35) =IF(E35>B35,”F”,IF(E35<B35,”U”,””)) =B7+D7*E30
Rent =B16 =ABS(B36-E36) =IF(E36>B36,”F”,IF(E36<B36,”U”,”“)) =B8
Miscellaneous =B17 =ABS(B37-E37) =IF(E37>B37,”F”,IF(E37<B37,”U”,”“)) =B9+D9*E30
Total expenses =SUM(B33:B37) =ABS(B38-E38) =IF(E38>B38,”F”,IF(E38<B38,”U”,””)) =SUM(E33:E37)
Net operating income =B31-B38 =ABS(B39-E39) =IF(E39>B39,”U”,IF(E39<B39,”F”,””)) =E31-E38
Construct a flexible budget performance report
Enter a formula into each of the cells marked with a ? below
Review Problem: Variance Analysis Using a Flexible Budget
Page 3
Chapter_10_Applying_Excel.xls
Chapter_10_Applying_Excel.xls
Chapter 10: Applying Excel
Data
Revenue $16.00 q
Cost of ingredients $6.50 q
Wages and salaries $10,000
Utilities $800 +$0.20 q
Rent $2,200
Miscellaneous $600 +$0.80 q
Actual results:
Revenue $27,920
Cost of ingredients $11,110
Wages and salaries $10,130
Utilities $1,080
Rent $2,200
Miscellaneous $2,240
Planning budget activity 1,800 meals served
Actual activity 1,700 meals served
Enter a formula into each of the cells marked with a ? below
Review Problem: Variance Analysis Using a Flexible Budget
Construct a flexible budget performance report
Revenue
and
Actual Spending Flexible Activity Planning
Results Variances Budget Variances Budget
Meals served 1,700 1,700 1,800
Revenue 27,920$ 720$ F 27,200$ 1,600$ U 28,800$
Expenses:
Cost of ingredients 11,110 60 U 11,050 650 F 11,700
Wages and salaries 10,130 130 U 10,000 – 10,000
Utilities 1,080 60 F 1,140 20 F 1,160
Rent 2,200 – 2,200 – 2,200
Miscellaneous 2,240 280 U 1,960 80 F 2,040
Total expenses 26,760 410 U 26,350 750 F 27,100
Net operating income 1,160$ 310$ F 850$ 850$ U 1,700$
Chapter_10_Applying_Excel.xls
Chapter 10: Applying Excel
Data
Revenue $16.50 q
Cost of ingredients $6.25 q
Wages and salaries $10,400
Utilities $800 +$0.20 q
Rent $2,200
Miscellaneous $600 +$0.80 q
Actual results:
Revenue $28,900
Cost of ingredients $11,300
Wages and salaries $10,300
Utilities $1,120
Rent $2,300
Miscellaneous $2,020
Planning budget activity 1,700 meals served
Actual activity 1,800 meals served
Enter a formula into each of the cells marked with a ? below
Review Problem: Variance Analysis Using a Flexible Budget
Construct a flexible budget performance report
Revenue
and
Actual Spending Flexible Activity Planning
Results Variances Budget Variances Budget
Meals served 1,800 1,800 1,700
Revenue 28,900$ 800$ U 29,700$ 1,650$ F 28,050$
Expenses:
Cost of ingredients 11,300 50 U 11,250 625 U 10,625
Wages and salaries 10,300 100 F 10,400 – 10,400
Utilities 1,120 40 F 1,160 20 U 1,140
Rent 2,300 100 U 2,200 – 2,200
Miscellaneous 2,020 20 F 2,040 80 U 1,960
Total expenses 27,040 10 F 27,050 725 U 26,325
Net operating income 1,860$ 790$ U 2,650$ 925$ F 1,725$