Based on the data table provided below, answer the following question Variance analysis 1 Select one of the products of the company 2 Choose an actual amount for sales in Year 1 that is different from the budgeted amount Calculate the sales volume variance for operating income generated by the product 3 Choose an actual amount for one cost item (direct materials costs or direct labour costs) used to produce the product in Year 1 This actual amount should be different from the budgeted amount Calculate the efficiency variance and the price variance a Supporting schedules i Sales budget Month Forecasted unit sales of coffee roasting x Price per unit ($) Total gross sales Sales discounts allowances Total net Sales Forecasted unit sales of coffee beans x Price per unit ($) Total gross sales Sales discounts allowances Total net Sales Jan 50000 5 250000 900 249100 30000 8 240000 600 239400 Feb 50000 5 250000 1000 249000 32000 8 256000 610 255390 Mar 65000 5 325000 1540 323460 31000 8 248000 604 247396 Apr 70000 5 350000 2444 347556 35000 8 280000 650 279350 May 61000 5 305000 1760 303240 40000 8 320000 700 319300 Jun 65000 5 325000 2212 322788 41000 8 328000 750 327250 Jul 80000 5 400000 2500 397500 43000 8 344000 750 343250 Aug 85000 5 425000 3200 421800 35000 8 280000 650 279350 Sep 90000 5 450000 3372 446628 37000 8 296000 670 295330 Oct 91000 5 455000 3604 451396 41000 8 328000 750 327250 No 93000 5 465000 3690 461310 45000 8 360000 780 359220 Dec 95000 5 475000 3700 471300 50000 8 400000 800 399200 Total 895000 4475000 460000 3680000 8155000 ii Cash collection from customer 1355000 1 Coffee roasting Jan Feb Mar Apr May Jun Jul Aug Sep Oct No Dec Cash sales 74730 74700 97038 104266 8 90972 96836 4 119250 126540 133988 4 135418 8 138393 141390 Collection of credit sales 30 of current month credit sales 52311 52290 67926 6 72986 76 63680 4 67785 48 83475 88578 93791 88 94793 16 96875 1 98973 70 of prior month credit sales 122059 122010 158495 4 170302 44 148587 6 158166 12 194775 206682 218847 7 221184 226041 9 Total collection of credit sales 52311 174349 189936 6 231482 16 233982 84 216373 08 241641 12 283353 300473 88 313640 9 318059 1 325014 9 Total cash collections 127041 249049 286974 6 335748 96 324954 84 313209 48 360891 12 409893 434462 28 449059 7 456452 1 466404 9 4214141 2 Coffee bean Jan Feb Mar Apr May Jun Jul Aug Sep Oct No Dec Cash sales 71820 76617 74218 8 83805 95790 98175 102975 83805 88599 98175 107766 119760 Collection of credit sales 30 of current month credit sales 50274 53631 9 51953 16 58663 5 67053 68722 5 72082 5 58663 5 62019 3 68722 5 75436 2 83832 70 of prior month credit sales 117306 125141 1 121224 04 136881 5 156457 160352 5 168192 5 136881 5 144711 7 160352 5 176017 8 Total collection of credit sales 50274 170937 9 177094 26 179887 54 203934 5 225179 5 232435 226856 198900 8 213434 2 235788 7 259849 8 Total cash collecions 122094 247554 9 251313 06 263692 54 299724 5 323354 5 335410 310661 287499 8 311609 2 343554 7 379609 8 3476078 7690219 iii Purchases and cost of goods sold budget 1 Coffee roasting Average COGS percentage for Coffee roasting is 40 of sales, and keep a target inventory of at least 50 of next month's COGS Quarter Ended March 31 Quarter Ended June 30 Quarter Ended September 30 Quarter Ended December 31 Cost of good sold ( 40 x sales) 330000 392000 510000 558000 Plus Desired ending merchandise inventory 196000 255000 279000 290000 1020000 Total merchandise inventory required 526000 647000 789000 848000 Less Beginning merchandise inventory 95000 105000 200000 250000 Budgeted purchases 431000 542000 589000 598000 2160000 2 Coffee bean Average COGS percentage for Coffee bean is 35 of sales, and keep a target inventory of at least 50 of next month's COGS Quarter Ended March 31 Quarter Ended June 30 Quarter Ended September 30 Quarter Ended December 31 Cost of good sold ( 35 x sales) 260400 324800 322000 380800 Plus Desired ending merchandise inventory 162400 161000 190400 200000 713800 Total merchandise inventory required 422800 485800 512400 580800 Less Beginning merchandise inventory 80000 85000 92000 110000 Budgeted purchases 342800 400800 420400 470800 1634800 3794800 iv Cash disbursements from purchases Coffee roasting Coffee bean Quarter Ended March 31 Quarter Ended June 30 Quarter Ended September 30 Quarter Ended December 31 Quarter Ended March 31 Quarter Ended June 30 Quarter Ended September 30 Quarter Ended December 31 Budgeted Purchases 431000 542000 589000 598000 342800 400800 420400 470800 Budgeted Cash Disbursements ( 50 budgeted purchase) 215500 271000 294500 299000 171400 200400 210200 235400 1897400 1897400 v Operating expenses budget Variable operating expenses Commission expense, 15 of sales 1223250 Miscellaneous expenses, 5 of sales 407750 Total variable operating expenses 1631000 Fixed operating expenses Salary expense, fixed amount 611625 Rent expense, fixed amount 400000 Depreciation expense, fixed amount 150000 Insurance expense, fixed amount 48930 Total fixed operating expenses 1210555 Total operating expenses 2841555 vii Cash disbursements from operating expenses Variable operating expenses Commission expense, 15 of sales 1223250 Miscellaneous expenses, 5 of sales 407750 Total variable operating expenses 1631000 Fixed operating expenses Salary expense, fixed amount 611625 Rent expense, fixed amount 400000 Insurance expense, fixed amount 48930 Total fixed operating expenses 1060555 Total cash disbursements 2691555 b Operating budget (budgeted income statement) Sales 8155000 Cost of goods sold 2854250 Gross Profit 5300750 Operating Expenses 2841555 Operating Income 2459195 Less Interest Expenses 40000 Less Provision Income Tax 483839 Net Income 1935356 c Financial budget i Capital budget ii Cash budget Beginning cash balance 100000 Cash receipts and disbursements Collections from customers 7690219 Payments for purchases 1897400 Payments for operating expenses 2691555 Payments for capital investment 500000 Net cash receipts and disbursements 2601264 Total cash available 2701264 Minimum cash balance 1000000 Excess (deficiency) of cash 1701264 Financing Borrowing (at beginning of year) 700000 Repayment (at end of year) 200000 Interest payment (only in months where repayment) 40000 Total cash increase (decrease) from financing 540000 Ending cash balance 2161264 iii Budgeted balance sheet Non current assets 750000 Current assets Inventories 1733800 Trade receivables 8155000 Cash 2161264 12050064 Total assets 12800064 Equity Owner's equity 8483469 Retained profit 1935356 10418825 Current liabilities Trade payable 1897400 Taxation 483839 2381239 Total equity and liabilities 12800064

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 26, 2024

Based on the data table provided below, answer the following question: Variance analysis 1. Select one of the products of the company. 2. Choose an

Based on the data table provided below, answer the following question:

Variance analysis 1. Select one of the products of the company. 2. Choose an actual amount for sales in Year 1 that is different from the budgeted amount. Calculate the sales-volume variance for operating income generated by the product. 3. Choose an actual amount for one cost item (direct materials costs or direct labour costs) used to produce the product in Year 1. This actual amount should be different from the budgeted amount. Calculate the efficiency variance and the price variance.

a. Supporting schedules
i/ Sales budget
Month	Forecasted unit sales of coffee roasting	x Price per unit ($)	Total gross sales	- Sales discounts & allowances	= Total net Sales	Forecasted unit sales of coffee beans	x Price per unit ($)	Total gross sales	- Sales discounts & allowances	= Total net Sales
Jan	50000	5	250000	900	249100	30000	8	240000	600	239400
Feb	50000	5	250000	1000	249000	32000	8	256000	610	255390
Mar	65000	5	325000	1540	323460	31000	8	248000	604	247396
Apr	70000	5	350000	2444	347556	35000	8	280000	650	279350
May	61000	5	305000	1760	303240	40000	8	320000	700	319300
Jun	65000	5	325000	2212	322788	41000	8	328000	750	327250
Jul	80000	5	400000	2500	397500	43000	8	344000	750	343250
Aug	85000	5	425000	3200	421800	35000	8	280000	650	279350
Sep	90000	5	450000	3372	446628	37000	8	296000	670	295330
Oct	91000	5	455000	3604	451396	41000	8	328000	750	327250
No	93000	5	465000	3690	461310	45000	8	360000	780	359220
Dec	95000	5	475000	3700	471300	50000	8	400000	800	399200
Total	895000		4475000			460000		3680000			8155000
ii/ Cash collection from customer											1355000

1/ Coffee roasting
	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	No	Dec
Cash sales	74730	74700	97038	104266.8	90972	96836.4	119250	126540	133988.4	135418.8	138393	141390
Collection of credit sales:
30% of current month credit sales	52311	52290	67926.6	72986.76	63680.4	67785.48	83475	88578	93791.88	94793.16	96875.1	98973
70% of prior month credit sales	-	122059	122010	158495.4	170302.44	148587.6	158166.12	194775	206682	218847.7	221184	226041.9
Total collection of credit sales	52311	174349	189936.6	231482.16	233982.84	216373.08	241641.12	283353	300473.88	313640.9	318059.1	325014.9
Total cash collections	127041	249049	286974.6	335748.96	324954.84	313209.48	360891.12	409893	434462.28	449059.7	456452.1	466404.9	4214141

2/ Coffee bean
	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	No	Dec
Cash sales	71820	76617	74218.8	83805	95790	98175	102975	83805	88599	98175	107766	119760
Collection of credit sales:
30% of current month credit sales	50274	53631.9	51953.16	58663.5	67053	68722.5	72082.5	58663.5	62019.3	68722.5	75436.2	83832
70% of prior month credit sales	-	117306	125141.1	121224.04	136881.5	156457	160352.5	168192.5	136881.5	144711.7	160352.5	176017.8
Total collection of credit sales	50274	170937.9	177094.26	179887.54	203934.5	225179.5	232435	226856	198900.8	213434.2	235788.7	259849.8
Total cash collecions	122094	247554.9	251313.06	263692.54	299724.5	323354.5	335410	310661	287499.8	311609.2	343554.7	379609.8	3476078	7690219


iii/ Purchases and cost-of-goods-sold budget
1/ Coffee roasting
Average COGS percentage for Coffee roasting is 40% of sales, and keep a target inventory of at least 50% of next month's COGS
	Quarter Ended March 31	Quarter Ended June 30	Quarter Ended September 30	Quarter Ended December 31
Cost of good sold (=40% x sales)	330000	392000	510000	558000
Plus: Desired ending merchandise inventory	196000	255000	279000	290000	1020000
Total merchandise inventory required	526000	647000	789000	848000
Less: Beginning merchandise inventory	95000	105000	200000	250000
Budgeted purchases	431000	542000	589000	598000	2160000
2/ Coffee bean
Average COGS percentage for Coffee bean is 35% of sales, and keep a target inventory of at least 50% of next month's COGS
	Quarter Ended March 31	Quarter Ended June 30	Quarter Ended September 30	Quarter Ended December 31
Cost of good sold (=35% x sales)	260400	324800	322000	380800
Plus: Desired ending merchandise inventory	162400	161000	190400	200000	713800
Total merchandise inventory required	422800	485800	512400	580800
Less: Beginning merchandise inventory	80000	85000	92000	110000
Budgeted purchases	342800	400800	420400	470800	1634800
					3794800
iv/Cash disbursements from purchases
	Coffee roasting	Coffee bean
Quarter Ended March 31	Quarter Ended June 30	Quarter Ended September 30	Quarter Ended December 31	Quarter Ended March 31	Quarter Ended June 30	Quarter Ended September 30	Quarter Ended December 31
Budgeted Purchases	431000	542000	589000	598000	342800	400800	420400	470800
Budgeted Cash Disbursements (=50% budgeted purchase)	215500	271000	294500	299000	171400	200400	210200	235400	1897400	1897400

v/ Operating expenses budget

Variable operating expenses:
Commission expense, 15% of sales	1223250
Miscellaneous expenses, 5% of sales	407750
Total variable operating expenses:	1631000
Fixed operating expenses:
Salary expense, fixed amount	611625
Rent expense, fixed amount	400000
Depreciation expense, fixed amount	150000
Insurance expense, fixed amount	48930
Total fixed operating expenses	1210555
Total operating expenses	2841555

vii. Cash disbursements from operating expenses

Variable operating expenses:
Commission expense, 15% of sales	1223250
Miscellaneous expenses, 5% of sales	407750
Total variable operating expenses:	1631000
Fixed operating expenses:
Salary expense, fixed amount	611625
Rent expense, fixed amount	400000
Insurance expense, fixed amount	48930
Total fixed operating expenses	1060555
Total cash disbursements	2691555

b. Operating budget (budgeted income statement)
Sales	8155000
Cost-of-goods-sold	2854250
Gross Profit	5300750
Operating Expenses	2841555
Operating Income	2459195
Less: Interest Expenses	40000
Less: Provision Income Tax	483839
Net Income	1935356

c. Financial budget
i/ Capital budget














ii/ Cash budget
Beginning cash balance	100000
Cash receipts and disbursements
Collections from customers	7690219
-Payments for purchases	1897400
-Payments for operating expenses	2691555
-Payments for capital investment	500000
Net cash receipts and disbursements	2601264
Total cash available	2701264
Minimum cash balance	1000000
Excess (deficiency) of cash	1701264
Financing
Borrowing (at beginning of year)	700000
Repayment (at end of year)	200000
Interest payment (only in months where repayment)	40000
Total cash increase (decrease) from financing	540000
Ending cash balance	2161264

iii/ Budgeted balance sheet
Non-current assets	750000
Current assets
Inventories	1733800
Trade receivables	8155000
Cash	2161264
	12050064
Total assets	12800064
Equity
Owner's equity	8483469
Retained profit	1935356
	10418825
Current liabilities
Trade payable	1897400
Taxation	483839
	2381239
Total equity and liabilities	12800064