assignment mod 05 Excel Home Insert Draw Page Layout Formulas Data Review Tel me what you want to do View Help X Cut A A Calibri 11 Wrap Text General Copy aste B IU Conditional Format as Cell 00 Formatting Table Styles- Merge & Center $ % 9 Format Painter Clipboard Font Alignment Number Styles A B D G H 1 Assume the estimated January 1 innventory for finished goods and work in process were $54,000 and $47,000 respectively. Also assume the desired December 31 inventories for 2 finished goods and work in process were $50,000 and $49,000 respectively. Factory overhead was budgeted for $126,000. 3 4 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 9 Work in process Inventory January 1 10 Direct Materials: 11 Direct materials inventory January 1 (Note a) 12 Direct materials purchases 13 Cost of direct materials available for sale 14 Less direct materials December 31 (note b) 15 Cost of direct materials used in production 16 Direct labor 17 Factory Overhead 18 Cost of goods added to production 19 Cost of goods available 20 Less work in process December 31 inventory 21 Cost of goods manufactured 22 Plus Finished goods January 1 23 Less finished goods December 31 24 Cost of goods sold 25 26 Note a: 240000 X S0.12 27 Note b: 200000x.12 28 Income Statement Actual Results Selling and Admin Budoet Direct labor cost budget Cost of Goods Sold Budget Direct Materials Budget Reade Draw Insert Home File ab Wrap Te cut A A 11 Calibri PCopy A Merge & B IU Paste Format Painter Alignment Font Clipboard fx X F15 E D B 1 The following are date for the sales and administrative budget for Coopie Awards: 2 Sales Commissions 3 Advertising Expense 4 Misc. selling expense 5 Office salaries expense 6 office supplies expense 7 Misc administrative expense 7% of sales 18% of sales $1,750 plus 4% of sales $12,000 per month 5% of sales $1,400 per month plus 2% of sales 8 9 Requirement: Prepare the selling and admin budget for Coopie Awards Coopie Awards Selling and Administrative Budget 10 11 For the current Year 12 13 Selling Expenses: Sales commissions 14 Advertising expenses 15 Miscellaneous selling expenses Total selling expenses 18 Administrative expenses: 19 Office salaries Office supplies 20 Miscellaneous administrative expenses Total administrative expenses 23 Total selling and administrative expenses 21 22 24 25 26 27 Insert File Home Draw Page Layout Formulas Data Review Cut A A Calibri 11 Copy Paste BIU A Format Painter Clipboard Font Align fx F11 A B D E 1 Coopie Awards pays 40% in income taxes. 2 3 Requirement: Prepare Coopie Awards' budgeted income statement 4 5 Coopie Awards Budgeted Income Statement For the current year 7 8 Sales Revenue $1,575,000 9 Cost of Goods sold 10 Gross Profit 11 Selling and administrative expenses 12 Income from operations $1,575,000 0 $1,575,000 13 Income Tax 630000 14 Net Income $945,000 15 16 17 18 19 20 23 25 26 27 assignment mod 05 Excel File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Cut .A Calibri A 11 Wrap Text Currency Copy Paste Conditional Formatting IU B A . $ % Merge & Center Format Painter Clipboard Font Alignment Number B10 112932 A D E F G H 1 Coopie Awards budgeted proction of 47,000 brass plaques. Each plaque requires 12 minutes of engraving. Engraving labor costs $11.00 per hour. 2 3 Required: Prepare the direct labor cost budget for Coopie Awards 4 Coopie Awards Cost of Direct Labor Budget 5 6 7 For the Current Year 8 Hours required for production (47,000x12/60) 9 Hourly rate 10 Total Direct Labor cost 9,400 $11.00 $112,932.00 11 12 13 14 15 16 17 18 19 20 21 22 assignment mod 05 Eecel File Home Insert Draw Page Layout Formulas Data Review View Q Tell me what you want to do Help X Cut 11 A A Calbr Wap Text Copy Currency Paster BIU Format Painter Conditional Format as Cell Fomatting Table ++ Merge & Center S % Insert Delese Fomat Styles Clipboard Font Aigments Number Syles D13 Cels 1575000 A G K M N Coople 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. 2 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 P 4 Requirement: Prepare the sales budget for Coopie Awards 5 6 Coopie Awards 7 Sales Budget For the current Year 9 Region Sales Volume Selling Price Total Sales 10 Western 20,000 535 $700,000 11 Central 10,000 $35 $350,000 12 Eastern 15.000 $35 $525,000 $1,575,000 45.000 13 Total Units 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Inc 28 Cost of Goods Sold Budget Selling and Admin Budget Drect labor cost budget Direct Materials Budget Sales Budget Production Budget Type here to search W assignment mod 05 Excel File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Cut Calibri A A 11 b Wrap Text Number Copy Paste BIU Conditional Format as Cell Formatting Table Styles A. Format Painter Merge & Center" Clipboard Font Alignment Numben Styles B12 47000 A C D E F G Hi K M. 1 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. 2 3 Requirement: Prepare the production budget in units for Coopie Awards. Coopie Awards Production Budget For the current year 45,000 Sales 9 Plus desired ending inventory 10 Total 11 Less estimated beginning inventory 12 Total Production 5,000 50,000 3,000 47,000 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Inc .. Selling and Admin Budget Cost of Goods Sold Budget 28 Direct labor cost budget Sales Budget Production Budget Direct Materials Budget assignment mod 05-Excel Fle Home Insert Draw Page Layout Formulas Data Review View Q Tell me what you want to do Help do Cut 11 A A Calibri sb Wae Test Curency Copy Paster BIU Format Painter A. Conditional Fomat as Cell Formating + Merge & Center $ % Insert Delete Fom Table Seyles Cipboard font Alignment Number Styles Cetu B15 536640 C D E H G N L M C Coopie Awards budgeted production of 47.000 brass plaques for the vear, 96 square inches are required for each brass plaque. The estimated January 1 brass sheet inventory is 240.000 square inches 1 The desired December 31 brass sheet inventory is 200,000 square inches. Brash sheet costs $0.12 per square inch. 2 4 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) 10 Plus desired ending inventory 11 Total Needed 12 Less estimated beginning inventory 13 Total units to be purchased 14 Unit price 15 Total direct materials purchases 4,512,000 200,000 4,712,000 240,000 4,472.000 $0.12 S536,640.00 16 17 18 19 20 21 22 23 24 25 inc. 26 Selling and Admin Budgut Cost of Goods Sold Budoet 27 Drect labor cost budget 28 Direct Materials Budget Production Budget Sales Budget 3 assignment mod 05 Excel File Homes Insert Draw Page Layout Formulas OTell me what you want to do Data Review Help View Cut Calibri 11 A A gb Wrap Text Currency Copy Paste BIU A $ % Merge & Center YFormat Painter Cipboand Font Alignment Number B10 112932 A B C D G H 1 Coopie Awards budgeted proction of 47,000 brass plaques. Each plaque requires 12 minutes of engraving. Engraving labor costs $11.00 per hour. 3 Required: Prepare the direct labor cost budget for Coopie Awards 4 Coopie Awards 6 Cost of Direct Labor Budget 7 For the Current Year 8 Hours required for production (47,000X12/60) 9 Hourly rate no Total Direct Labor cost 9,400 $11.00 $112,932.00 11 12 13 14 15 16 17 18 19 20 21 22 23 24 26 27 28 Sales Budget Direct Materials Budget Production Budget Direct labor cost budget Cost of Goods Sold Budget Selling and Admin Budge Type here to search