question information is attached below. please show formulas too. Have to find the quarterly MPS and changeover and inventory holding costs. Answer in the excel sheets

Chapter 5 Master Production Scheduling Sales Forecast (Standard Boats for Each Series) Product Lines 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter FunRay series 8,000 9,000 6,000 6,000 SunRay series 4,000 5,000 2,000 2,000 StingRay series 9,000 10,000 6,000 7,000 Total 21,000 24,000 14,000 15,000 The sales forecast is stated in terms of "standard boats," reflecting total sales volume for each of the firm's three major product lines. Another item of information supplied by the marketing department is the target ending inventory position for each product line. The marketing department would like the production manager to plan on having the following number of standard boats on hand at the end of each quarter of next year: Quarterly Target Ending Inventory Product Line (in Standard Boats) FunRay series 3,000 boats SunRay series 1,000 boats StingRay series 3,000 boats The inventory position for each product is: Current Inventory Level Product Line (in Standard Boats) FunRay series 15,000 boats SunRay series 3,000 boats StingRay series 5,000 boats The master production schedule is to specify the number of boats (in standard units) to be produced for each product line in each quarter of next year on the firm's single assembly line. The assembly line can produce up to 15,000 standard boats per quarter (250 boats per day during the 60 days in a quarter). Two additional factors are taken into account by the production manager in preparing the master production schedule: the assembly line changeover cost and the inventory carrying cost for the finished goods inventory. Each assembly line changeover costs $5,000, reflecting material handling costs of changing the stocking of component parts on the line, adjusting the layout, and so on. After some discussion with the company comptroller, the production manager concluded that the firm's inventory carrying cost is 10 percent of standard boat cost per year. The item value for each of the product line standard units is: Product Line Standard Boat Cost FunRay series $100 SunRay series 150 StingRay series 200 The master production scheduler has calculated the production lot sizes as 5,000, 3,000, and 4,000 units, respectively.Sales Forecast (Standard Boats each quarter) W N D Product Lines Fun Ray Quarter 0 Quarter 1 Quarter 2 Quarter 3 Quarter 4 qtr 5 Demand Lot Size 5000 Production !Target inventory 3000 Inventory 8 9 Sun Ray 10 Demand 11 Lot Size 3000 Production 12 Target Inventory 1000 Inventory 13 14 Sting Ray 15 Demand 16 Lot Size 4000 Production 17 Target Inventory 3000 Inventory 18 19 20 21 Capacity 22 Capacity Used 23 Capacity Available 24 Production (Standard Boats each quarter) 25 Lot Size Product Lines 26 Overall Quarter 0 Quarter 1 Quarter 2 Quarter 3 Quarter 4 27 Demand 28 Production 29 15000 Capacity Used 30 Inventory 31 32 Inventory 33 Funray 15000 34 Sun Ray 3000 35 Sting Ray 5000 36 23000 37Neptune Manufacturing Sales Forecast (Standard Boats each quarter) Product Lines Quarter 0 Quarter 1 Quarter 2 Quarter 3 Quarter 4 6000 Fun Ray 8000 9000 6000 Sun Ray 4000 5000 2000 2000 0000 7000 Sting Ray 9000 6000 21000 24000 14000 15000 10 11 Capacity 15000 15000 15000 15000 12 Capacity Used 0 0 0 0 13 Capacity Available 15000 15000 15000 15000 14 Production (Standard Boats each quarter) 15 Lot Size Product Lines 16 Quarter 0 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Qtr 5 17 5000 Fun Ray 100.00 18 3000 Sun Ray 150.00 19 4000 Sting Ray 200.00 20 Target Inventory 21 22 Inventory Min Inventory Inventory 23 3000 Funray 3000 15000 24 1000 Sun Ray 1000 3000 25 3000 Sting Ray 3000 5000 26 27 Inventory Costs Holding costs Inventory 28 Funray $ 10.00 29 Sun Ray 15.00 30 Sting Ray 20.00 31 32 Changeover Costs $ 5,000.00 33 Total Cost 34 changeovers Funray 35 Sunray 36 Sting ray 37 38