Use the following Excel Template to complete your assignment for this week:

Excel Template

Given financial information, prepare Coopie Awards' master budget. Notice, you will prepare a different budget on each tab of the spreadsheet. The last tab in the spreadsheet you will complete for the discussion forum.

Submit your completed assignment to the drop box below. Please check the Course Calendar for specific due dates.

Save your assignment as a Microsoft Excel document. (Mac users, please remember to append the ".xlsx" extension to the filename.) The name of the file should be your first initial and last name, followed by an underscore and the name of the assignment, and an underscore and the date. An example is shown below:

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 fo Requirement: Prepare the sales budget for Coopie Awards Region Western Central Eastern Total Units Coopie Awards Sales Budget For the current Year Sales Volume Selling Price Total Sales sells for $35. 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 s. 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 24 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 ntory is 240,000 square inches. 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 9400 $5.00 $47,000.00 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 or 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 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 Cost of Goods sold Gross Profit Selling and administrative expenses Income from operations Income Tax Net Income $0 0 $0 0 $0 0 $0 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. Budget based on 45000 unis Sales Revenue Direct Materials costs Direct Labor Costs Overhead Costs total dierct costs Selling and administrative cost operating income Actual Results $ 1,548,000 $ 498000 94800 126000 $ 718,800 $ 700000 $ 129,200 $ Variance 1,575,000 $27,000 Unfavorable 518400 $20,400 Favorable 99000 $4,200 Favorable 126,000 $0 743,400 $24,600 Favorable 729550 $29,550 Favorable 102,050 ($27,150) Favorable