Hi! I am not sure how to do this or if what I did was right. The last picture is of hints but I am not sure how to use them. Any help is appreciated! Thank you!
window Help AutoSave OFF 2 - AS7685.xls Home Compatibilid Insert Draw Page Layout Formulas Data Review View Tell m MS Sans Serit 12 Paste General 191 $ F83 % fix A B E 6 13 14 Division N has decided to develop budget based upon projected sales of 43,000 lamps at 15 $49.00 per lamp 16. The company has requested that you prepare a master budget for the year. This budget is to be used 17 for planning and control of operations and should be composed of: 24 25 1. Production Budget 25 27 2. Materials Budget 28 35 3. Direct Labor Budget 36 37 4. Factory Overhead Budget 38 39 5. Selling and Administrative Budget 40 47 6. Cost of Goods Sold Budget 48 49 7. Budgeted Income Statement 57 8. Cash Budget 58 59 Notes for Budgeting 60 51 68 The company wants to maintain the same number of units in the beginning and ending inventories of 69 work-in-process, and electrical parts while increasing the inventory of Lamp Kits to 550 pieces and 70 decreasing the finished goods by 20% 71 22. Complete the following budgets 79 80 1 Production Budget 81 82 Planned Sales 83 Desired Ending Inventory of Finished Goods 90 Total Needed 91 Less: Beginning inventory 92 93 Total Production 41000 4400 TOT 102 03 2.400 17.01 2 Ready G 2 Materials Budget 3 4 5 6 7 9 10 1 42.400 units 12 | Lamp Kits Needed for Production Desired Ending Inventory Total Needed Less: Beginning Inventory Total Purchases Cost per piece Cost of Purchases (Round to two places, S##) 3 Direct Labor Budget (801) (8.02) (8.03) {8.04) 3 5 13 -5 -6 -7 -8 -9 51 2 3 (8.05) {8.06) Labor Cost Per Lamp Production Total Labor Cost (Round to two places, S.) (8.07) 4 Factory Overhead Budget (8.08) Vanable Factory Overhead: Variable Factory Overhead Cost Per Unit Number of Units to be produced Total Variable Factory Overhead (Round to two places, $#### Fixed Factory Overhead 5 -7 8 -9 0 1 2 3 4 5 6 7 8 9 0 Total Factory Overhead (Round to two places, S#.#N) (8.09) (8.10) (8.11) G H 4 Factory Overhead Budget Overhead Allocation rate based on 1. Number of Units Total Factory Overhead / Number of Units (Round to two places. S##.##) 5 Cost of making one unit next year Cost of one Lamp KI Labor Cost Per Lamp Factory overhead per unit 19.01) Total cost of one unit (Round to two places, $89.10) 19.02) 6 Selling and Admin Budget 19.03) Foxed Selling Variable Selling (Round to two places, Sald.) Fixed Administrative Variable Administrative (Round to two places, S.) Total Selling and Administrative (Round to two places, S.) 19.04) WALE Goods 7 Sold 19.05) 19.06) Round dollars to Two places, $ Budant- Beginning inventory. Finished Goods Production Costs: Materials: Lamp Kits: Beginning inventory Purchased Available for Use Ending Inventory of Lamp Kits Larro Kits Used In Production 19.07) Tot Materials Labor Overhead Cost of Goods Available Less Ending Inventory. Finished Goods Cost of Goods Sold 19.08) 19.09) 19.10) 19.11) 19.12) 19.13) 19.14) 2 D Ready D 7 Budated Income Statement Sales Cost of Goods Sold Gross Prost Selling Expenses & Admin. Expenses Net Income (10.01) 8 Cash Budget Assume actual cash receipts and disbursements will follow the pattem below. (Note: Receivables and Payables of 12/31x1 will have a cash Impact in 2012.) 1. 18.00% of sales for the year are made in November and December. Since our customers have 60 day torna those funds will be collected be collected in January and February 2.86.00% of mated al purchases will be paid during the year, the remaining portion will be paid in Januay of February or 3. All other manufacturing and operating costs are paid for when incurred, 04. The budgeted depreciation expense is equal to 0.6% of the fred manufacturing, selling and administrative expenses 1.5. Minimum Cash Balance needed for 20x2, $170,000 5 I See The Light Projected Cash Budget 7 For the Year Ending December 31, 2012 18 Round dollar to two places 55 56 17 Beginning Cash Balance Cash Infows Sales Collections: Account Receivable (Sales last year not collected) Bales made and collected in 2012 Cash Available (10.02) (10.01) (10.04) Cash Outflows Purchases Accounts Payable Purchases last year Purchases made and paid for in 20x2 Other Manufacturing Costs Direct Labor Total Manufacturing Overhead Selling and Administrative Loss: Depreciation Total Cash Outflows 03 64 05 09 70 71 72 73 77 78 TO 80 31 82 B3 84 (10.05) (10.05) 110.07) Budgeted Cash Balance before financing Needed Minimum Balance 110.051 Amount to be borrowed of any) 1938 SODES Budgeted Cash Balance [10.09) (10.10) text=course_entr... Home - Blackboar... U CaneLink WebAssign sign in Ebooks - Webread... OneDrive Student Sup Question 7.01. For this question, you have to start with the planned sales (given at the top) and the desired ending inventory. They tell you in the fact pattern that they want to decrease finished goods by 20%. If you look at the Balance Sheet (sheet #2) you will see that they had 3,000 items in finished goods last year. They want to decrease that number by 20% which would be 600. Therefore, the desired ending inventory is 2,400 (3,000 - 600). 8.02 is given on sheet #7. It's the desired ending inventory of lamp kits. 9.01 is just the total factory overhead from 8.11 divided by the planned production For 9.03, you need 3 things to make a unit: Direct Materials (Lamp Kits), Direct Labor and Manufacturing Overhead. You already have all 3 numbers: DM: From 8.05 DL: From 8.07 MO: From 9.01 9.08 is the ending lamp kits from 8.02 times the cost per lamp kit from 8.05. 9.09 is asking you about direct materials (lamp kits) USED in production. You need to follow the formula that's there: Beginning inventory = From Balance Sheet $8,000 Plus: Purchased From 8.06 Available for Sale Less: Ending Inventory of Lamp Kits = From (8.02 X 8.05) = Lamps Kits Used in Production For 9.12, Cost of Goods available is your beginning finished goods inventory (9.07) + your current costs (DM, DL and MO). DM, DL and MO are 9.09, 9.10 and 9.11 9.13 is the units left in finished goods inventory (desired ending finished goods from sheet #7) X cost per unit from 9.03. For 10.05, take your purchases from sheet #8 (8.06) and then multiply it by the percentage they gave you on sheet #10 in the additional information (#2 of the additional information), For 10.06, (depreciation) you need to multiply.006 times the answer from 4.11 (total fixed costs). For 10.07. you need to add accounts payable (from Balance Sheet) plus 10.05 + DL + MO + Selling and Admin and then subtract the depreciation expense. window Help AutoSave OFF 2 - AS7685.xls Home Compatibilid Insert Draw Page Layout Formulas Data Review View Tell m MS Sans Serit 12 Paste General 191 $ F83 % fix A B E 6 13 14 Division N has decided to develop budget based upon projected sales of 43,000 lamps at 15 $49.00 per lamp 16. The company has requested that you prepare a master budget for the year. This budget is to be used 17 for planning and control of operations and should be composed of: 24 25 1. Production Budget 25 27 2. Materials Budget 28 35 3. Direct Labor Budget 36 37 4. Factory Overhead Budget 38 39 5. Selling and Administrative Budget 40 47 6. Cost of Goods Sold Budget 48 49 7. Budgeted Income Statement 57 8. Cash Budget 58 59 Notes for Budgeting 60 51 68 The company wants to maintain the same number of units in the beginning and ending inventories of 69 work-in-process, and electrical parts while increasing the inventory of Lamp Kits to 550 pieces and 70 decreasing the finished goods by 20% 71 22. Complete the following budgets 79 80 1 Production Budget 81 82 Planned Sales 83 Desired Ending Inventory of Finished Goods 90 Total Needed 91 Less: Beginning inventory 92 93 Total Production 41000 4400 TOT 102 03 2.400 17.01 2 Ready G 2 Materials Budget 3 4 5 6 7 9 10 1 42.400 units 12 | Lamp Kits Needed for Production Desired Ending Inventory Total Needed Less: Beginning Inventory Total Purchases Cost per piece Cost of Purchases (Round to two places, S##) 3 Direct Labor Budget (801) (8.02) (8.03) {8.04) 3 5 13 -5 -6 -7 -8 -9 51 2 3 (8.05) {8.06) Labor Cost Per Lamp Production Total Labor Cost (Round to two places, S.) (8.07) 4 Factory Overhead Budget (8.08) Vanable Factory Overhead: Variable Factory Overhead Cost Per Unit Number of Units to be produced Total Variable Factory Overhead (Round to two places, $#### Fixed Factory Overhead 5 -7 8 -9 0 1 2 3 4 5 6 7 8 9 0 Total Factory Overhead (Round to two places, S#.#N) (8.09) (8.10) (8.11) G H 4 Factory Overhead Budget Overhead Allocation rate based on 1. Number of Units Total Factory Overhead / Number of Units (Round to two places. S##.##) 5 Cost of making one unit next year Cost of one Lamp KI Labor Cost Per Lamp Factory overhead per unit 19.01) Total cost of one unit (Round to two places, $89.10) 19.02) 6 Selling and Admin Budget 19.03) Foxed Selling Variable Selling (Round to two places, Sald.) Fixed Administrative Variable Administrative (Round to two places, S.) Total Selling and Administrative (Round to two places, S.) 19.04) WALE Goods 7 Sold 19.05) 19.06) Round dollars to Two places, $ Budant- Beginning inventory. Finished Goods Production Costs: Materials: Lamp Kits: Beginning inventory Purchased Available for Use Ending Inventory of Lamp Kits Larro Kits Used In Production 19.07) Tot Materials Labor Overhead Cost of Goods Available Less Ending Inventory. Finished Goods Cost of Goods Sold 19.08) 19.09) 19.10) 19.11) 19.12) 19.13) 19.14) 2 D Ready D 7 Budated Income Statement Sales Cost of Goods Sold Gross Prost Selling Expenses & Admin. Expenses Net Income (10.01) 8 Cash Budget Assume actual cash receipts and disbursements will follow the pattem below. (Note: Receivables and Payables of 12/31x1 will have a cash Impact in 2012.) 1. 18.00% of sales for the year are made in November and December. Since our customers have 60 day torna those funds will be collected be collected in January and February 2.86.00% of mated al purchases will be paid during the year, the remaining portion will be paid in Januay of February or 3. All other manufacturing and operating costs are paid for when incurred, 04. The budgeted depreciation expense is equal to 0.6% of the fred manufacturing, selling and administrative expenses 1.5. Minimum Cash Balance needed for 20x2, $170,000 5 I See The Light Projected Cash Budget 7 For the Year Ending December 31, 2012 18 Round dollar to two places 55 56 17 Beginning Cash Balance Cash Infows Sales Collections: Account Receivable (Sales last year not collected) Bales made and collected in 2012 Cash Available (10.02) (10.01) (10.04) Cash Outflows Purchases Accounts Payable Purchases last year Purchases made and paid for in 20x2 Other Manufacturing Costs Direct Labor Total Manufacturing Overhead Selling and Administrative Loss: Depreciation Total Cash Outflows 03 64 05 09 70 71 72 73 77 78 TO 80 31 82 B3 84 (10.05) (10.05) 110.07) Budgeted Cash Balance before financing Needed Minimum Balance 110.051 Amount to be borrowed of any) 1938 SODES Budgeted Cash Balance [10.09) (10.10) text=course_entr... Home - Blackboar... U CaneLink WebAssign sign in Ebooks - Webread... OneDrive Student Sup Question 7.01. For this question, you have to start with the planned sales (given at the top) and the desired ending inventory. They tell you in the fact pattern that they want to decrease finished goods by 20%. If you look at the Balance Sheet (sheet #2) you will see that they had 3,000 items in finished goods last year. They want to decrease that number by 20% which would be 600. Therefore, the desired ending inventory is 2,400 (3,000 - 600). 8.02 is given on sheet #7. It's the desired ending inventory of lamp kits. 9.01 is just the total factory overhead from 8.11 divided by the planned production For 9.03, you need 3 things to make a unit: Direct Materials (Lamp Kits), Direct Labor and Manufacturing Overhead. You already have all 3 numbers: DM: From 8.05 DL: From 8.07 MO: From 9.01 9.08 is the ending lamp kits from 8.02 times the cost per lamp kit from 8.05. 9.09 is asking you about direct materials (lamp kits) USED in production. You need to follow the formula that's there: Beginning inventory = From Balance Sheet $8,000 Plus: Purchased From 8.06 Available for Sale Less: Ending Inventory of Lamp Kits = From (8.02 X 8.05) = Lamps Kits Used in Production For 9.12, Cost of Goods available is your beginning finished goods inventory (9.07) + your current costs (DM, DL and MO). DM, DL and MO are 9.09, 9.10 and 9.11 9.13 is the units left in finished goods inventory (desired ending finished goods from sheet #7) X cost per unit from 9.03. For 10.05, take your purchases from sheet #8 (8.06) and then multiply it by the percentage they gave you on sheet #10 in the additional information (#2 of the additional information), For 10.06, (depreciation) you need to multiply.006 times the answer from 4.11 (total fixed costs). For 10.07. you need to add accounts payable (from Balance Sheet) plus 10.05 + DL + MO + Selling and Admin and then subtract the depreciation expense
