Manufacturing Budget
shodel1
excel_spreadsheet.xlsxSales Budget
| Coopie Awards produces brass plaques. They operates in 3 regions of the country. The western region sales manager estimates sales of 20,000 plaques for the region. | |||
| The central regional manager estimates sales of 10,000 plaques for the region and the eastern sales manager estimates slaes of 15,000 plaques for the region. Each plaque sells for $35. | |||
| Requirement: Prepare the sales budget for Coopie Awards | |||
| Coopie Awards | |||
| Sales Budget | |||
| For the current Year | |||
| Region | Sales Volume | Selling Price | Total Sales |
| Western | |||
| Central | |||
| Eastern | |||
| Total Units | |||
Production Budget
| Coopie Awards projected sales of 45,000 brass plaques for the year. The estimated January 1 inventory is 3,000 plaques, and the desired December 31 inventory is 5,000 units. |
| Requirement: Prepare the production budget in units for Coopie Awards. |
| Coopie Awards |
| Production Budget |
| For the current year |
| Sales |
| Plus desired ending inventory |
| Total |
| Less estimated beginning inventory |
| Total Production |
Direct Materials Budget
| Coopie Awards budgeted production of 47,000 brass plaques for the year. 96 square inches are required for each brass plaque. The estimated January 1 brass sheet inventory is 240,000 square inches. |
| The desired December 31 brass sheet inventory is 200,000 square inches. Brash sheet costs $0.12 per square inch. |
| Requirement: Prepare the direct materials purchases budget for Coopie Awards |
| Coopie Awards |
| Direct Materials Purchases Budget |
| For the current Year |
| Units Required for Production (47,000X96) |
| Plus desired ending inventory |
| Total Needed |
| Less estimated beginning inventory |
| Total units to be purchased |
| Unit price |
| Total direct materials purchases |
Direct labor cost budget
| Coopie Awards budgeted proction of 47,000 brass plaques. Each plaque requires 12 minutes of engraving. Engraving labor costs $11.00 per hour. |
| Required: Prepare the direct labor cost budget for Coopie Awards |
| Coopie Awards |
| Cost of Direct Labor Budget |
| For the Current Year |
| Hours required for production (47,000X12/60) |
| Hourly rate |
| Total Direct Labor cost |
Cost of Goods Sold Budget
| Assume the estimated January 1 inventory for finished goods and work in process were $54,000 and $47,000 respectively. Also assume the desired December 31 inventories for |
| finished goods and work in process were $50,000 and $49,000 respectively. Factory overhead was budgeted for $126,000. |
| Using the information on the previous tabs, prepare a cost of goods sold budget for Coopie Awards for the current year. |
| Coopie Awards |
| Budgeted Cost of Goods Sold |
| For the current year |
| Work in process Inventory January 1 |
| Direct Materials: |
| Direct materials inventory January 1 (Note a) |
| Direct materials purchases |
| Cost of direct materials available for sale |
| Less direct materials December 31 (note b) |
| Cost of direct materials used in production |
| Direct labor |
| Factory Overhead |
| Cost of goods added to production |
| Cost of goods available |
| Less work in process December 31 inventory |
| Cost of goods manufactured |
| Plus Finished goods January 1 |
| Less finished goods December 31 |
| Cost of goods sold |
| Note a: 240000 X $0.12 |
| Note b: 200000X.12 |
Selling and Admin Budget
| The following are date for the sales and administrative budget for Coopie Awards: | |
| Sales Commissions | 7% of sales |
| Advertising Expense | 18% of sales |
| Misc. selling expense | $1,750 plus 4% of sales |
| Office salaries expense | $12,000 per month |
| office supplies expense | 5% of sales |
| Misc administrative expense | $1,400 per month plus 2% of sales |
| Requirement: Prepare the selling and admin budget for Coopie Awards | |
| Coopie Awards | |
| Selling and Administrative Budget | |
| For the current Year | |
| Selling Expenses: | |
| Sales commissions | |
| Advertising expenses | |
| Miscellaneous selling expenses | |
| Total selling expenses | |
| Administrative expenses: | |
| Office salaries | |
| Office supplies | |
| Miscellaneous administrative expenses | |
| Total administrative expenses | |
| Total selling and administrative expenses | |
Income Statement
| Coopie Awards pays 40% in income taxes. | |
| Requirement: Prepare Coopie Awards' budgeted income statement | |
| Coopie Awards | |
| Budgeted Income Statement | |
| For the current year | |
| Sales Revenue | $0 |
| Cost of Goods sold | 0 |
| Gross Profit | $0 |
| Selling and administrative expenses | 0 |
| Income from operations | $0 |
| Income Tax | 0 |
| Net Income | $0 |
Actual Results
| Coopie Awards sold 43,000 brass plaques for the current year with the following results. In the discussion forum comment on Coopie Awards results. | ||||
| Hint: Preparing a flexible budget will help in analyzing the results. Please show the flexible budget on this tab. | ||||
| Actual Results | Budget based on 45000 unis | Variance | ||
| Sales Revenue | $ 1,548,000 | $ 1,575,000 | $27,000 | Unfavorable |
| Direct Materials costs | 498000 | 518400 | $20,400 | Favorable |
| Direct Labor Costs | 94800 | 99000 | $4,200 | Favorable |
| Overhead Costs | 126000 | 126,000 | $0 | |
| total dierct costs | $ 718,800 | $ 743,400 | $24,600 | Favorable |
| Selling and administrative cost | 700000 | 729550 | $29,550 | Favorable |
| operating income | $ 129,200 | $ 102,050 | ($27,150) | Favorable |
