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	?

Answer 1

• All working forms part of the answer
• Requirement completed-Complete worksheet:

					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

• Amount calculated as:

	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