In this assignment you will prepare a production plan that minimizes the total cost in terms of the certain ones in the case. To do that: 1-Define the problem clearly (10 Pts) 2-Specify and formulate the objective function and/or goals(10 Pts) 3-Specify and formulate the constraints(10 Pts) 4-Create the relevant model using a table in Excel(10 Pts) 5-Solve problem (use Excel Solverd) and show each step clearly and separately.(10 Pts) 6-Generate your solutions and specify your findings. 10Pts) 7-Interprete your findings(10 Pts) 8-Summarize and report what you did as executive brief.(10 Pts) PS: Create and submit an Excel Worksheet for doing calculations and solution steps there.(10 Pts) Create and submit a report in Word Format that covers all these 8 topics and a cover page. (10 Pts) Production Planning: A Case Study At the A1 production department in the Klosch zmir Factory, a total of two different product groups are produced. These products support the assembly lines of Klosch factories in different locations, including zmir. The mentioned parts can be produced technically in the zmir production factory, or they can be supplied as finished products from another supplier, the TSU company in Japan. At the end of this work, you will be expected to decide how much of the orders of the B1 factory should be produced by itself and how much should be purchased from the TSU company. You can access the products produced and the factories produced and sent in the table below. There are a total of 2 product groups (X,Z). These 2 groups of products pass to the finished product phase with different type numbers. The only difference between different type numbers in the same product group is the packaging methods and they are sent to factories. Although the factories sent to are factories in different locations of Klosch, they are customers for the A1 production department in B1. NOTE: In this study, numbers are simplified by 1000. For example, the mentioned 567 units correspond to 567,000 units. The study is conducted in July (01.07.2022). Customers send the order quantities for the type numbers assigned to them monthly for all products to the B1 factory. At the B1 factory, orders are published in the Customer Order Report covering, a 12-month period. Factories plan their production according to the published Customer Order Report. According to the last published report, the total order quantities from all product groups belonging to the A1 production department are as follows. The sales department conducts detailed analyses taking into account the agreements made with customers. As a result, it publishes a Sales Forecast Report showing the estimated sales quantities for the coming years. According to the last published Sales Report, the sales expectation for the A1 production department in 2023 will be as follows. While making the plans for the A1 production department, it takes into account the customer orders for the current year and the estimated sales quantities in the Sales Forecast Report for future years. Below is the total capacity of the A1 production department in the B1 factory shared according to shifts. Agreements are made with supplier companies based on the estimates and customer orders received. Annual minimum quantities are determined and agreements are made with suppliers. If Klosch requests an order below the agreed minimum quantity, it pays a penalty fee. According to the agreement with the TSU company, orders for 12 months have been planned based on the agreed minimum quantity. In case of cancellation of any of the orders below, the penalty amount to be paid for each cancelled month is 40.17. The cost of 1 piece supplied by the TSU company to Klosch is a total of 1.47. The delivery time for the parts ordered from the TSU company is 2 months (60 days) and orders on the way cannot be changed. The orders that Klosch wants from the TSU company are given in the table below according to the arrival dates at the B1 factory. *The Klosch company can make order changes based on the monthly quantities mentioned above. For example, it cannot place an order of 150 units instead of 263 units in November, it either accepts the orders for November or cancels them completely. If a decision is made to cancel an order for a month, a penalty fee of 40.17 is paid. The options to postpone or advance order dates are not considered in this case analysis. Based on the information shared above and included in the appendix, the A1 production department has predicted that it will have spare capacity in the coming months and that if it continues to produce, its stock level will increase. In this case, how should the production plan of the A1 production department of the Klosch company's B1 factory be in the next 12-month period and how should the orders to be purchased from the TSU company be planned? - The stock level of the B1 production department is increasing with updated orders. Therefore, the stock level must be a maximum of 300 units from September onwards (including September). - The A1 production department in the B1 factory must work at full capacity in July and August. - The A1 production department works at full capacity every month when the stock level in the B1 factory is below 300 units." Shift Information The current work model in the B1 factory is 18 shifts, and planning must be done at least one month in advance in order to provide the number of personnel for shift increases. The extra overtime cost resulting from the increase in the shift model is as follows. - 18 Shifts > Standard model, no extra overtime fee. - 19 Shifts > Calculated by adding 5 more units to the 18-shift production quantity. 500 Eur per day worked from the month the shift increase is made. - 20 Shifts > Calculated by adding 5 more units to the 19-shift production quantity. 1000 Eur per day worked from the month the shift increase is made. Capacity Information The capacity information of the mentioned A1 production department is as follows: - Daily production capacity of the A1 production department: 18 units/18 shift daily - Production times of product groups are the same. - Type changeover times on the production line are not taken into account. - All product groups can be produced on each line. Cost Information The unit cost of each product group in the A1 production department of the B1 factory is the same and is as follows: * Material Unit Cost represents the cost per unit of the parts used to produce a product. Variable Cost covers the variable costs that arise in producing a product. If a part is not produced, there is no variable cost. Fixed Cost is the type of cost that consists of wages paid to employees in the A1 production department, fixed expense items, amortization expenses, etc. Although fixed costs are calculated as unit costs for one product, they cover expenses incurred in the A1 production department even if no production is done. 2023 Contract Information - The agreement quantities made with TSU company for the year 2023 are as follows: - Also, the last 4 Customer Order Reports published for the A1 production department at B1 factory are as follows