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 quarters sales, and an inventory of materials equal to 15% of the next quarters 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.