Review Problem: Variance Analysis Using a Flexible Budget Data Fixed Cost Revenu
ID: 2405932 • Letter: R
Question
Review Problem: Variance Analysis Using a Flexible Budget Data Fixed Cost Revenue & Cost Per Door Revenue & Cost Per Window Revenue $864.00 $562.00 Cost of inventory $568.00 $218.75 Wages and salaries $27,555 $75.00 $63.00 Utilities $2,875 $2.05 $1.30 Rent $6,500 Insurance $3,575 $1.50 $3.65 Miscellaneous $1,650 $45.00 $37.00 Actual results: Revenue $288,499 Cost of inventory $152,045 Wages and salaries $65,198 Utilities $2,077 Rent $6,500 Insurance $4,500 Miscellaneous $17,328 Doors Sold Windows Sold Actual unit activity 152 280 Planning budget unit activity 150 301 Enter a formula into each of the cells marked with a ? below Must enter an IF Statement for the U/F selection; can be different between revenue and expenses but same IF statement must be used for entire column of expenses. Construct a flexible budget performance report Revenue and Planning Activity Flexible Spending Actual Budget Variances U/F Budget Variances U/F Results Doors Sold ? ? ? Windows Sold ? ? ? Revenue ? ? U/F ? ? U/F ? Expenses: Cost of inventory ? ? U/F ? ? U/F ? Wages and salaries ? ? U/F ? ? U/F ? Utilities ? ? U/F ? ? U/F ? Rent ? ? U/F ? ? U/F ? Insurance ? ? U/F ? ? U/F ? Miscellaneous ? ? U/F ? ? U/F ? Total expenses ? ? U/F ? ? U/F ? Net operating income ? ? U/F ? ? U/F ? Review Problem: Variance Analysis Using a Flexible Budget Data Fixed Cost Revenue & Cost Per Door Revenue & Cost Per Window Revenue $864.00 $562.00 Cost of inventory $568.00 $218.75 Wages and salaries $27,555 $75.00 $63.00 Utilities $2,875 $2.05 $1.30 Rent $6,500 Insurance $3,575 $1.50 $3.65 Miscellaneous $1,650 $45.00 $37.00 Actual results: Revenue $288,499 Cost of inventory $152,045 Wages and salaries $65,198 Utilities $2,077 Rent $6,500 Insurance $4,500 Miscellaneous $17,328 Doors Sold Windows Sold Actual unit activity 152 280 Planning budget unit activity 150 301 Enter a formula into each of the cells marked with a ? below Must enter an IF Statement for the U/F selection; can be different between revenue and expenses but same IF statement must be used for entire column of expenses. Construct a flexible budget performance report Revenue and Planning Activity Flexible Spending Actual Budget Variances U/F Budget Variances U/F Results Doors Sold ? ? ? Windows Sold ? ? ? Revenue ? ? U/F ? ? U/F ? Expenses: Cost of inventory ? ? U/F ? ? U/F ? Wages and salaries ? ? U/F ? ? U/F ? Utilities ? ? U/F ? ? U/F ? Rent ? ? U/F ? ? U/F ? Insurance ? ? U/F ? ? U/F ? Miscellaneous ? ? U/F ? ? U/F ? Total expenses ? ? U/F ? ? U/F ? Net operating income ? ? U/F ? ? U/F ?Explanation / Answer
Answers
Revenue
and
Planning
Activity
Flexible
Spending
Actual
Budget
Variances
U/F
Budget
Variances
U/F
Results
Doors Sold
$ 150.00
$ 2.00
F
$ 152.00
$ -
Neither U nor F (can be termed as 'F')
$ 152.00
Windows Sold
$ 301.00
$ 21.00
U
$ 280.00
$ -
Neither U nor F (can be termed as 'F')
$ 280.00
Revenue
$ 298,762.00
$ 10,074.00
U
$ 288,688.00
$ 189.00
U
$ 288,499.00
Expenses:
Cost of inventory
$ 151,043.75
$ 3,457.75
U
$ 147,586.00
$ 4,459.00
U
$ 152,045.00
Wages and salaries
$ 57,768.00
$ 1,173.00
U
$ 56,595.00
$ 8,603.00
U
$ 65,198.00
Utilities
$ 3,573.80
$ 23.20
U
$ 3,550.60
$ 1,473.60
F
$ 2,077.00
Rent
$ 6,500.00
$ -
Neither U nor F (can be termed as 'F')
$ 6,500.00
$ -
Neither U nor F (can be termed as 'F')
$ 6,500.00
Insurance
$ 4,898.65
$ 73.65
U
$ 4,825.00
$ 325.00
F
$ 4,500.00
Miscellaneous
$ 19,537.00
$ 687.00
U
$ 18,850.00
$ 1,522.00
F
$ 17,328.00
Total expenses
$ 243,321.20
$ 5,414.60
U
$ 237,906.60
$ 9,741.40
U
$ 247,648.00
Net operating income
$ 55,440.80
$ 4,659.40
U
$ 50,781.40
$ 9,930.40
U
$ 40,851.00
Planning
Flexible
Actual
Budget
Budget
Results
Doors Sold
150
152
152
Windows Sold
301
280
280
Revenue
=+(150*864)+(301*562)
=+(152*864)+(280*562)
288499
Expenses:
Cost of inventory
=+(150*568)+(301*218.75)
=+(152*568)+(280*218.75)
152045
Wages and salaries
=+(150*75)+(301*63)+27555
=+(152*75)+(280*63)+27555
65198
Utilities
=+(150*2.05)+(301*1.3)+2875
=+(152*2.05)+(280*1.3)+2875
2077
Rent
6500
6500
6500
Insurance
=+(150*1.5)+(301*3.65)+3575
=+(152*1.5)+(280*3.65)+3575
4500
Miscellaneous
=+(150*45)+(301*37)+1650
=+(152*45)+(280*37)+1650
17328
Total expenses
Total expenses
Total expenses
Total expenses
Net operating income
Net operating income
Net operating income
Net operating income
Revenue
and
Planning
Activity
Flexible
Spending
Actual
Budget
Variances
U/F
Budget
Variances
U/F
Results
Doors Sold
$ 150.00
$ 2.00
F
$ 152.00
$ -
Neither U nor F (can be termed as 'F')
$ 152.00
Windows Sold
$ 301.00
$ 21.00
U
$ 280.00
$ -
Neither U nor F (can be termed as 'F')
$ 280.00
Revenue
$ 298,762.00
$ 10,074.00
U
$ 288,688.00
$ 189.00
U
$ 288,499.00
Expenses:
Cost of inventory
$ 151,043.75
$ 3,457.75
U
$ 147,586.00
$ 4,459.00
U
$ 152,045.00
Wages and salaries
$ 57,768.00
$ 1,173.00
U
$ 56,595.00
$ 8,603.00
U
$ 65,198.00
Utilities
$ 3,573.80
$ 23.20
U
$ 3,550.60
$ 1,473.60
F
$ 2,077.00
Rent
$ 6,500.00
$ -
Neither U nor F (can be termed as 'F')
$ 6,500.00
$ -
Neither U nor F (can be termed as 'F')
$ 6,500.00
Insurance
$ 4,898.65
$ 73.65
U
$ 4,825.00
$ 325.00
F
$ 4,500.00
Miscellaneous
$ 19,537.00
$ 687.00
U
$ 18,850.00
$ 1,522.00
F
$ 17,328.00
Total expenses
$ 243,321.20
$ 5,414.60
U
$ 237,906.60
$ 9,741.40
U
$ 247,648.00
Net operating income
$ 55,440.80
$ 4,659.40
U
$ 50,781.40
$ 9,930.40
U
$ 40,851.00
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.