This is a Problem Set with 3 question and i am also attaching excel data Screen Shot below

This MS Excel Problem Set.

Can You Please Help me with this problem Set? Also i don't just need Explaination, i need all formula and And updated Excel data file for the same.

1. this Problem Set

2. This is Data Sheet

A produce company supplies organically grown apples to four San Francisco specialty stores. The company's main orchard, located in Petaluma, California, produces approximately 3,000 lbs of organic apples every month, in several varieties. After the apples are collected at the company's orchard, they are transported to one of three preparation centers, where they undergo extensive cleaning and packaging. All the apples brought into a center undergo this process. The cost of transporting organic fruit is rather high, due to the fragility of the product. Table 1 shows the unit shipping and preparation costs for each of the three centers, and the monthly capacities of the centers. For instance, the first center can process at most 200lbs of apples in one month, and it costs $0.30 to transport and process 1lb of apples there. After this process, all the apples in each center must be shipped to one of four specialty stores. The contract that the company signed with the stores involves a stringent quality of service, whereby every store is promised that its demand is fully filled. Monthly demand at the stores is shown in Table 2, and the unit costs of transporting the apples from the preparation centers to the specialty stores is shown in Table 3 . For instance, it costs $0.40 to transport 1lb of apples from Center \#1 to Organic Orchard. The produce company must decide the amount of apples to prepare at each of the three centers, and how much of each specialty stores' demands to supply from each center, at minimum cost. 1. Construct a linear optimization model to address this problem. [ Hint: Think carefully about what the decision variables are. ] Treat each of the following questions independently. Make sure your final models are linear! 2. The manager of Preparation Center 1 believes that his facility could, in principle, process additional apples beyond the installed capacity of 200lbs. In particular, he thinks PC1 could handle up to an additional 150 lbs of apples monthly, by hiring additional staff to handle the extra load. Unfortunately, however, the per-unit (transportation and preparation) cost for handling any of this extra load would be 0.85S/lb. Update the model you created in Q1 to account for the possibility of using some of this additional capacity. [Hint: Hint: One way to model this is to add two additional variables, corresponding to how much of the regular capacity and how much of the 'extra' capacity you should use, and link these decisions with the rest of the model. Another way is to treat the additional capacity as a "separate center".] 3. Whole Wallet has finally agreed to relax their stringent requirement that the entire quantity of 200lbs should be delivered every month. In particular, they are willing to accept under-delivery, but are charging a penalty of $1.50 for every pound of apples that is not delivered. Update the model in Q1 to account for the possibility of under-delivering apples to Whole Wallet. [Hint: Add one additional variable...] Table 2 - Demand for organic apples at the \begin{tabular}{|l|} \hline Parameters \\ \hline Decision Variables \\ \hline Objective Function \\ \hline Constraints \\ \hline Changed logic \\ \hline \end{tabular} \begin{tabular}{c|l|l|} \hline 8 & Store & Monthly Demand (lbs) \\ \hline 9 & Organic Orchard & 300 \\ \hline 10 & Fresh and Local & 500 \\ \hline 11 & Season's Harvest & 400 \\ \hline 12 & Whole Wallet & 200 \\ \hline \end{tabular} Table 3 - Unit transportation cost from the preparation centers to the stores (in $/lb ) \begin{tabular}{ll|l|l|l|l|} \hline 15 & Preparation Center & Store & \multicolumn{3}{|l|}{} \\ \cline { 2 - 6 } & Organic Orchard & Fresh and Local & Season's Harvest & Whole Wallet \\ \hline 17 & 0.4 & 0.6 & 0.3 & 0.7 \\ 18 & 0.6 & 0.6 & 0.2 & 0.45 \\ \hline 1 & 2 & 0.1 & 0.6 & 0.9 \\ \hline \end{tabular}