Project
You need to use cell references in the development of your budgets. You must use this worksheet to reference the data that is being inputted onto the budgets on the solution worksheet.
The budget director for Bird House Unlimited, Inc., has gathered the following data for use in developing the budgeted income statement for November 20XX.
| Estimated sales for November |
| Bird House |
28,600 units at $33 per unit |
| Bird Feeder |
24,000 units at $37 per unit |
| Direct materials |
|
|
| Estimated inventories at November 1(beginning) |
|
|
| Wood |
2600 feet |
|
| Plastic |
3200 pounds |
|
| Estimated inventories at November 30th(ending) |
|
|
| Wood |
3500 feet |
|
| Plastic |
2800 pounds |
|
|
|
|
| Direct materials used in production: |
|
|
| In the manufacture of a Bird House: |
|
|
| Wood |
0.80 feet per unit of product |
|
| Plastic |
0.50 pounds per unit of product |
|
|
|
|
| In the manufacture of a Bird Feeder: |
|
|
| Wood |
1.20 feet per unit of product |
|
| Plastic |
0.70 pounds per unit of product |
|
|
|
|
| Estimated cost of direct materials |
|
|
| Wood |
$6.00 per feet |
|
| Plastic |
$0.80 per pound |
|
|
|
|
| Work in process-given in total of the 2 products |
|
|
| Estimated inventories at November 1(beginning) |
$2,000 |
|
| Estimated inventories at November 30th(ending) |
$3,000 |
|
|
|
|
| Finished Goods |
|
|
| Estimated inventories at November 1(beginning) |
|
|
| Bird House |
4,000 units at $13 per unit |
|
| Bird Feeder |
2,500 units at $14 per unit |
|
|
|
|
| Estimated inventories at November 30th(ending) |
|
|
| Bird House |
5,000 units at $13 per unit |
|
| Bird Feeder |
2,000 units at $14 per unit |
|
|
|
|
| Direct Labor Requirements: |
|
|
| Bird House |
|
|
|
Fabrication Department |
0.25 hour at $14 per hour |
|
Assembly Department |
0.3 hour at $11 per hour |
| Bird Feeder |
|
|
|
Fabrication Department |
0.45 hour at $14 per hour |
|
Assembly Department |
0.35 hour at $11 per hour |
| Estimated Manufacturing Overhead Costs for November |
|
| Indirect Factory Wages |
|
$2,000 |
| Depreciation of Plant and Equipment |
1,000 |
| Power and Light |
|
4,000 |
| Insurance and property tax |
|
3,000 |
|
|
|
| Estimated operating expenses for November |
|
| Sales salaries expense |
|
$2,000 |
| Advertising expense |
|
10,000 |
| Office salaries expense |
|
5,000 |
| Depreciation expense-office equipment |
4,000 |
| Telephone expense-selling |
|
5,000 |
| Telephone expense-administrative |
2,000 |
| Travel expense-selling |
|
2,000 |
| Office supplies expense |
|
3,000 |
| Miscellaneous Administrative expense |
6,000 |
|
|
|
| Estimated other income and expense for November |
|
| Interest Revenue |
|
$14,000 |
| Interest Expense |
|
10,000 |
|
|
|
| Estimated tax rate |
|
30% |
Project 4 Objectives:
1. Develop operating budgets
2. Analyze the cash flow of the company.
3. Analyze how changes in cost or revenue impact the budgets.
4. Provide recommendations to management.
Questions:
1. Prepare a sales budget for November
2. Prepare a production budget for November.
3. Prepare a supporting schedule to determine required material for production and a direct materials purchase budget for November
4. Prepare a direct labor cost budget for November.
5. Prepare a factory overhead cost budget for November
6. Prepare a cost of goods sold budget for November.
7. Prepare a selling and administrative budget for November, set up based on categorization of expenses into selling and administrative costs.
8. Prepare a budgeted income statement for November.
9. What if the company decides to start a new advertising campaign