Question

EXCELL Only - When you create your budgets, you are required to use formulas in the cells of the spreadsheet to do the calculations.

\XYZ Company provided the following information to prepare budgets for January, February, and March 2023, including the total for the quarter.

Sales were 67,000 units in December 2022. Forecasted sales in units are: January, 70,000; February, 77,000; March, 89,000; April, 106,000. The selling price is $70 per unit.

Ending finished goods inventory must equal 20% of the next month’s expected unit sales. The December 31 finished goods inventory is 6,000 units, which does not comply with this requirement.

Ending direct materials inventory must equal 15% of the next month’s direct materials requirements. The December 31 direct materials inventory was 1,500 units, which does not comply with this policy. The budgeted March 31 direct materials inventory is 35,000 units. Each unit of finished goods inventory requires 3 units of direct materials. Direct materials cost is $24 per unit.

Each unit of finished goods inventory requires 45 minutes (3/4 hour) of direct labor at a rate of $29 per hour.

Factory overhead is allocated based on direct labor hours. The predetermined variable overhead rate is $3.25 per direct labor hour. Depreciation is $40,000 per month and is considered fixed factory overhead.

Sales commissions are 15% of sales and are paid in the month of sale. The sales manager’s salary is $6,000 each month.

Monthly general and administrative expenses include $5,000 of administrative salaries. It also includes $350 of interest payable on a long-term note.

Prepare the following budgets for Happy Day Company:

Sales budget

Production budget

Direct materials budget

Direct labor budget

Factory overhead budget

Selling expense budget

General and administrative expense budget

Answer 1

1. Sales budget:

- December 2022 sales: 67,000 units

- Forecasted sales in units: January 70,000, February 77,000, March 89,000, April 106,000

- Selling price per unit: $70

- Calculate the sales revenue for each month by multiplying the forecasted sales by the selling price.

2. Production budget:

- Calculate the required production units for each month by adding the forecasted sales units to the desired ending finished goods inventory, and subtracting the beginning finished goods inventory.

3. Direct materials budget:

- Determine the direct materials required for production by multiplying the required production units by the direct materials required per finished goods unit.

- Calculate the total direct materials cost by multiplying the direct materials required by the direct materials cost per unit.

4. Direct labor budget:

- Determine the direct labor hours required for production by multiplying the required production units by the direct labor hours per finished goods unit.

- Calculate the total direct labor cost by multiplying the direct labor hours required by the direct labor rate per hour.

5. Factory overhead budget:

- Calculate the variable factory overhead by multiplying the direct labor hours required by the predetermined variable overhead rate.

- Add the fixed factory overhead cost (depreciation) to calculate the total factory overhead cost.

6. Selling expense budget:

- Calculate the selling expense by multiplying the forecasted sales revenue by the sales commission rate.

- Include the sales manager's salary in the selling expense budget.

7. General and administrative expense budget:

- Include the administrative salaries and the interest payable on a long-term note in the general and administrative expense budget.