Apologies for the long question!

L M N O P Q R S at 10 at A1 fx Part 2 C D E F G H I J K 7 The budget director for Bird House Unlimited, Inc., has gathered the following data for use in developing the budgeted income statement for January 2020. Estimated sales for January Bird House 34,000 units $42 per unit Bird Feeder 25,000 units $70 per unit Direct materials Estimated inventories at January 1(beginning) Wood 2600 feet Plastic 3200 pounds Estimated inventories at January 31st(ending) Wood 3500 feet Plastic 2800 pounds Direct materials used in production: In the manufacture of a Bird House: Wood Plastic 0.80 feet per unit of product 0.50 pounds per unit of product In the manufacture of a Bird Feeder: Wood Plastic 1.20 feet per unit of product 0.75 pounds per unit of product Estimated cost of direct materials Wood Plastic $6.50 per feet $0.90 per pound Work in process-given in total of the 2 products Estimated inventories at January 1(beginning) Estimated inventories at January 31st(ending) $27,000 $32,400 36 37 38 Finished Goods Estimated inventories at January 1(beginning) Bird House Project 3 Part 1 Part 2 Budget Template 4900 units at $25 per unit Ready 0 - A1 X fx Part 2 . D E G H I J K L M N O P Q R S 31 32 33 34 35 Work in process-given in total of the 2 products Estimated inventories at January 1(beginning) Estimated inventories at January 31st(ending) $27,000 $32,400 37 Finished Goods Estimated inventories at January 1(beginning) Bird House Bird Feeder 38 39 at 4900 units 2,500 units $25 per unit $35 per unit 40 42 Estimated inventories at January 31st(ending) Bird House Bird Feeder 5300 units 2,100 units $24 per unit $36 per unit Direct Labor Requirements: Bird House at 46 47 48 49 Fabrication Department Assembly Department 0.25 hour 0.3 hour $14 per hour $10 per hour at Bird Feeder Fabrication Department Assembly Department 0.45 hour 0.35 hour $14 per hour $10 per hour 53 54 55 56 Estimated Manufacturing Overhead Costs for January Indirect Factory Wages $550,000 Depreciation of Plant and Equipment 165,000 Power and Light 42.000 Insurance and property tax 15,400 60 Estimated operating expenses for January Sales salaries expense Advertising expense Office salaries expense Project 3 Part 1 Part 2 Budget Template || $600,000 148,600 214,800 X V for Part 2 B C D E 2,100 units F G H $36 per unit I J K L M N O P Q R S T 43 Bird Feeder at 44 45 46 Direct Labor Requirements: Bird House Fabrication Department Assembly Department 0.25 hour 0.3 hour $14 per hour $10 per hour Bird Feeder Fabrication Department Assembly Department 0.45 hour 0.35 hour at at $14 per hour $10 per hour Estimated Manufacturing Overhead Costs for January Indirect Factory Wages $550,000 Depreciation of Plant and Equipment 165,000 Power and Light 42,000 Insurance and property tax 15,400 Estimated operating expenses for January Sales salaries expense Advertising expense Office salaries expense Depreciation expense-office equipment Telephone expense-selling Telephone expense-administrative Travel expense-selling Office supplies expense Miscellaneous Administrative expense $600,000 148,600 214,800 4,900 5,200 1,700 39,200 3,500 5,000 69 71 72 73 Estimated other income and expense for January Interest Revenue Interest Expense $14,000 11,000 74 32% Estimated tax rate Project 3 Part 1 Part 2 Budget Template Ready ! @ - -_ + 10 File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments (i) PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing SIGN IN TO OFFICE It looks like your stored credentials are out of date. Please sign in as vilcapo51@yahoo.com so we can verify your subscription. Sign In F81 - X fic M N O P Q R S T A B C D E F G H I J K L 1 Prepare the following budgets using cell references and formulas in the shaded cells below. You 2 should not have any numbers typed into the budgets below, since the last question is a what if analysis. 3 I know that my budget format January be different than the textbook, but conceptually it is the same. 4 1. Prepare a sales budget for January 5 2. Prepare a production budget for January. 6 3. Prepare a supporting schedule to determine required material for production and a direct materials purchase budget for January 7 4. Prepare a direct labor cost budget for January. 8 5. Prepare a factory overhead cost budget for January 9 6. Prepare a cost of goods sold budget for January. 10 7. Prepare a selling and administrative budget for January, set up based on categorization of expenses into selling and administrative costs. 11 8. Prepare a budgeted income statement for January. 12 9. What if the company decides to start a new advertising campaign 13 that will increase the total advertising expense to a total of $180,000 and the estimated sales 14 of bird house would increase to 36,000 units and Bird Feeders would increase to 29,000 units. 15 Make these changes on the Part 2 worksheet and if you have linked your data correctly to these budgets the 16 budgets should update immediately. What budgets changed? What would be the new budgeted income? 17 Would you recommend this advertising campaign? Why? 18 19 Enter the appropriate cell references or formulas in the shaded cells below. 20 1. Bird House Unlimited, Inc. Sales Budget For the Month Ending January 31, 2020 Unit Sales Volume Unit Selling Price Total Sales Bird House Bird Feeder Total revenue from sales 50 V1 G H I J K L M N O P Q R S T D E F 18 19 Enter the appropriate cell references or formulas in the shaded cells below. 20 1. Bird House Unlimited, Inc. Sales Budget For the Month Ending January 31, 2020 Unit Sales Volume Unit Selling Price Total Sales Bird House Bird Feeder Total revenue from sales 29 DU T 32 2. 33 Bird House Unlimited, Inc. Production Budget For the Month Ending January 31, 2020 Units Bird House Bird Feeder Expected units to be sold Plus desired inventory, January 31, 2020 Total Less estimated inventory, January 1, 2020 Total units to be produced 43 44 45 47 3. 48 49 50 Bird House Unlimited, Inc. Schedule to determine the required material for production For the Month Ending January 31, 2020 Part 2 Budget Template Project 3 Part 1 A B F G H I J K L M N O P Q R S T Bird House Unlimited, Inc. Schedule to determine the required material for production For the Month Ending January 31, 2020 Bird House Bird Feeder Required units for production: Wood required per unit(feet) Wood required for production (feet) Required units for production: Plastic required per unit(Pounds) Plastic required for production (pounds) Plastic Total Bird House Unlimited, Inc. Direct Materials Purchases Budget For the Month Ending January 31, 2020 Wood Required units for production: Bird House Bird Feeder Plus desired ending inventory, January 31, 2020 Total Less estimated beginning inventory, January 1, 2020 Total units to be purchased Cost per material Total direct materials to be purchased 70 72 73 74 75 4. 76 77 Bird House Unlimited, Inc. Direct Labor Cost Budget For the Month Ending January 31, 2020 Budget Template Project 3 Part 1 Part 2 G H I K L M N O P Q R S T AB c o E F Bird House Unlimited, Inc. Direct Labor Cost Budget For the Month Ending January 31, 2020 Fabrication Department Hours required for production: Bird House Bird Feeder Total Hourly rate Total direct labor cost Assembly Department Total 85 86 88 5. Bird House Unlimited, Inc. Manufacturing Overhead Cost Budget For the Month Ending January 31, 2020 Indirect factory wages Depreciation of plant and equipment Power and light 96 Insurance and property tax Total 98 99 6. Hint: Do not forget you have 2 products and 2 direct materials, so you will need to combine this information below. 100 TUT 102 Bird House Unlimited, Inc. 103 Cost of Goods Sold Budget For the Month Ending January 31, 2020 105 106 Direct materials: Project 3 Part 1 Part 2 Budget Template 104 V1 X & fic L M N O P Q R S T AB E F I K 98 99 6. Hint: Do not forget you have 2 products and 2 direct materials, so you will need to combine this information below. 100 TUT 102 103 104 105 106 Bird House Unlimited, Inc. Cost of Goods Sold Budget For the Month Ending January 31, 2020 107 108 109 110 111 112 113 114 115 116 117 118 Direct materials: Direct materials inventory, January 1, 2020 Direct materials purchases Cost of direct materials available for use Less: Direct materials inventory, January 31, 2020 Cost of direct materials placed in production Direct labor Manufacturing overhead Total manufacturing costs Work in process inventory, January 1, 2020 Subtotal Less work in process inventory, January 31, 2020 Cost of goods manufactured Finished goods inventory, January 1, 2020 Cost of finished goods available for sale Less finished goods inventory, January 31, 2020 Cost of goods sold 119 120 121 122 123 124 125 7. 126 127 Bird House Unlimited, Inc. Selling and Administrative Expenses Budget For the Month Ending January 31, 2020 128 129 Selling expenses: Project 3 Part 1 Part 2 Budget Template + Ready @ -_ + 100 V1 AB F G H I J K L M N O P Q R S T 119 120 D E Finished goods inventory, January 1, 2020 Cost of finished goods available for sale Less finished goods inventory, January 31, 2020 Cost of goods sold 121 122 123 124 125 7. 126 127 128 129 130 131 132 133 134 135 136 137 138 Bird House Unlimited, Inc. Selling and Administrative Expenses Budget For the Month Ending January 31, 2020 Selling expenses: Sales salaries expense Advertising expense Telephone expense - selling Travel expense - selling Total selling expenses Administrative expenses: Office salaries expense Depreciation expense - office equipment Telephone expense - administrative Office supplies expense Miscellaneous administrative expense Total administrative expenses Total operating expenses 139 140 141 142 144 145 8. 146 147 148 149 150 Bird House Unlimited, Inc. Budgeted Income Statement For the Month Ending January 31, 2020 Sales Revenue Project 3 Part 1 Part 2 Budget Template Ready 0 1 - - + 10 vi - X fx C D E F G H I K L M N O P Q R S T 1 145 8. 146 147 148 149 150 151 152 Bird House Unlimited, Inc. Budgeted Income Statement For the Month Ending January 31, 2020 153 154 155 156 157 158 159 160 161 162 163 Sales Revenue Less: Cost of goods sold Gross Margin Operating expenses: Selling expenses Administrative expenses Total operating expenses Income from operations Other income: Interest revenue Other expenses: Interest expense Income before income tax Income tax expense Net income 164 165 166 167 168 9. What if the company decides to start a new advertising campaign 169 that will increase the total advertising expense to a total of $180,000 and the estimated sales 170 of bird house would increase to 36,000 units and Bird Feeders would increase to 29,000 units. 171 Make these changes on the Part 2 worksheet and if you have linked your data correctly to these budgets the 172 budgets should update immediately. What budgets changed? What would be the new budgeted income? 173 Would you recommend this advertising campaign? Why? 174 Please make sure you change your data back to the original information before you submit. 175 176 177 Project 3 Part 1 Part 2 Budget Template Ready 9 0 - + 10