Question

I need a help with the solution for the Master Budget Performance Report and Flexible Budget Performance Report in the tables given, based on the Budget assumptions given in the same attachment.

I need help with the formulas for excel.

Thank You!

Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30 20,000 cases 50.00 per case 4 Volume S Sales Revenue MASTER 6 Percent sold on credit 90% percent of sales ACTUAL BUDGET VARIANCE VAR.% Volume (incases) 21.460 30.00 per case 9 CGS 10 Sales Commission 11 Shipping Expense 12 Bad Debt Expense Sales Revenue Less Variable Expenses: 1,131,020 2.50 percase 2.00 per case 100% percent of credit sales 雾682.880 $58,685 $44,213 $16,930 Contribution Margin%328,312 Cost of Goods Sold $ Sales Commissions ShippingExpense Bad debtexpense 14 15 Salaries 16 Lease on Distribution center$ 17 Depreciation on fleet & Equip 40,000 17,000 12,000 10,000 Less Fixed Expenses: $43,000 Lease on Distn center $ 15,500 Depreo, on fleetlequip12,000 $7,750 Office rent, phone, net$12,300 237.762 Salaries 19 Office rent, phone, internet 20 21 Management by exoeption $ Advertising 15% Investigation rule Operating Income 23 24 25 27 28 30 31 32 34 35 36 37 Kelsey's Frozen Confectionaries Fexible Budget Performance Report For the month ended June 30 MASTER BUDGET FLEXIBLE BUDGET ACTUAL 39 40 41 43 46 47 48 49 52 53 54 56 14 ▶ 뇌 Performance Report

Answer 1

1.

Kelsey's Frozen Confectionaries Master Budget Performance Report For the month ended June 30
	Actual	Master Budget	Variance	Variance %	F or U
Volume ( in Cases )	21,460	20,000
Sales Revenue	$ 1,131,020	$ 1,000,000	$ 131,020	13.10 %	F
Less: Variable Expenses
Cost of Goods Sold	682,880	600,000	82,880	13.81 %	U
Sales Commissions	58,685	50,000	8,685	17.37 %	U
Shipping Expense	44,213	40,000	4,213	10.53 %	U
Bad Debt Expense	16,930	18,000	1,070	5.94 %	F
Contribution Margin	328,312	292,000	36,312	12.44 %	F
Less: Fixed Expenses
Salaries	43,000	40,000	3,000	7.5 %	U
Lease on Distribution Center	15,500	17,000	1,500	8.82 %	F
Depreciation on Fleet & Equipment	12,000	12,000	0	NA	None
Advertising	7,750	10,000	2,250	22.5 %	F
Office Rent, Phone and Internet	12,300	11,000	1,300	11.82 %	U
Operating Income	$ 237,762	$ 202,000	$ 35,762	17.70 %	U

Budgeted sales revenue = 20,000 x $ 50 = $ 1,000,000

Variance = $ ( 1, 131,020 - 1,000,000 ) = $ 131,020 F

Variance % = $ 131,020 / $ 1,000,000 = 13.10 % F

Budgeted cost of goods sold = 20,000 x $ 30 = $ 600,000.

Variance = $ ( 682,880 - 600,000 ) = $ 82,880 U

Variance % = $ 82,880 / $ 600,000 * 100 = 13.8 % U

For revenues, if actual results exceed budgeted values, the variance is favorable, and vice versa.

For expenses, if actual results exceed budgeted values, the variance is unfavorable, and vice versa.

2.

Kelsey's Frozen Confectionaries Flexible Budget Performance Report For the month ended June 30
	Actual	Variances	Flexible Budget	Variances	Master Budget
Volume (in Cases )	21,460		21,460		20,000
Sales Revenue	1,131,020	58,020 F	1,073,000	73,000 F	1,000,000
Less: Variable Expenses
Cost of Goods Sold	682,880	39,080 U	643,800	43,800 U	600,000
Sales Commission	58,685	5,035 U	53,650	3,650 U	50,000
Shipping Expense	44,213	1,293 U	42,920	2,920 U	40,000
Bad Debt Expense	16,930	2,384 F	19,314	1,314 U	18,000
Contribution Margin	328,312	14,996 F	313,316	21,316 F	292,000
Less: Fixed Expenses
Salaries	43,000	3,000 U	40,000	None	40,000
Lease of Distribution Center	15,500	1,500 F	17,000	None	17,000
Depreciation of Fleet & Equipment	12,000	None	12,000	None	12,000
Advertising	7,750	2,250 F	10,000	None	10,000
Office Rent, Phone, Net	12,300	1,300 U	11,000	None	11,000
Operating Income	237,762	14,446 F	223,316	21,316 F	$ 202,000

Sales Revenue:

As per flexible budget, budgeted sales revenue = 21,460 x $ 50 = $ 1,073,000.