Question $6.(28$ points$)$ EcoFurn's Sustainable Furniture Journey

In the serene countryside of GreenValley, there is an environmentally conscious furniture company called "EcoFurn." EcoFurn is dedicated to crafting sustainable and beautiful furniture that blended seamlessly with nature. Over the years, their eco$-$friendly designs had gained global recognition, leading to a surge in demand for their furniture.

Production Facilities: EcoFurn had five state$-$of$-$the$-$art furniture factories, each with unique capabilities and materials for crafting their signature pieces. These factories were spread across different regions of GreenValley.

Demand Locations: Their eco$-$friendly furniture was highly sought after at various retail outlets and showrooms in $10$ major cities worldwide.

The Challenge: The challenge for EcoFurn was to optimize their production and distribution $($transportation$)$ processes to meet the surging demand while minimizing costs, all while staying true to their commitment to sustainability.

Data: Data is provided in the Excel file in the worksheet $$Q$6$ Data$.$

You are asked to determine the optimal production and shipment plan $($i$.$e$.,$ the weekly number of truckloads to be shipped from a factory to a city$$s showroom$)$ that minimizes EcoFurn$$s total weekly production and transportation costs. You will formulate a linear programming model to determine the optimal solution using Excel Solver.

NOTE $1$: SHOW ALL YOUR WORK. USE $4$ DECIMAL PLACES IN YOUR CALCULATIONS.

NOTE $2$: YOU SHOULD PROVIDE AND EXPLAIN ALL STEPS IN YOUR SOLUTION IN AN EXCEL WORKSHEET. NAME YOUR WORKSHEET AS Q$6.$

$(3$ points$)$ What are the decision variables? How many decision variables are there? Clearly explain the decision variables in English and provide the mathematical notation used in the algebraic formulation of the decision problem.

The decision variable in this problem is the number of truckloads shipped from each factory to each city$$s showroom. It$$s $50$ decision variables because there is $5$ factories and $10$ cities. The notion is F $($factory$)$ C $($city$).$

FC$=$ # of loads shipped from factory $($F$)$ to City $($C$)$

$(2$ points$)$ What is the objective function? Clearly explain the objective function in English and provide the mathematical formulation.

The objective function is to minimize total weekly production and transport costs. The sum of the production costs at each factory and the number of units shipped from each factory to the city showrooms.

Total cost$=($production cost per unit Factory $*$ the number of truckloads shipped from factory to city$)$ or total cost $=($Production cost per F $*$ FC$)$

$(6$ points$)$ What are the constraints of the problem? Clearly explain each constraint in English and provide the mathematical formulation.

Number of units shipped must be less than or equal to the weekly capacity but greater than or equal to the weekly demand.

$(1$ point$)$ Are there any sign or type restrictions in your formulation? Why or why not? Clearly explain the sign and type restrictions $($if any$).$

Number of loads shipped that would be an integer

All of the values non$-$negative meaning F and C $>=0$

$(8$ points$)$ Prepare a spreadsheet model and determine the optimal production and shipment plan as well as the optimal objective function value using Excel Solver.

$$10,065,300$

$(3$ points$)$ Due to a labor strike in Factory $1,$ EcoFurn had to increase the payments for the production employees as well as the truck drivers who drive from Factory $1$ to any of the $10$ city showrooms. As a result, the production cost per unit went up by $20\%$ and the cost of shipping a truckload of items from Factory $1$ to each of the $10$ city showrooms went up by $15\%.$ What would be the new optimal weekly shipment plan and the optimal total transportation cost after this change? Would the optimal total transportation cost increase by somewhere between $15\%$ and $20\%?$ If not, what is the percentage increase in the optimal total transportation cost?

$$11,955,880.$

Yes, the optimal total transportation cost increased by $19\%.$

Which factory should EcoFurn choose to increase its capacity at $$500$ per week? Provide your answer by using cost$-$benefit analysis for each factory separately considering the change in the total production and transportation cost and the weekly additional cost of $$500.$ To do so$,$ you must show all your work by solving the spreadsheet LP model five times $($one for each factory using the new capacity after the increase$)$ and providing the optimal total weekly production and transportation cost. pls show the last one on excel