please complete sheets 7-11
attached bellow are supporting information for questions
A1 X fx E B D 15 Division N has decided to develop its budget based upon projected sales of 33,000 lamps at 16 $48.00 per lamp 17 The company has requested that you prepare a master budget for the year. This budget is to be used 18 for planning and control of operations and should be composed of. 26 27 1. Production Budget 28 29 2. Materials Budget 30 38 3. Direct Labor Budget 39 40 4. Factory Overhead Budget 41 42 5. Selling and Administrative Budget 50 51 6. Cost of Goods Sold Budget 52 53 7. Budgeted Income Statement 54 62 8. Cash Budget 63 64 Notes for Budgeting: 65 66 74 The company wants to maintain the same number of units in the beginning and ending inventories of 75 work-in-process, and electrical parts while increasing the figurines inventory to 675 pieces and 76 increasing the finished goods by 23.00% 77 78 Complete the following budgets 86 87 1 Production Budget 88 89 Planned Sales 90 Desired Ending Inventory of Finished Goods (roundup to the next unit) 98 Total Needed 99 Less: Beginning Inventory 100 101 Total Production {7.01) 102 FAQ 1 2 Introduction 3 4 5 6 7 8 9 10 11 18 19 20 Fox B D E F G 2 Materials Budget (8.01) (8.02) Figurines Needed for Production Desired Ending Inventory Total Needed Less: Beginning Inventory Total Purchases Cost per piece Cost of Purchases (Round to two places, S##) (8.03) (8.04) {8.05) 3 4 5 6 7 16 17 18 19 20 29 30 31 32 33 42 43 14 45 16 55 56 57 58 59 38 59 70 71 72 81 82 83 84 Electrical Parts Needed for Production Desired Ending Inventory Total Needed Less: Beginning Inventory Total Purchases Cost per piece Cost of Purchases (Round to two places, $####) (8.06) (8.07) Lamp Shades - not inventoried they arrive from the shop next door Just-in-time. Needed for Production Desired Ending Inventory Total Needed Less: Beginning Inventory Total Purchases Cost per piece Cost of Purchases (Round to two places, st) (8.08) Introduction FAQ 1 3 4 5 6 8 9 10 11 18 19 20 21 Tvne here to cearch Cost per piece Cost of Purchases (Round to two places, S####) (8 3 Direct Labor Budget 72 81 82 83 84 85 94 95 96 97 98 107 108 109 110 111 120 121 Labor Cost Per Lamp Production Total Labor Cost (Round to two places, St#.### $ 1.00 {8.0 4 Factory Overhead Budget Variable Factory Overhead Variable Factory Overhead Cost Per Unit Number of Units to be Produced Total Variable Factory Overhead (Round to two places, S#) Fixed Factory Overhead 122 (8.10 123 124 125 126 127 Total Factory Overhead (Round to two places, $ ) (8.11 Predetermined Factory Overhead Rate based upon the budgeted total factory OH, divided by the budgeted number of units to be produced, and then rounded to seven places, $## ####) {8.12) 128 129 130 131 132 133 194 B D E F G H 4 5 5 Selling and Admin. Budget 6 7 Fixed Selling B Variable Selling (Round to two places, S####) 8 Fixed Administrative 9 Variable Administrative (Round to two places, $######) 20 Total Selling and Administrative (Round to two places, 5.) (9.01) 1 2 6 Cost of Goods Sold Budget - Assume FIFO (First In, First-Out) and overhead is applied based on the number of units to be produced. 2 3 Round dollars to seven places, 4 Cost of making one unit next year $###### 5 Material cost per unit 6 Labor Cost Per Lamp 6 Factory overhead per unit 7 8 Total cost of one unit (9.02) (Round to seven places, Sww.www) Round dollars to two places, 0 S#### 0 Beginning Inventory, Finished Goods (9.03) 1 Production Costs: Materials: 3 Figurines: Beginning Inventory 4 Purchased Available for Use 5 Ending Inventory of Figurines Figurines Used In Production {904) Electrical Parts Beginning Inventory Purchased 3 Available for Use 1 Ending Inventory of Electrical Parts Introduction FAQ 1 2 3 4 56 7 8 9 10 11 18 19 20 9 4 9 3 A1 X & fx - A B D E F G H Round dollars to seven places, $##.####### -4 55 36 16 47 18 Cost of making one unit next year Material cost per unit Labor Cost Per Lamp Factory overhead per unit Total cost of one unit (Round to seven places, S##.##*****##) {9. 19 Round dollars to two places. $##.## {9.0 (9.04 50 60 61 62 63 64 74 75 76 77 78 88 89 90 91 92 102 103 104 105 106 116 117 118 119 120 130 131 Beginning Inventory, Finished Goods Production Costs: Materials: Figurines: Beginning Inventory Purchased Available for Use Ending Inventory of Figurines Figurines Used In Production Electrical Parts Beginning Inventory Purchased Available for Use Ending Inventory of Electrical Parts Electrical Parts Used In Production Lamp Shades: Lamp Shades Used In Production Total Materials Labor Overhead Cost of Goods Available Less: Ending Inventory, Finished Goods Cost of Goods Sold {9.05) {9.06) {9.07) {9.08) {9.09) {9.10) {9.11) {9.12) Introduction FAQ 4 5 6 8 9 10 11 18 19 A1 D E K (10.01) 4 F G H 6 7 7 Budgeted Income Statement 8 9 Sales 16 Cost of Goods Sold 17 Gross Profit 18 Selling Expenses & Admin Expenses 19 Net Operating Income 20 27 28 29 30 31 Cash Budget 38 39 Assume actual cash receipts and disbursements will follow the pattern below: (Note: Receivables and 40 Payables of 12/31/x1 will have a cash impact in 20x2.) 41 42 1. 20.00% of sales for the year are made in November and December. Since our customers have 60 day terms 49 those funds will be collected be collected in January and February. 50 2. 81.00% of material purchases will be paid during the year, the remaining portion will be paid in Januay or February 51 3. All other manufacturing and operating costs are paid for when incurred. 52 4. The budgeted depreciation expense is equal to 0.6% of the fixed manufacturing, selling and administrative expenses. 53 5. Minimum Cash Balance needed for 20x2, 5190,000 60 I See The Light 61 Projected Cash Budget For the Year Ending December 31, 20x2 62 Round dollars to two places, SNW.44 63 64 71 72 73 74 75 82 83 Beginning Cash Balance Cash Inflows: Sales Collections: Account Receivable (Sales last year not collected) Sales made and collected in 20x2 Cash Available (1002) (1003) (1004) Cash Outflows Introduction FAQ 1 2 3 4 5 6 7 8 9 10 11 18 19 20 | 21 Type here to search o BE AutoSave On KF7131 - Excel e Search File Home Insert Page Layout Formulas Data Review View Help SmartArt Get Add-ins b? Shapes Icons 3D Models Pivottable Recommended Table Pivot Tables 38 Pictures Screenshot My Add-ins Recommended Charts Tables illustrations Add-ins X118 X D E H K 4ABC 60 61 62 G I See The Light Projected Cash Budget For the Year Ending December 31, 20x2 Round dollars to two places, S### Beginning Cash Balance Cash Inflows: Sales Collections Account Receivable (Sales last year not collected) Sales made and collected in 20x2 Cash Available (1002) (10.03) {10.04 63 64 71 72 73 74 75 B2 33 84 35 36 3 14 15 6 7 34 05 16 7 8 9 o (10.05) Cash Outflows, Purchases Accounts Payable (Purchases last year) Material purchases made and paid for in 20X2 Other Manufacturing Costs Direct Labor Total Manufacturing Overhead Selling and Administrative Less: Depreciation Total Cash Outflows {10.06) {10.07) Budgeted Cash Balance before financing Needed Minimum Balance (10.08) Amount to be borrowed (if any) (10.09) Budgeted Cash Balance (10.10) Introduction FAQ 1 2 3 3 4 5 on 6 7 8 9 10 11 18 19 20 21 B D E F G H 20x2 Cost Rounded to 7 Decimal Places 5 6 7 B 3 0 9 Variable Cost of making one unit next year used to calculate the Ending Inventory of Finished Goods Material cost per unit Labor Cost Per Lamp Variable Factory overhead per unit Total variable manufacturing cost of one unit (11.01) 3 4 5 10 Budgeted Operating Income Using Variable (Direct) Costing 20x2 Cost Rounded to 2 Decimal Places Sales (11.02) (11.03) Variable Cost of Goods Sold - Assume FIFO (First-In, First-Out) Beginning Inventory, Finished Goods (Variable Costing) Production Costs: Materials Figurines Electrical Parts Lamp Shades Labor Variable Overhead Total Variable Production Costs Cost of Goods Available For Sale Less Ending Inventory, Finished Goods (Variable Costing) Variable Cost of Goods Sold Variable Selling (Round to two places, www) Variable Administrative (Round to two places, S##.##) Total Variable Costs Contribution Margin Fixed Costs: Fixed Manufacturing Overhead Fixed Selling Fixed Administrative Total Fixed Introduction FAQ 1 2 3 4 5 (11.04) (11.05) (11.06) (11.07 (11.08) 6 7 8 9 10 11 18 19 20 21 A1 fr D E G H (11.02) (11.03) MA B 20 Variable Cost of Goods Sold - Assume FIFO (First-In, First-Out) 21 Beginning Inventory, Finished Goods (Variable Costing) 2 Production Costs: 4 Materials: 5 Figurines 6 Electrical Parts 7 Lamp Shades Labor: Variable Overhead: 1 Total Variable Production Costs 2 Cost of Goods Available For Sale Less Ending Inventory, Finished Goods (Variable Costing) Variable Cost of Goods Sold Variable Selling (Round to two places, S####) Variable Administrative (Round to two places, S### ##) Total Variable Costs Contribution Margin Fixed Costs: Fixed Manufacturing Overhead Fixed Selling Fixed Administrative Total Fixed Operating Income, Variable Costing (11.04) (11.05) (11.06) (11.07) (11.08) (11.09) Operating Income, Absorption Operating Income, Variable Costing Excess (Absorption Costing Operating Income - Variable Costing Operating Income) (11.10) Budgeted Fixed Overhead Budgoted Number of Units to be Produced Budgeted Fixed Cost Per Unit (Round to 7 decimals #. (11.11) Fixed Manufacturing Overhead in the Ending Inventory Fixed Manufacturing Overhead in the Beginning Inventory Increase (Fixed Manufacturing Overhead in the Ending Inventory-Fixed Manufacturing Overhead in the Beginning Inventory) (11.12) (11.13) (11.14) Introduction FAQ 1 2 3 4 5 5 6 7 ce 9 10 11 18 19 20 21 I See The Light Projected Income Statement For the Period Ending December 31, 20x1 $1,125,000.00 723,250.00 $ 401,750.00 Sales 25,000 lamps @ $45.00 Cost of Goods Sold @ $28.93 Gross Profit Selling Expenses: Fixed Variable (Commission per unit) @ $3.15 Administrative Expenses Total Selling and Administrative Expenses: Net Profit $ 23,000.00 78,750.00 $101,750.00 41,250.00 143,000.00 $ 258,750.00 I See The Light Projected Balance Sheet As of December 31, 20x1 34,710.00 67,500.00 Current Assets Cash Accounts Receivable Inventory Raw Material Figurines Electrical Sets Work in Process Finished Goods Total Current Assets 500 @ $9.20 500 @ $1.25 0 3000 @ $28.9250 4,600.00 625.00 86,775.00 $ 194,210.00 Fixed Assets Equipment Accumulated Depreciation Total Fixed Assets Total Assets $ 20,000.00 6,800.00 13,200.00 $207,410.00 $ 54,000.00 54,000.00 Current Liabilities Accounts Payable Total Liabilities Stockholder's Equity Common Stock Retained Earnings Total Stockholder's Equity Total Liabilities and Stockholder's Equity $ 12,000.00 141.410.00 153.410.00 $ 207 410.00 Introduction FAQ 2 3 5 6 7 8 9 10 11 18 19 20 21 File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer te A1 f B D E K 7 8 The projected cost of a lamp is calculated based upon the projected increases or decreases to 9 current costs. The present costs to manufacture one lamp are: 10 11 Figurines $92000000 per lamp 12 Electrical Sets 12500000 per lamp 16 Lamp Shade 6.0000000 per lamp 17 Dired Labor 2.2500000 per lamp (4 lampshr) 18 Variable Overhead 0.2250000 per lamp 19 Foed Overhead 10.0000000 per lamp (based on normal capacity of 25,000 lamps) 20 24 Cost per lamp $23.0280000 per lamp 25 26 Expected increases for 2012 27 When calculating projected increases round to SEVEN decimal places 500000000 28 1. Material Costs are expected to increase by 2.50% 34 2 Labor Costs are expected to increase by 5.50% 35 35 3. Variable Overhead is expected to increase by 600% 40 41 4. Fred Overhead is expected to increase to $300,000 42 43 5. Faed selling expenses are expected to be $35,000 in 2012 44 6. Variable selling expenses (measured on a per lamp basis) are expected to increase by 3.00% 7. Fored Administrative expenses are expected to increase by 510,000 The total administrative expenses for 200 were 541,205.00, when 23.500 units were sold Use the High-Low method to calculate the total fed administrative expense 8. Variable administrative expenses (measured on a perlamp basis) are expected to 60 increase by 6 50%. The total administrative expenses for 200 were 541.205.00, when 23.500 units were sold Use High-Low method to calculate the variable administrative pense per lamp 67 On the following schedule develop the following tures 58 1- 2010 Projected Variable Manufacturing Unit Cost of 2- 2012 Projected Variable Unt Cost per lamp 3- 2012 Projeledred Costs 75 Introduction FAQ 1 2 3 5 6 8 9 10 11 18 19 Type here to search o BE 8 9 Variable Manufacturing Unit Cost 20x1 Cost Projected 20x2 Cost Rounded to Percent 7 Decimal Places 10 Increase 11 Figurines 9.2 102.50% $9.4300000 {4.01) 12 Electrical Sets 1.25 102.50% $1.2812500 {4.02) 13 Lamp Shade 6 102.50% $6.1500000 {4.03) 18 Labor 2.25 105.50% $2.3737500 {4.04) 19 Variable Overhead 0.225 106.00% $0.2385000 {4.05) 20 21 Projected Variable Manufacturing Cost Per Unit $19.4735000 {4.06) 22 27 28 29 Total Variable Cost Per Unit 20x1 Cost Projected 20x2 Cost Rounded Percent to 7 Decimal Places 30 Increase 31 Variable Selling 3.15 103.00% 3.2445000 4.07) 36 Variable Administrative 20x1 0.0300000 106.50% {4.08) 37 Variable Administrative 20x2 0.03 106.50% 0.0319500 (409) 38 39 Projected Variable Manufacturing Unit Cost {4.06) 40 Projected Total Variable Cost Per Unit 22.7499500 (410) 45 46 47 48 Schedule of Fixed Costs 20x1 Cost Projected 20x2 Cost Rounded 49 Increase to 2 Decimal Places 54 Fixed Overhead $ 300,000.00 {4.11) 55 (normal capacity of lamps @_) 56 Fixed Selling 35,000.00 (4.12) 57 Fixed Administrative 20x1 (4.13) 58 Fixed Administrative 20x2 $ 50,500.00 (4.14) 63 Projected Total Fixed Costs $ 385,500.00 (4.15) 64 65 Introduction FAQ 1 2 3 4 5 | 6 | 7 8 9 10 11 {5.01) {5.02) 22 price per unit. 23 24 1. For 20x2 the selling price per lamp will be $45.00. What is the projected contribution margin and contribution 30 margin ratio for each lamp sold? 31 45 32 19.4735 33 3.2445 34 0.03195 40 41 42 43 44 Contribution Margin per unit (Round to seven places, S##.##### $22.2500500 50 51 Contribution Margin Ratio (Round to seven places,% is two of those places ##.##### 49.44456% 52 53 54 2. For 20x2 the selling price per lamp will be $45.00. How many lamps must be sold to breakeven? 60 61 FIXEDC 385500 62 63 64 70 71 72 73 74 80 Breakeven sales in units (Round up to zero places, #,### units) 17,326 units 81 82 83 84 90 3. For 20x2 the selling price per lamp will be $45.00. The desired operating income in 20x2 is $271,250. What 91 would sales in units have to be in 20x2 to reach the profit goal? 92 FC 385500 93 DESIRE 271250 94 95 96 97 98 99 100 101 FAQ Introduction 1 2 3 5 6 7 10 8 (5.03) 8 2 3 11 18 Get Add-ins Icons Screenshot Pictures Pivot Table Recommended Table Pivot Tables 3D Models My Add-ins Recoma Cha Tables illustrations Add-ins H131 X fx =(H108+H121+H126)/3990 56 B D E F G 42 43 On January 1, 20x2. Division S began Job 2407 for the Client, THE BIG CHILDREN STORE. The 44 job called for 4,000 customized lamps. The following set of transactions occurred from 45 January 5 until the job was completed: 46 55 5 Jan Purchased 4,150 figurines @ $9.75 per figurine 6-Jan Purchased 4.175 sets of electical components @ $1.25 per set. 57 7-Jan Purchased 4,000 lamp shades @ $6.60 per set 58 8 Jan 4,150 figurines were requisitioned. 59 9 Jan 4,150 sets of electrical components were requisitioned 68 17-Jan Payroll of 620 Direct Labor Hours @ $9.80 per hour. 28 Jan 3,990 lamp shades were requisitioned 70 30 Jan Payroll of 670 Direct Labor Hours @ $10.05 per hour. 71 30-Jan 3,990 lamps were completed and shipped. All materials requisitioned were 72 used or scrapped, and are a cost of normal processing. 81 Month End Overhead Information Actual Variable Manufacturing Overhead $ 1.212.60 83 Actual Fixed Manufacturing Overhead $ 40,873.45 84 85 94 69 82 Round to six places Cost of Direct Material Incurred in Manufacturing Job 2407 $71.984.000000 (1801) 95 96 97 98 107 108 109 110 111 120 121 122 123 124 125 126 Cost of Direct Labor incurred in Manufacturing Job 2407 $ 12.809 500000 (18.02) Cost of Manufacturing Overhead Applied to Job 2407 $ 9 6.004750000 (18.03) 10 11 18 Introduction FAQ 1 2 3 4 5 6 7 8 19 A1 X fx E B D 15 Division N has decided to develop its budget based upon projected sales of 33,000 lamps at 16 $48.00 per lamp 17 The company has requested that you prepare a master budget for the year. This budget is to be used 18 for planning and control of operations and should be composed of. 26 27 1. Production Budget 28 29 2. Materials Budget 30 38 3. Direct Labor Budget 39 40 4. Factory Overhead Budget 41 42 5. Selling and Administrative Budget 50 51 6. Cost of Goods Sold Budget 52 53 7. Budgeted Income Statement 54 62 8. Cash Budget 63 64 Notes for Budgeting: 65 66 74 The company wants to maintain the same number of units in the beginning and ending inventories of 75 work-in-process, and electrical parts while increasing the figurines inventory to 675 pieces and 76 increasing the finished goods by 23.00% 77 78 Complete the following budgets 86 87 1 Production Budget 88 89 Planned Sales 90 Desired Ending Inventory of Finished Goods (roundup to the next unit) 98 Total Needed 99 Less: Beginning Inventory 100 101 Total Production {7.01) 102 FAQ 1 2 Introduction 3 4 5 6 7 8 9 10 11 18 19 20 Fox B D E F G 2 Materials Budget (8.01) (8.02) Figurines Needed for Production Desired Ending Inventory Total Needed Less: Beginning Inventory Total Purchases Cost per piece Cost of Purchases (Round to two places, S##) (8.03) (8.04) {8.05) 3 4 5 6 7 16 17 18 19 20 29 30 31 32 33 42 43 14 45 16 55 56 57 58 59 38 59 70 71 72 81 82 83 84 Electrical Parts Needed for Production Desired Ending Inventory Total Needed Less: Beginning Inventory Total Purchases Cost per piece Cost of Purchases (Round to two places, $####) (8.06) (8.07) Lamp Shades - not inventoried they arrive from the shop next door Just-in-time. Needed for Production Desired Ending Inventory Total Needed Less: Beginning Inventory Total Purchases Cost per piece Cost of Purchases (Round to two places, st) (8.08) Introduction FAQ 1 3 4 5 6 8 9 10 11 18 19 20 21 Tvne here to cearch Cost per piece Cost of Purchases (Round to two places, S####) (8 3 Direct Labor Budget 72 81 82 83 84 85 94 95 96 97 98 107 108 109 110 111 120 121 Labor Cost Per Lamp Production Total Labor Cost (Round to two places, St#.### $ 1.00 {8.0 4 Factory Overhead Budget Variable Factory Overhead Variable Factory Overhead Cost Per Unit Number of Units to be Produced Total Variable Factory Overhead (Round to two places, S#) Fixed Factory Overhead 122 (8.10 123 124 125 126 127 Total Factory Overhead (Round to two places, $ ) (8.11 Predetermined Factory Overhead Rate based upon the budgeted total factory OH, divided by the budgeted number of units to be produced, and then rounded to seven places, $## ####) {8.12) 128 129 130 131 132 133 194 B D E F G H 4 5 5 Selling and Admin. Budget 6 7 Fixed Selling B Variable Selling (Round to two places, S####) 8 Fixed Administrative 9 Variable Administrative (Round to two places, $######) 20 Total Selling and Administrative (Round to two places, 5.) (9.01) 1 2 6 Cost of Goods Sold Budget - Assume FIFO (First In, First-Out) and overhead is applied based on the number of units to be produced. 2 3 Round dollars to seven places, 4 Cost of making one unit next year $###### 5 Material cost per unit 6 Labor Cost Per Lamp 6 Factory overhead per unit 7 8 Total cost of one unit (9.02) (Round to seven places, Sww.www) Round dollars to two places, 0 S#### 0 Beginning Inventory, Finished Goods (9.03) 1 Production Costs: Materials: 3 Figurines: Beginning Inventory 4 Purchased Available for Use 5 Ending Inventory of Figurines Figurines Used In Production {904) Electrical Parts Beginning Inventory Purchased 3 Available for Use 1 Ending Inventory of Electrical Parts Introduction FAQ 1 2 3 4 56 7 8 9 10 11 18 19 20 9 4 9 3 A1 X & fx - A B D E F G H Round dollars to seven places, $##.####### -4 55 36 16 47 18 Cost of making one unit next year Material cost per unit Labor Cost Per Lamp Factory overhead per unit Total cost of one unit (Round to seven places, S##.##*****##) {9. 19 Round dollars to two places. $##.## {9.0 (9.04 50 60 61 62 63 64 74 75 76 77 78 88 89 90 91 92 102 103 104 105 106 116 117 118 119 120 130 131 Beginning Inventory, Finished Goods Production Costs: Materials: Figurines: Beginning Inventory Purchased Available for Use Ending Inventory of Figurines Figurines Used In Production Electrical Parts Beginning Inventory Purchased Available for Use Ending Inventory of Electrical Parts Electrical Parts Used In Production Lamp Shades: Lamp Shades Used In Production Total Materials Labor Overhead Cost of Goods Available Less: Ending Inventory, Finished Goods Cost of Goods Sold {9.05) {9.06) {9.07) {9.08) {9.09) {9.10) {9.11) {9.12) Introduction FAQ 4 5 6 8 9 10 11 18 19 A1 D E K (10.01) 4 F G H 6 7 7 Budgeted Income Statement 8 9 Sales 16 Cost of Goods Sold 17 Gross Profit 18 Selling Expenses & Admin Expenses 19 Net Operating Income 20 27 28 29 30 31 Cash Budget 38 39 Assume actual cash receipts and disbursements will follow the pattern below: (Note: Receivables and 40 Payables of 12/31/x1 will have a cash impact in 20x2.) 41 42 1. 20.00% of sales for the year are made in November and December. Since our customers have 60 day terms 49 those funds will be collected be collected in January and February. 50 2. 81.00% of material purchases will be paid during the year, the remaining portion will be paid in Januay or February 51 3. All other manufacturing and operating costs are paid for when incurred. 52 4. The budgeted depreciation expense is equal to 0.6% of the fixed manufacturing, selling and administrative expenses. 53 5. Minimum Cash Balance needed for 20x2, 5190,000 60 I See The Light 61 Projected Cash Budget For the Year Ending December 31, 20x2 62 Round dollars to two places, SNW.44 63 64 71 72 73 74 75 82 83 Beginning Cash Balance Cash Inflows: Sales Collections: Account Receivable (Sales last year not collected) Sales made and collected in 20x2 Cash Available (1002) (1003) (1004) Cash Outflows Introduction FAQ 1 2 3 4 5 6 7 8 9 10 11 18 19 20 | 21 Type here to search o BE AutoSave On KF7131 - Excel e Search File Home Insert Page Layout Formulas Data Review View Help SmartArt Get Add-ins b? Shapes Icons 3D Models Pivottable Recommended Table Pivot Tables 38 Pictures Screenshot My Add-ins Recommended Charts Tables illustrations Add-ins X118 X D E H K 4ABC 60 61 62 G I See The Light Projected Cash Budget For the Year Ending December 31, 20x2 Round dollars to two places, S### Beginning Cash Balance Cash Inflows: Sales Collections Account Receivable (Sales last year not collected) Sales made and collected in 20x2 Cash Available (1002) (10.03) {10.04 63 64 71 72 73 74 75 B2 33 84 35 36 3 14 15 6 7 34 05 16 7 8 9 o (10.05) Cash Outflows, Purchases Accounts Payable (Purchases last year) Material purchases made and paid for in 20X2 Other Manufacturing Costs Direct Labor Total Manufacturing Overhead Selling and Administrative Less: Depreciation Total Cash Outflows {10.06) {10.07) Budgeted Cash Balance before financing Needed Minimum Balance (10.08) Amount to be borrowed (if any) (10.09) Budgeted Cash Balance (10.10) Introduction FAQ 1 2 3 3 4 5 on 6 7 8 9 10 11 18 19 20 21 B D E F G H 20x2 Cost Rounded to 7 Decimal Places 5 6 7 B 3 0 9 Variable Cost of making one unit next year used to calculate the Ending Inventory of Finished Goods Material cost per unit Labor Cost Per Lamp Variable Factory overhead per unit Total variable manufacturing cost of one unit (11.01) 3 4 5 10 Budgeted Operating Income Using Variable (Direct) Costing 20x2 Cost Rounded to 2 Decimal Places Sales (11.02) (11.03) Variable Cost of Goods Sold - Assume FIFO (First-In, First-Out) Beginning Inventory, Finished Goods (Variable Costing) Production Costs: Materials Figurines Electrical Parts Lamp Shades Labor Variable Overhead Total Variable Production Costs Cost of Goods Available For Sale Less Ending Inventory, Finished Goods (Variable Costing) Variable Cost of Goods Sold Variable Selling (Round to two places, www) Variable Administrative (Round to two places, S##.##) Total Variable Costs Contribution Margin Fixed Costs: Fixed Manufacturing Overhead Fixed Selling Fixed Administrative Total Fixed Introduction FAQ 1 2 3 4 5 (11.04) (11.05) (11.06) (11.07 (11.08) 6 7 8 9 10 11 18 19 20 21 A1 fr D E G H (11.02) (11.03) MA B 20 Variable Cost of Goods Sold - Assume FIFO (First-In, First-Out) 21 Beginning Inventory, Finished Goods (Variable Costing) 2 Production Costs: 4 Materials: 5 Figurines 6 Electrical Parts 7 Lamp Shades Labor: Variable Overhead: 1 Total Variable Production Costs 2 Cost of Goods Available For Sale Less Ending Inventory, Finished Goods (Variable Costing) Variable Cost of Goods Sold Variable Selling (Round to two places, S####) Variable Administrative (Round to two places, S### ##) Total Variable Costs Contribution Margin Fixed Costs: Fixed Manufacturing Overhead Fixed Selling Fixed Administrative Total Fixed Operating Income, Variable Costing (11.04) (11.05) (11.06) (11.07) (11.08) (11.09) Operating Income, Absorption Operating Income, Variable Costing Excess (Absorption Costing Operating Income - Variable Costing Operating Income) (11.10) Budgeted Fixed Overhead Budgoted Number of Units to be Produced Budgeted Fixed Cost Per Unit (Round to 7 decimals #. (11.11) Fixed Manufacturing Overhead in the Ending Inventory Fixed Manufacturing Overhead in the Beginning Inventory Increase (Fixed Manufacturing Overhead in the Ending Inventory-Fixed Manufacturing Overhead in the Beginning Inventory) (11.12) (11.13) (11.14) Introduction FAQ 1 2 3 4 5 5 6 7 ce 9 10 11 18 19 20 21 I See The Light Projected Income Statement For the Period Ending December 31, 20x1 $1,125,000.00 723,250.00 $ 401,750.00 Sales 25,000 lamps @ $45.00 Cost of Goods Sold @ $28.93 Gross Profit Selling Expenses: Fixed Variable (Commission per unit) @ $3.15 Administrative Expenses Total Selling and Administrative Expenses: Net Profit $ 23,000.00 78,750.00 $101,750.00 41,250.00 143,000.00 $ 258,750.00 I See The Light Projected Balance Sheet As of December 31, 20x1 34,710.00 67,500.00 Current Assets Cash Accounts Receivable Inventory Raw Material Figurines Electrical Sets Work in Process Finished Goods Total Current Assets 500 @ $9.20 500 @ $1.25 0 3000 @ $28.9250 4,600.00 625.00 86,775.00 $ 194,210.00 Fixed Assets Equipment Accumulated Depreciation Total Fixed Assets Total Assets $ 20,000.00 6,800.00 13,200.00 $207,410.00 $ 54,000.00 54,000.00 Current Liabilities Accounts Payable Total Liabilities Stockholder's Equity Common Stock Retained Earnings Total Stockholder's Equity Total Liabilities and Stockholder's Equity $ 12,000.00 141.410.00 153.410.00 $ 207 410.00 Introduction FAQ 2 3 5 6 7 8 9 10 11 18 19 20 21 File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer te A1 f B D E K 7 8 The projected cost of a lamp is calculated based upon the projected increases or decreases to 9 current costs. The present costs to manufacture one lamp are: 10 11 Figurines $92000000 per lamp 12 Electrical Sets 12500000 per lamp 16 Lamp Shade 6.0000000 per lamp 17 Dired Labor 2.2500000 per lamp (4 lampshr) 18 Variable Overhead 0.2250000 per lamp 19 Foed Overhead 10.0000000 per lamp (based on normal capacity of 25,000 lamps) 20 24 Cost per lamp $23.0280000 per lamp 25 26 Expected increases for 2012 27 When calculating projected increases round to SEVEN decimal places 500000000 28 1. Material Costs are expected to increase by 2.50% 34 2 Labor Costs are expected to increase by 5.50% 35 35 3. Variable Overhead is expected to increase by 600% 40 41 4. Fred Overhead is expected to increase to $300,000 42 43 5. Faed selling expenses are expected to be $35,000 in 2012 44 6. Variable selling expenses (measured on a per lamp basis) are expected to increase by 3.00% 7. Fored Administrative expenses are expected to increase by 510,000 The total administrative expenses for 200 were 541,205.00, when 23.500 units were sold Use the High-Low method to calculate the total fed administrative expense 8. Variable administrative expenses (measured on a perlamp basis) are expected to 60 increase by 6 50%. The total administrative expenses for 200 were 541.205.00, when 23.500 units were sold Use High-Low method to calculate the variable administrative pense per lamp 67 On the following schedule develop the following tures 58 1- 2010 Projected Variable Manufacturing Unit Cost of 2- 2012 Projected Variable Unt Cost per lamp 3- 2012 Projeledred Costs 75 Introduction FAQ 1 2 3 5 6 8 9 10 11 18 19 Type here to search o BE 8 9 Variable Manufacturing Unit Cost 20x1 Cost Projected 20x2 Cost Rounded to Percent 7 Decimal Places 10 Increase 11 Figurines 9.2 102.50% $9.4300000 {4.01) 12 Electrical Sets 1.25 102.50% $1.2812500 {4.02) 13 Lamp Shade 6 102.50% $6.1500000 {4.03) 18 Labor 2.25 105.50% $2.3737500 {4.04) 19 Variable Overhead 0.225 106.00% $0.2385000 {4.05) 20 21 Projected Variable Manufacturing Cost Per Unit $19.4735000 {4.06) 22 27 28 29 Total Variable Cost Per Unit 20x1 Cost Projected 20x2 Cost Rounded Percent to 7 Decimal Places 30 Increase 31 Variable Selling 3.15 103.00% 3.2445000 4.07) 36 Variable Administrative 20x1 0.0300000 106.50% {4.08) 37 Variable Administrative 20x2 0.03 106.50% 0.0319500 (409) 38 39 Projected Variable Manufacturing Unit Cost {4.06) 40 Projected Total Variable Cost Per Unit 22.7499500 (410) 45 46 47 48 Schedule of Fixed Costs 20x1 Cost Projected 20x2 Cost Rounded 49 Increase to 2 Decimal Places 54 Fixed Overhead $ 300,000.00 {4.11) 55 (normal capacity of lamps @_) 56 Fixed Selling 35,000.00 (4.12) 57 Fixed Administrative 20x1 (4.13) 58 Fixed Administrative 20x2 $ 50,500.00 (4.14) 63 Projected Total Fixed Costs $ 385,500.00 (4.15) 64 65 Introduction FAQ 1 2 3 4 5 | 6 | 7 8 9 10 11 {5.01) {5.02) 22 price per unit. 23 24 1. For 20x2 the selling price per lamp will be $45.00. What is the projected contribution margin and contribution 30 margin ratio for each lamp sold? 31 45 32 19.4735 33 3.2445 34 0.03195 40 41 42 43 44 Contribution Margin per unit (Round to seven places, S##.##### $22.2500500 50 51 Contribution Margin Ratio (Round to seven places,% is two of those places ##.##### 49.44456% 52 53 54 2. For 20x2 the selling price per lamp will be $45.00. How many lamps must be sold to breakeven? 60 61 FIXEDC 385500 62 63 64 70 71 72 73 74 80 Breakeven sales in units (Round up to zero places, #,### units) 17,326 units 81 82 83 84 90 3. For 20x2 the selling price per lamp will be $45.00. The desired operating income in 20x2 is $271,250. What 91 would sales in units have to be in 20x2 to reach the profit goal? 92 FC 385500 93 DESIRE 271250 94 95 96 97 98 99 100 101 FAQ Introduction 1 2 3 5 6 7 10 8 (5.03) 8 2 3 11 18 Get Add-ins Icons Screenshot Pictures Pivot Table Recommended Table Pivot Tables 3D Models My Add-ins Recoma Cha Tables illustrations Add-ins H131 X fx =(H108+H121+H126)/3990 56 B D E F G 42 43 On January 1, 20x2. Division S began Job 2407 for the Client, THE BIG CHILDREN STORE. The 44 job called for 4,000 customized lamps. The following set of transactions occurred from 45 January 5 until the job was completed: 46 55 5 Jan Purchased 4,150 figurines @ $9.75 per figurine 6-Jan Purchased 4.175 sets of electical components @ $1.25 per set. 57 7-Jan Purchased 4,000 lamp shades @ $6.60 per set 58 8 Jan 4,150 figurines were requisitioned. 59 9 Jan 4,150 sets of electrical components were requisitioned 68 17-Jan Payroll of 620 Direct Labor Hours @ $9.80 per hour. 28 Jan 3,990 lamp shades were requisitioned 70 30 Jan Payroll of 670 Direct Labor Hours @ $10.05 per hour. 71 30-Jan 3,990 lamps were completed and shipped. All materials requisitioned were 72 used or scrapped, and are a cost of normal processing. 81 Month End Overhead Information Actual Variable Manufacturing Overhead $ 1.212.60 83 Actual Fixed Manufacturing Overhead $ 40,873.45 84 85 94 69 82 Round to six places Cost of Direct Material Incurred in Manufacturing Job 2407 $71.984.000000 (1801) 95 96 97 98 107 108 109 110 111 120 121 122 123 124 125 126 Cost of Direct Labor incurred in Manufacturing Job 2407 $ 12.809 500000 (18.02) Cost of Manufacturing Overhead Applied to Job 2407 $ 9 6.004750000 (18.03) 10 11 18 Introduction FAQ 1 2 3 4 5 6 7 8 19
