Question: Please solve using excel and following table format Problem 11-18 (Static) Dundas Bike Components Inc. of Wheelville, Illinois, manufactures bicycle wheels in two different sizes

Please solve using excel and following table format

Please solve using excel and following table format Problem 11-18 (Static) Dundas

Problem 11-18 (Static) Dundas Bike Components Inc. of Wheelville, Illinois, manufactures bicycle wheels in two different sizes for the Big Bike Co. assembly plant located across town. David Dundas, the firm's owner-manager, has just received Big Bike's order for the next six months. Feb. 20-Inch Wheels 24-Inch Wheels Nov. 1,000 units 500 units Dec. 900 500 Jan. 600 300 700 500 Mar. 1,100 400 Apr. 1,100 600 Click here for the Excel Data File Currently, Dundas employs 28 full-time, employees, each of whom can produce 50 wheels per month in addition to their other tasks. Because labor is in short supply in the Wheelville area, David would like to develop one pure level-output plan. There is no inventory of finished wheels on hand at present, but David would like to have 300 on hand at the end of April. Big Bike will tolerate back orders of up to 200 units per month. (Round your Average, Overtime, Inventory and Total values to 1 decimal place. Negative amounts should be indicated by a minus sign. Leave no cells blank - be certain to enter "0" wherever required.) Regular Overtime Part-time Subcontract $ 50 Hiring $ 75 Layoff NA Inventory NA Back order $ 300 $ 400 $1.00 $ 6.00 In the answer table below, develop a level plan using overtime without hiring additional employees. The overtime should be the same in every month except the last month where the overtime should be zero. (Enter all answers as whole numbers. Negative amounts should be indicated by a minus sign. Leave no cells blank - be certain to enter "O" wherever required.) Option 1: Maintain 28 employees & use overtime in equal amounts in Months 1-5: Nov. Dec. Jan. Feb. Mar. April Total 1,400 1,400 1,200 1,400 1,500 1,400 1,700 1,4001 1,500 1,400 0 17 0 900 1,400 0 17 8,200 8.400 0 0 0 0 0 17 17 17 17 0 0 0 0 0 517 -83 17 217 -83 -284 301 Forecast Output Reg. Output Part Time Output Overtime Output Subcontract Output Minus Forecast Beginning Inventory Ending Inventory Average Inventory Backlog Cost Reg. @ 50 Cost Part Time Cost Overtime @ 75 Cost Hire/Layoff Cost Inventory @ 1 Cost Backorders @6 Total Cost 0

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Accounting Questions!