Your company is competing for business in the toy robot industry Prepare a sales, production, direct materials, and direct labor budget based on the following assumptions Assumptions Q1 Q2 Q4 1. Sales Projections (Units) 10,000 15,000 17,000 Q4 25,000 7. Material Needs 1 Lb. per unit of plastic 8. Material Costs $2 per pound of plastic 2. Sales Price per Unit $25.00 9. Material Desired Ending Inventory 10% of next months Units lbs. 10. Material Beginning Inventory The same as the previous quarters ending inventory. lbs. 5. Desired Ending Inventory 10% of next quarters sales 6. Beginning Inventory The same as the prior quarters ending inventory. 11. Labor Costs $20 per hour 12. Time to produce one mouse 0.25 hour NOTE: To protect the integrity of the numbers within this Workbook, each spreadsheet has been protected so that you can only access the yellow cells. If you wish to utilize the ability to create formulas within Excel... Go to the File Tab, Select Info, Below "Protect Workbook", Select "Unprotect" after each spreadsheet name. Units Sales Price Total Sales Sales Budget Expected Sales in Units Q1 Q2 Q3 Q4 $0 $0 $0 $0 TechGadgets Co. sells their Robot Kits for $25 each. In 20XX the company expects to sell 10,000 units in Quarter 1; 15,000 in Quarter 2; 17,000 in Quarter 3; and 25,000 in Quarter 4; with no change in the Selling Price. Production Budget Expected number of goods to be produced to meet the sales projection What you need: Sales Forecast data from the Sales Budget Ending Inventory - inventory left over from the previous time period Beginning Inventory - what is left over from the previous reporting period TechGadgets Co. projects the following unit sales in 2023 the company expects to sell 10,000 units in Quarter 1, 15,000 in Quarter 2, 17,000 in Quarter 3 and 25,000 in Quarter 4. TechGadgets Co. desires an ending inventory of 10% of next quarters planned units and its beginning inventory is equal to the previous quarters ending inventory. Because this is a new product, we will have a beginning inventory of zero in quarter 1, and Quarter 4's desired ending inventory will be provided. Q1 Q2 Q3 Q4 Planned Sales (in units) Desired Ending Inventory (in units) Needed Inventory (in units) Less Beginning Inventory Units to be Produced 0 2,000 2,000 2,000 To meet the sales projection, how many Robot Kits do you have to produce? Direct Material Budget Expected material that will be needed for production What you need: Units to be produced - taken from Production Budget Materials needed to produce each unit (an assumption) Desired ending inventory of materials (an assumption) Beginning inventory of materials (an assumption) Unit price per lb. (an assumption) TechGadgets Co. created a Production Budget to project the Production Needs in Units for each quarter to make their Robot Kits. Use the production needs in units found in the Production Budget to complete the Direct Materials Budget. Each Unit requires 1 pounds of plastic which cost $2.00 per 1 pound. TechGadgets Co. desires a material ending inventory of 10% of next quarters production needs and its material beginning inventory is equal to the previous quarters ending inventory. Because this is a new product, we will have a beginning inventory of zero in quarter 1, and Quarter 4's desired ending inventory will be provided. Q1 Q2 Q3 Q4 Planned Production Needs (in units) Materials needed to produce a unit (assumption) Production Needs (lbs) Desired Ending Inventory of materials (lbs) Total Needs (lbs) 2,000 2,000 Less: Beginning Inventory of materials (lbs) Needed Materials (lbs) 2,000 Cost of materials to produce one unit Cost of Materials to Produce Required Units based on Sales Projection $ $ - $ - $ What will it cost you in Q1 for material? Direct Labor Budget Expected costs to produce required products What you need: Units to be produced - taken from Production Budget Direct labor hours required to produce one unit (an assumption) Cost of labor per hour (an assumption) Q1 Q2 Q3 Q4 Required Units to be Produced (taken from Production Budget) Direct Labor Hours required to produce one unit Production Needs Cost of Labor per hour Total Cost of Labor to Produce Required Units to Meet Sales Budget What is it going to cost you in labor to produce the number of Robot Kits required to meet revenue projections? TechGadgets Co. created a Production Budget to project the Production Needs in Units for each quarter to make their Robot Kits. Use the production needs in units found in the Production Budget to complete the Direct Labor Budget. In order to produce their Robot Kits, TechGadgets Co. will require Labor Costs of $20 per hour and needs 0.25 hours to produce one Unit. Projected Sales (Revenue) Cost of Goods Sold - Material Costs - Labor Costs Gross Profit Calculation Projected Revenue - Projected Cost of Goods Sold Gross Profit Percentage -(Revenue - COGS)/ Revenue *100 Is it Worth It? $625,000 TechGadgets Co. created a Master Budget to project the needs in units and costs associated with the manufacturing of their Robot Kits. Tech Gadgets Co. wants to look at whether it will be a favorable or unfavorable year. To evaluate this, TechGadgets Co. will need to utilize the Master Budget to create an Income Statement. (For the purpose of this assignment we will only create the top half of the Incomes Statement that represents to Sales, Cost of Goods Sold, and the Gross Profit.) Q1 $250,000 Q2 Q3 Q4 $375,000 $425,000 $250,000 $375,000 $425,000 $625,000 100% 100% 100% 100%