Question

ACCOUNTING: PREPARE THE BUDGET PROCESS USING EXCEL

a) Prepare a sales budget in units and dollars by quarter and for the year.

b) Prepare a production budget in units by quarter and for the year.

c) Prepare a materials purchases budget in feet and dollars by quarter and for the year.

d) Prepare a direct labor budget in hours and dollars by quarter and for the year.

e) Prepare an overhead budget by quarter and for the year. Show depreciation separately from other fixed overhead.

f) Prepare a selling and administrative expense budget by quarter and for the year. Show depreciation separately from other fixed selling and administrative costs.

EXCEL- must be done in Excel and should exhibit appropriate use of the software.   All of the required budgets should appear on a single spreadsheet labeled “Operating Budgets.” All parts other than per unit amounts should be in whole numbers--ie. no ".00"   Either do not use "$" or use them sparingly and only where appropriate.

DATA:

BUDGET PROJECT DATA

Crossley is the manufacturing subsidiary of a company that is a distributor of high end office furniture. Crossley was formed two years ago when the company decided to begin making some of the furniture it sells. Crossley currently makes a line of desks that has been quite successful since its introduction, and there are plans to introduce new products as the company grows. Crossley uses variable standard costing for budgeting and performance reporting. Standard cost per desk is as follows:

                                                                                                         Cost/Unit

                        Direct materials          10 ft.   @ $5.00/ft.            $ 50.00

                        Direct labor                   6 hrs. @ $9.00/hr. 54.00

                        Variable overhead        6 hrs. @ $6.00/hr. 36.00

                                    Total budgeted cost/unit $140.00

Annual fixed overhead totals $2,521,000, which includes $425,000 of depreciation budgeted at $90,000 per quarter for the first two quarters and $122,500 per quarter for the last two quarters to reflect depreciation on new production equipment that will be acquired at the start of the third quarter. The remainder of the fixed overhead is incurred uniformly throughout the year.

Sales of this product have been increasing at an average rate of 20% per year; 2017 sales totaled 170,000 units, with 20% of sales occurring in the first quarter, 30% in the second quarter, 35% in the third quarter and 15% in the fourth quarter. Strauss expects that division sales will continue to increase at the same rate over the next 3 years and will follow the same quarterly pattern. Crossley prices this product at 50% over variable product cost.

The manufacturing division tries to maintain an inventory of finished goods equal to 10% of the next quarter’s sales, and an inventory of materials equal to 15% of the next quarter’s production requirements. The company expects to have 3,440 desks and 55,000 feet of materials in inventory at 1/1/18.

The company budgets quarterly selling expenses at $925,000 plus a 3.5% commission on sales, and administrative expenses at $1,100,000 per quarter. Quarterly fixed selling expense includes $75,000 of depreciation, and quarterly administrative expenses include $120,000 of depreciation.

Answer 1

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you.
a. Sales Budget
		Year 1					Year 2
		Q1	Q2	Q3	Q4	Full Year	Q1	Q2	Q3	Q4	Full Year
Working		20%	30%	35%	15%	170000+20%	20%	30%	35%	15%	204000*120%
Budgeted Sales Units		40800	61200	71400	30600	204000	48960	73440	85680	36720	244800
Selling price per unit		$      210.00	$         210.00	$         210.00	$     210.00	$         210.00	$         210.00	$         210.00	$         210.00	$     210.00	$        210.00
Total Sales		$ 85,68,000	$1,28,52,000	$1,49,94,000	$64,26,000	$4,28,40,000	$1,02,81,600	$1,54,22,400	$1,79,92,800	$77,11,200	$5,14,08,000
b. Production Budget
		Q1	Q2	Q3	Q4	Full Year	Q1	Q2
Budgeted Sales Units		40,800	61,200	71,400	30,600	2,04,000	48,960	73,440
Add: Desired Ending Inventory		6,120	7,140	3,060	4,896	4,896	-
Total Needs		46,920	68,340	74,460	35,496	2,08,896	48,960
Less: Beginning Inventory of Finished Goods		-3,440	-6,120	-7,140	-3,060	-3,440	4,896
Budgeted Production		43,480	62,220	67,320	32,436	2,05,456	53,856
c. Raw Material Purchase Budget
		Q1	Q2	Q3	Q4	Full Year	Q1	Q2
Budgeted Production		43,480	62,220	67,320	32,436	2,05,456	53,856
Material needed per unit		10	10	10	10	10	10
Production Needs (feet)		4,34,800	6,22,200	6,73,200	3,24,360	20,54,560	5,38,560
Add: Desired Inventory of Raw Material		93,330	1,00,980	48,654	80,784	80,784
Total Needs		5,28,130	7,23,180	7,21,854	4,05,144	21,35,344
Less: Beginning Inventory of Raw Materials		-55,000	-93,330	-1,00,980	-48,654	-55,000
Raw Materials to be purchased (feet)		4,73,130	6,29,850	6,20,874	3,56,490	20,80,344
Cost of Raw Material per pound		$           5.00	$             5.00	$             5.00	$          5.00	$             5.00
Cost of total Raw Material to be purchased		$ 23,65,650	$   31,49,250	$   31,04,370	$17,82,450	$1,04,01,720
d. Direct Labor Budget
		Q1	Q2	Q3	Q4	Full Year
Budgeted Production		43480	62220	67320	32436	205456
Hours needed per unit		6	6	6	6	6
Total Budgeted Hours		2,60,880	3,73,320	4,03,920	1,94,616	12,32,736
Cost of per hour		$           9.00	$             9.00	$             9.00	$          9.00	$             9.00
Total Budgeted Direct Labor		$ 23,47,920	$   33,59,880	$   36,35,280	$17,51,544	$1,10,94,624
e. Manufacturing Overhead:
		Q1	Q2	Q3	Q4	Full Year
Variable Manufacturing Overhead:
Budgeted Labor Hours		260880	373320	403920	194616	1232736
Variable Overhead rate		$                6	$                  6	$                  6	$               6	$                  6
Total Variable Overhead	A	$ 15,65,280	$   22,39,920	$   24,23,520	$11,67,696	$   73,96,416
Fixed Manufacturing Overhead:
Depreciation		$      90,000	$         90,000	$     1,22,500	$ 1,22,500	$     4,25,000
Others (2521000-425000)/4		$   5,24,000	$     5,24,000	$     5,24,000	$ 5,24,000	$   20,96,000
Total Fixed Overhead	B	$   6,14,000	$     6,14,000	$     6,46,500	$ 6,46,500	$   25,21,000
Total Budgeted Overhead	A+B	$ 21,79,280	$   28,53,920	$   30,70,020	$18,14,196	$   99,17,416
Less: Depreciation		$     -90,000	$       -90,000	$    -1,22,500	$ -1,22,500	$    -4,25,000
Cash Disbursment for Overhead		$ 20,89,280	$   27,63,920	$   29,47,520	$16,91,696	$   94,92,416
Predeteremined Overhead rate -Fixed
Budgeted Fixed Overhead	C					$   25,21,000
Budgeted Labor Hours	D					1232736
Predeteremined Overhead rate -Fixed	C/D					$             2.05
f. Selling and Admin Budget
		Q1	Q2	Q3	Q4	Full Year
Fixed Selling Expense		$   8,50,000	$     8,50,000	$     8,50,000	$ 8,50,000	$   34,00,000
Depreciation-Selling		$      75,000	$         75,000	$         75,000	$     75,000	$     3,00,000
Variable Selling Expense	3.5% on sale	$   2,99,880	$     4,49,820	$     5,24,790	$ 2,24,910	$   14,99,400
Total Selling Budget	a	$ 12,24,880	$   13,74,820	$   14,49,790	$11,49,910	$   51,99,400
Administrative Expense		$   9,80,000	$     9,80,000	$     9,80,000	$ 9,80,000	$   39,20,000
Depreciation-Admin		$   1,20,000	$     1,20,000	$     1,20,000	$ 1,20,000	$     4,80,000
Total Admin Budget	b	$ 11,00,000	$   11,00,000	$   11,00,000	$11,00,000	$   44,00,000
Total selling and Admin budget	a+b	$ 23,24,880	$   24,74,820	$   25,49,790	$22,49,910	$   95,99,400