Budget preparation for Millie's exclusive office equipment

Millie manufactures exclusive office equipment and is attempting to prepare budgets for the next couple of months being April and May.The following information is relevant:

Opening stock Budgeted sales Selling price

In units in units per unit

April May £

Chairs 63 290 410 120

Desks 36 120 100 208

Stools 90 230 120 51

Millie requires her closing stock to be 30% of that month’s sales All three products are made using wood, plastic, unskilled and skilled labour.The quantities per unit are as follows:

Wood Plastic Unskilled labour Skilled labour

cu ft cu ft hours hours

Chairs 4 2 3 2

Desks 5 3 5 8

Stools 2 1 2 0

Cost/unit £12/ft £7/ft £7.25/hr £12/hr

Millie’s opening stocks of raw materials are 142 cubic feet of wood and 81 cubic feet of plastic. However she intends to increase this during April so that she always has sufficient raw materials to product 50 units of each item by the end of May.Production overheads amount to £7,174.00 which are recovered based on total direct labour hours for production.

Required:

Prepare the following budgets for April and May to include:

The sales budget

The production budget

The material usage budget

The material purchases budget

The labour utilisation budget

The predicted profit statement

These budgets and profit statement need to be completed in excel and are required in order to answer part 2 of the TCA.

SALES BUDGET:

Particulars	Chairs	Desks	Stools	TOTAL

April:
Selling Price per Unit	£120	£208	£51
Sales Volume in April	290	120	230
Total Sales in April	£34,800	£24,960	£11,730	£71,490

May:
Selling Price per Unit	£120	£208	£51
Sales Volume in May	410	100	120
Total Sales in May	£49,200	£20,800	£6,120	£76,120

TOTAL SALES	£84,000	£45,760	£17,850	£1,47,610


PRODUCTION BUDGET:

Particulars	Chairs	Desks	Stools

April:
Total Sales in April	290	120	230
Add: Closing Stock	87	36	69
Less: Opening Stock	63	36	90
Total Production in April	314	120	209

May:
Total Sales in May	410	100	120
Add: Closing Stock	123	30	36
Less: Opening Stock	87	36	69
Total Production in May	446	94	87

TOTAL PRODUCTION	760	214	296

MATERIAL USAGE BUDGET:
	April		May
Particulars	Wood	Plastic	Wood	Plastic

Chair:
Total Production	314	314	446	446
Usage per unit (cu ft.)	4	2	4	2
Total Usage for Chairs (cu ft.)	1256	628	1784	892


Desks:
Total Production	120	120	94	94
Usage per unit (cu ft.)	5	3	5	3
Total Usage for Desks (cu ft.)	600	360	470	282


Stools:
Total Production	209	209	87	87
Usage per unit (cu ft.)	2	1	2	1
Total Usage for Stools (cu ft.)	418	209	174	87

TOTAL MATERIAL USAGE	2274	1197	2428	1261

MATERIAL PURCHASE BUDGET:

Particulars	Woods	Plastic	TOTAL

April:
Total Usage on April	2274	1197
Add: Closing Stock	550	300
Less: Opening Stock	142	81
Purchase Volume in April (cu ft.)	2682	1416
Material Cost per ft.	£12	£7
Total Purchase in April	£32,184	£9,912	£42,096

May:
Total Sales in May	2428	1261
Add: Closing Stock	550	300
Less: Opening Stock	550	300
Purchase Volume in May (cu ft.)	2428	1261
Material Cost per ft.	£12	£7
Total Purchase in May	£29,136	£8,827	£37,963

TOTAL MATERIAL PURCHASE	£61,320	£18,739	£80,059

LABOUR UTILISATION BUDGET:
	April		May
Particulars	Unskilled Labour Hour	Skilled Labour Hours	Unskilled Labour Hour	Skilled Labour Hour

Chair:
Total Production	314	314	446	446
Labour Hour required per unit	3	2	3	2
Total Labour Hr for Chairs	942	628	1338	892


Desks:
Total Production	120	120	94	94
Labour Hour required per unit	5	8	5	8
Total Labour Hr for Desks	600	960	470	752


Stools:
Total Production	209	209	87	87
Labour Hour required per unit	2	0	2	0
Total Labour Hr for Stools	418	0	174	0

TOTAL LABOUR HOURS	1960	1588	1982	1644
Rate per Laour Hour	£7.25	£12	£7.25	£12

TOTAL LABOUR UTILISATION	£14,210	£19,056	£14,370	£19,728

BUDGETED PROFIT STATEMENT:

Particulars	April	May	TOTAL

Total Sales	£71,490	£76,120	£1,47,610
Cost of Material Consumed:
Woods	-£27,288	-£29,136	-£56,424
Plastic	-£8,379	-£8,827	-£17,206
Labor Cost:
Unskilled Labours	-£14,210	-£14,370	-£28,580
Skilled Labours	-£19,056	-£19,728	-£38,784
Production Overhead	-£3,548	-£3,626	-£7,174

BUDGETED PROFIT	-£991	£434	-£558

SALES BUDGET:

Particulars	Chairs	Desks	Stools	TOTAL

April:
Selling Price per Unit	£144	£208	£51
Sales Volume in April	290	120	230
Total Sales in April	£41,760	£24,960	£11,730	£78,450

May:
Selling Price per Unit	£144	£208	£51
Sales Volume in May	410	100	120
Total Sales in May	£59,040	£20,800	£6,120	£85,960

TOTAL SALES	£1,00,800	£45,760	£17,850	£1,64,410


PRODUCTION BUDGET:

Particulars	Chairs	Desks	Stools

April:
Total Sales in April	290	120	230
Add: Closing Stock	87	36	69
Less: Opening Stock	63	36	90
Total Production in April	314	120	209

May:
Total Sales in May	410	100	120
Add: Closing Stock	123	30	36
Less: Opening Stock	87	36	69
Total Production in May	446	94	87

TOTAL PRODUCTION	760	214	296

MATERIAL USAGE BUDGET:
	April		May
Particulars	Wood	Plastic	Wood	Plastic

Chair:
Total Production	314	314	446	446
Usage per unit (cu ft.)	4	2	4	2
Total Usage for Chairs (cu ft.)	1256	628	1784	892


Desks:
Total Production	120	120	94	94
Usage per unit (cu ft.)	5	3	5	3
Total Usage for Desks (cu ft.)	600	360	470	282


Stools:
Total Production	209	209	87	87
Usage per unit (cu ft.)	2	1	2	1
Total Usage for Stools (cu ft.)	418	209	174	87

TOTAL MATERIAL USAGE	2274	1197	2428	1261

MATERIAL PURCHASE BUDGET:

Particulars	Woods	Plastic	TOTAL

April:
Total Usage on April	2274	1197
Add: Closing Stock	550	300
Less: Opening Stock	142	81
Purchase Volume in April (cu ft.)	2682	1416
Material Cost per ft.	£12	£7
Total Purchase in April	£32,184	£9,912	£42,096

May:
Total Sales in May	2428	1261
Add: Closing Stock	550	300
Less: Opening Stock	550	300
Purchase Volume in May (cu ft.)	2428	1261
Material Cost per ft.	£12	£7
Total Purchase in May	£29,136	£8,827	£37,963

TOTAL MATERIAL PURCHASE	£61,320	£18,739	£80,059

LABOUR UTILISATION BUDGET:
	April		May
Particulars	Unskilled Labour Hour	Skilled Labour Hours	Unskilled Labour Hour	Skilled Labour Hour

Chair:
Total Production	314	314	446	446
Labour Hour required per unit	3	2	3	2
Total Labour Hr for Chairs	942	628	1338	892


Desks:
Total Production	120	120	94	94
Labour Hour required per unit	5	8	5	8
Total Labour Hr for Desks	600	960	470	752


Stools:
Total Production	209	209	87	87
Labour Hour required per unit	2	0	2	0
Total Labour Hr for Stools	418	0	174	0

TOTAL LABOUR HOURS	1960	1588	1982	1644
Rate per Laour Hour	£7.25	£12	£7.25	£12

TOTAL LABOUR UTILISATION	£14,210	£19,056	£14,370	£19,728

BUDGETED PROFIT STATEMENT:

Particulars	April	May	TOTAL

Total Sales	£78,450	£85,960	£1,64,410
Cost of Material Consumed:
Woods	-£27,288	-£29,136	-£56,424
Plastic	-£8,379	-£8,827	-£17,206
Labor Cost:
Unskilled Labours	-£14,210	-£14,370	-£28,580
Skilled Labours	-£19,056	-£19,728	-£38,784
Production Overhead	-£3,548	-£3,626	-£7,174

BUDGETED PROFIT	£5,969	£10,274	£16,243

SALES BUDGET:

Particulars	Chairs	Desks	Stools	TOTAL

April:
Selling Price per Unit	£120	£208	£51
Sales Volume in April	290	120	230
Total Sales in April	£34,800	£24,960	£11,730	£71,490

May:
Selling Price per Unit	£120	£208	£51
Sales Volume in May	410	100	150
Total Sales in May	£49,200	£20,800	£7,650	£77,650

TOTAL SALES	£84,000	£45,760	£19,380	£1,49,140


PRODUCTION BUDGET:

Particulars	Chairs	Desks	Stools

April:
Total Sales in April	290	120	230
Add: Closing Stock	87	36	69
Less: Opening Stock	63	36	90
Total Production in April	314	120	209

May:
Total Sales in May	410	100	150
Add: Closing Stock	123	30	45
Less: Opening Stock	87	36	69
Total Production in May	446	94	126

TOTAL PRODUCTION	760	214	335

MATERIAL USAGE BUDGET:
	April		May
Particulars	Wood	Plastic	Wood	Plastic

Chair:
Total Production	314	314	446	446
Usage per unit (cu ft.)	4	2	4	2
Total Usage for Chairs (cu ft.)	1256	628	1784	892


Desks:
Total Production	120	120	94	94
Usage per unit (cu ft.)	5	3	5	3
Total Usage for Desks (cu ft.)	600	360	470	282


Stools:
Total Production	209	209	126	126
Usage per unit (cu ft.)	2	1	2	1
Total Usage for Stools (cu ft.)	418	209	252	126

TOTAL MATERIAL USAGE	2274	1197	2506	1300
			78	39
MATERIAL PURCHASE BUDGET:			4780	2497

Particulars	Woods	Plastic	TOTAL

April:
Total Usage on April	2274	1197
Add: Closing Stock	550	300
Less: Opening Stock	142	81
Purchase Volume in April (cu ft.)	2682	1416
Material Cost per ft.	£12	£7
Total Purchase in April	£32,184	£9,912	£42,096

May:
Total Sales in May	2506	1300
Add: Closing Stock	550	300
Less: Opening Stock	550	300
Purchase Volume in May (cu ft.)	2506	1300
Material Cost per ft.	£12	£7
Total Purchase in May	£30,072	£9,100	£39,172

TOTAL MATERIAL PURCHASE	£62,256	£19,012	£81,268
	£936	£273
LABOUR UTILISATION BUDGET:
	April		May
Particulars	Unskilled Labour Hour	Skilled Labour Hours	Unskilled Labour Hour	Skilled Labour Hour

Chair:
Total Production	314	314	446	446
Labour Hour required per unit	3	2	3	2
Total Labour Hr for Chairs	942	628	1338	892


Desks:
Total Production	120	120	94	94
Labour Hour required per unit	5	8	5	8
Total Labour Hr for Desks	600	960	470	752


Stools:
Total Production	209	209	126	126
Labour Hour required per unit	2	0	2	0
Total Labour Hr for Stools	418	0	252	0

TOTAL LABOUR HOURS	1960	1588	2060	1644
Rate per Laour Hour	£7.25	£12	£7.25	£12

TOTAL LABOUR UTILISATION	£14,210	£19,056	£14,935	£19,728

BUDGETED PROFIT STATEMENT:

Particulars	April	May	TOTAL

Total Sales	£71,490	£77,650	£1,49,140
Cost of Material Consumed:
Woods	-£27,288	-£30,072	-£57,360
Plastic	-£8,379	-£9,100	-£17,479
Labor Cost:
Unskilled Labours	-£14,210	-£14,935	-£29,145
Skilled Labours	-£19,056	-£19,728	-£38,784
Production Overhead	-£3,510	-£3,664	-£7,174

BUDGETED PROFIT	-£953	£151	-£802

SALES BUDGET:

Particulars	Chairs	Desks	Stools	TOTAL

April:
Selling Price per Unit	£120	£208	£51
Sales Volume in April	290	120	230
Total Sales in April	£34,800	£24,960	£11,730	£71,490

May:
Selling Price per Unit	£120	£208	£51
Sales Volume in May	410	100	120
Total Sales in May	£49,200	£20,800	£6,120	£76,120

TOTAL SALES	£84,000	£45,760	£17,850	£1,47,610


PRODUCTION BUDGET:

Particulars	Chairs	Desks	Stools

April:
Total Sales in April	290	120	230
Add: Closing Stock	58	24	46
Less: Opening Stock	63	36	90
Total Production in April	285	108	186

May:
Total Sales in May	410	100	120
Add: Closing Stock	82	20	24
Less: Opening Stock	58	24	46
Total Production in May	434	96	98

TOTAL PRODUCTION	719	204	284

MATERIAL USAGE BUDGET:
	April		May
Particulars	Wood	Plastic	Wood	Plastic

Chair:
Total Production	285	285	434	434
Usage per unit (cu ft.)	4	2	4	2
Total Usage for Chairs (cu ft.)	1140	570	1736	868


Desks:
Total Production	108	108	96	96
Usage per unit (cu ft.)	5	3	5	3
Total Usage for Desks (cu ft.)	540	324	480	288


Stools:
Total Production	186	186	98	98
Usage per unit (cu ft.)	2	1	2	1
Total Usage for Stools (cu ft.)	372	186	196	98

TOTAL MATERIAL USAGE	2052	1080	2412	1254

MATERIAL PURCHASE BUDGET:

Particulars	Woods	Plastic	TOTAL

April:
Total Usage on April	2052	1080
Add: Closing Stock	550	300
Less: Opening Stock	142	81
Purchase Volume in April (cu ft.)	2460	1299
Material Cost per ft.	£12	£7
Total Purchase in April	£29,520	£9,093	£38,613

May:
Total Sales in May	2412	1254
Add: Closing Stock	550	300
Less: Opening Stock	550	300
Purchase Volume in May (cu ft.)	2412	1254
Material Cost per ft.	£12	£7
Total Purchase in May	£28,944	£8,778	£37,722

TOTAL MATERIAL PURCHASE	£58,464	£17,871	£76,335
	£2,856	£868
LABOUR UTILISATION BUDGET:
	April		May
Particulars	Unskilled Labour Hour	Skilled Labour Hours	Unskilled Labour Hour	Skilled Labour Hour

Chair:
Total Production	285	285	434	434
Labour Hour required per unit	3	2	3	2
Total Labour Hr for Chairs	855	570	1302	868


Desks:
Total Production	120	120	96	96
Labour Hour required per unit	5	8	5	8
Total Labour Hr for Desks	600	960	480	768


Stools:
Total Production	186	186	98	98
Labour Hour required per unit	2	0	2	0
Total Labour Hr for Stools	372	0	196	0

TOTAL LABOUR HOURS	1827	1530	1978	1636
Rate per Laour Hour	£7.25	£12	£7.25	£12

TOTAL LABOUR UTILISATION	£13,246	£18,360	£14,341	£19,632

BUDGETED PROFIT STATEMENT:

Particulars	April	May	TOTAL

Total Sales	£71,490	£76,120	£1,47,610
Cost of Material Consumed:
Woods	-£24,624	-£28,944	-£53,568
Plastic	-£7,560	-£8,778	-£16,338
Labor Cost:
Unskilled Labours	-£13,246	-£14,341	-£27,586
Skilled Labours	-£18,360	-£19,632	-£37,992
Production Overhead	-£3,455	-£3,719	-£7,174

BUDGETED PROFIT	£4,245	£706	£4,952

SALES BUDGET:

Particulars	Chairs	Desks	Stools	TOTAL

April:
Selling Price per Unit	£120	£208	£51
Sales Volume in April	290	120	230
Total Sales in April	£34,800	£24,960	£11,730	£71,490

May:
Selling Price per Unit	£120	£208	£51
Sales Volume in May	410	100	120
Total Sales in May	£49,200	£20,800	£6,120	£76,120

TOTAL SALES	£84,000	£45,760	£17,850	£1,47,610


PRODUCTION BUDGET:

Particulars	Chairs	Desks	Stools

April:
Total Sales in April	290	120	230
Add: Closing Stock	87	36	69
Less: Opening Stock	63	36	90
Total Production in April	314	120	209

May:
Total Sales in May	410	100	120
Add: Closing Stock	123	30	36
Less: Opening Stock	87	36	69
Total Production in May	446	94	87

TOTAL PRODUCTION	760	214	296

MATERIAL USAGE BUDGET:
	April		May
Particulars	Wood	Plastic	Wood	Plastic

Chair:
Total Production	314	314	446	446
Usage per unit (cu ft.)	5	2	5	2
Total Usage for Chairs (cu ft.)	1570	628	2230	892


Desks:
Total Production	120	120	94	94
Usage per unit (cu ft.)	5	2	5	2
Total Usage for Desks (cu ft.)	600	240	470	188


Stools:
Total Production	209	209	87	87
Usage per unit (cu ft.)	2	1	2	1
Total Usage for Stools (cu ft.)	418	209	174	87

TOTAL MATERIAL USAGE	2588	1077	2874	1167

MATERIAL PURCHASE BUDGET:

Particulars	Woods	Plastic	TOTAL

April:
Total Usage on April	2588	1077
Add: Closing Stock	550	300
Less: Opening Stock	142	81
Purchase Volume in April (cu ft.)	2996	1296
Material Cost per ft.	£12	£7
Total Purchase in April	£35,952	£9,072	£45,024

May:
Total Sales in May	2874	1167
Add: Closing Stock	550	300
Less: Opening Stock	550	300
Purchase Volume in May (cu ft.)	2874	1167
Material Cost per ft.	£12	£7
Total Purchase in May	£34,488	£8,169	£42,657

TOTAL MATERIAL PURCHASE	£70,440	£17,241	£87,681

LABOUR UTILISATION BUDGET:
	April		May
Particulars	Unskilled Labour Hour	Skilled Labour Hours	Unskilled Labour Hour	Skilled Labour Hour

Chair:
Total Production	314	314	446	446
Labour Hour required per unit	3	2	3	2
Total Labour Hr for Chairs	942	628	1338	892


Desks:
Total Production	120	120	94	94
Labour Hour required per unit	5	8	5	8
Total Labour Hr for Desks	600	960	470	752


Stools:
Total Production	209	209	87	87
Labour Hour required per unit	2	0	2	0
Total Labour Hr for Stools	418	0	174	0

TOTAL LABOUR HOURS	1960	1588	1982	1644
Rate per Laour Hour	£7.25	£12	£7.25	£12

TOTAL LABOUR UTILISATION	£14,210	£19,056	£14,370	£19,728

BUDGETED PROFIT STATEMENT:

Particulars	April	May	TOTAL

Total Sales	£71,490	£76,120	£1,47,610
Cost of Material Consumed:
Woods	-£31,056	-£34,488	-£65,544	£9,120
Plastic	-£7,539	-£8,169	-£15,708	£1,498
Labor Cost:				£81,252
Unskilled Labours	-£14,210	-£14,370	-£28,580
Skilled Labours	-£19,056	-£19,728	-£38,784
Production Overhead	-£3,548	-£3,626	-£7,174

BUDGETED PROFIT	-£3,919	-£4,261	-£8,180

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Budget Preparation For Millie's Exclusive Office Equipment. Retrieved from https://myassignmenthelp.com/free-samples/acc1005-time-constrained-assessment/sufficient-raw-materials.html.

"Budget Preparation For Millie's Exclusive Office Equipment." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/acc1005-time-constrained-assessment/sufficient-raw-materials.html.

My Assignment Help (2020) Budget Preparation For Millie's Exclusive Office Equipment [Online]. Available from: https://myassignmenthelp.com/free-samples/acc1005-time-constrained-assessment/sufficient-raw-materials.html
[Accessed 19 April 2024].

My Assignment Help. 'Budget Preparation For Millie's Exclusive Office Equipment' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/acc1005-time-constrained-assessment/sufficient-raw-materials.html> accessed 19 April 2024.

My Assignment Help. Budget Preparation For Millie's Exclusive Office Equipment [Internet]. My Assignment Help. 2020 [cited 19 April 2024]. Available from: https://myassignmenthelp.com/free-samples/acc1005-time-constrained-assessment/sufficient-raw-materials.html.

Get instant help from 5000+ experts for

Writing Rewriting Editing

Subject/course code

❮ ❯

Pages

250 words

Order description (write/attach)

Attach file

I accept the T&C, agree to receive offers & updates

Have a coupon code?