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 materials10ft.@ $5.00/ft.$ 50.00

Direct labor6 hrs. @ $9.00/hr.54.00

Variable overhead6 hrs. @ $6.00/hr.36.00

Total budgeted cost/unit$140.00

Annual fixed overhead totals $2,521,000, which includes $425,000 of depreciationbudgeted 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.

PROJECT REQUIREMENTS

Remember, do not combine text and numbers in the same cell if you will use the number in a formula in your spreadsheet.Note, in some cases you will have to make projections for 2019.You do not, however, need to verify the 1/1/18 beginning balances givenuse the information provided.

1.What equations can I use to produce a sales budget in units and dollars by quarter and for the year.

2.What equations can I use to produce a production budget in units by quarter and for the year.

3.What equations can I use to produce a materials purchases budget in feet and dollars by quarter and for the year.

4.What equations can I use to produce a direct labor budget in hours and dollars by quarter and for the year.

5.What equations can I use to produce an overhead budget by quarter and for the year.Show depreciation separately from other fixed overhead.

6.What equations can I use to produce a selling and administrative expense budget by quarter and for the year.Show depreciation separately from other fixed selling and administrative costs.