How do I prepare the bugdet and replace all question marks into formula? ### Budget and Production Planning Data#### Budgeted Unit Sales- **Year 2 Quarter 1**: 40,000 units- **Year 2 Quarter 2**: 60,000 units- **Year 2 Quarter 3**: 100,000 units- **Year 2 Quarter 4**: 50,000 units- **Year 3 Quarter 1**: 70,000 units- **Year 3 Quarter 2**: 80,000 units#### Sales and Receivables- **Selling Price Per Unit**: $12 per unit- **Accounts Receivable, Beginning Balance**: $65,000- **Sales Collection**: - 75% collected in the quarter sales are made - 25% collected in the quarter after sales are made#### Inventory- **Finished Goods Inventory**: - Desired ending inventory is 30% of the budgeted unit sales of the next quarter - Beginning inventory is 12,000 units- **Raw Materials Inventory**: - Required to produce one unit: 5 pounds - Desired ending inventory: 10% of the next quarter's production needs - Beginning inventory: 23,000 pounds#### Raw Material Costs- **Cost**: $0.80 per pound- **Purchases Payment Schedule**: - 60% in the quarter purchases are made - 40% in the quarter following purchase- **Accounts Payable for Raw Materials**, Beginning Balance: $81,500#### Labor and Overheads- **Direct Labor Cost**: $15 per hour- **Direct Labor Hour**: 0.2 hour per unit- **Variable Manufacturing Overhead (MOH) Rate**: $2 per hour- **Total Fixed MOH**: $60,000#### Selling and Administrative Expenses- **Variable S&A Expense Rate**: $1.80 per unit#### Financial Requirements- **Minimum Cash Balance**: $50,000- **Annual Interest Rate**: 12% **Construct the Direct Labor Budget**| Year 2 Quarter | 1 | 2 | 3 | 4 | Year ||-----------------|------|------|------|------|------|| **Required Production (units)** | ? | ? | ? | ? | ? || **Direct Labor-Hours per Unit** | ? | ? | ? | ? | ? || **Total Direct Labor-Hours Needed** | ? | ? | ? | ? | ? || **Direct Labor Cost per Hour** | ? | ? | ? | ? | ? || **Total Direct Labor Cost** | ? | ? | ? | ? | ? |This table is designed for constructing a direct labor budget. It is segmented into four quarters of Year 2 and includes an overall yearly total on the far right. Each row asks for specific inputs:1. **Required Production (units):** The number of units expected to be produced in each quarter and for the entire year.2. **Direct Labor-Hours per Unit:** How many labor hours are needed to produce one unit in each quarter and overall.3. **Total Direct Labor-Hours Needed:** The total labor hours required for production based on the required production and labor-hours per unit.4. **Direct Labor Cost per Hour:** The cost of one hour of labor for each quarter and the year.5. **Total Direct Labor Cost:** Calculated by multiplying the total direct labor-hours needed by the direct labor cost per hour for each quarter and for the year. Each of these elements is crucial for understanding labor costs and planning financially for production schedules.

Budgeting is an important part of the business process. Without budgeting a business cannot track its process and improves its performance. It is the process of estimating the revenue and expenses of a company for a specified period of time in future. Working note: Production budget:

Business

Accounting

Data Year 2 Quarter Year 3 Quarter 1 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 • Selling price per unit • Accounts receivable, beginning balance • Sales collected in the quarter sales are made • Sales collected in the quarter after sales are made • Desired ending finished goods inventory is • Finished goods inventory, beginning • Raw materials required to produce one unit • Desired ending inventory of raw materials is • Raw materials inventory, beginning • Raw material costs • Raw materials purchases are paid and • Accounts payable for raw materials, beginning balance $12 per unit $65,000 75% 25% 30% of the budgeted unit sales of the next quarter 12,000 units 5 pounds 10% of the next quarter's production needs 23,000 pounds $0.80 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase $81,500 •Direct labor cost per hour •Direct labor hour per unit $15 per hour 0.2 hour per unit •Variable MOH rate •Total fixed MOH $2 per hour $60,000| •Variable S&A expense rate $1.80 per unit $50,000 12% •Minimum cash balance •Annual Interest rate