Please solve the MBA problem accurately by filling out the excel spreadsheet using solver. thank you so much for your help and will surely upvote you

Red Brand Canners

Red Brand Canners is a medium$-$sized company specializing in canning and distributing a variety of

fruit and vegetable products under private brand names in the western states of the US$.$ On Monday,

September $13$th$,$ Mitchell Gordon, Vice$-$President of Operations, asked the Controller, the Sales

Manager, and the Production Manager of Red Brand Canners to meet with him to discuss the number

of tomato products to pack that season. The tomato crop, purchased at planting, wasbeginning to arrive

at the cannery. Packing operations would have to start by the following Monday because, after this time,

the fruit would begin to deteriorate. In effect, this ruled out the possibility of reselling any part of the crop

that would be worthless if it remained unpacked.

The Meeting

William Cooper, Controller, and Charles Myers, Sales Manager, were the first to arrive in Mr$.$ Gordon's

office. Dan Tucker, Production Manager, came in a few minutes later and said that he had picked up

Produce Inspection's latest estimate of the quality of the incoming tomatoes. According to the report,

about $20\%$ of the $3,000,000$ pound crop was Grade $"$A$"$ and the remaining portion was Grade $"$B$."$

Mr$.$ Gordon asked Mr$.$ Myers about the demand for tomato products for the coming year. Mr$.$ Myers

replied that they could sell all the canned tomatoes they could produce. However, the expected demand

for tomato juice and paste was limited$.$ The Sales Manager then passed around the latest demand

forecast $($Exhibit $1)$ reminding the group that selling prices had been set in light of the long$-$term

marketing strategy of the company, and that potential sales had been forecast at these prices.

With the new accounting system that had been set up$,$ Mr$.$ Cooper had been able to compute the

contribution for each product, and according to his analysis the incremental profit on whole tomatoes

was greater than for any other tomato product. In May, after Red Brand had signed contracts agreeing

to purchase the grower's production at an average delivered price of $18$ cents per pound, Mr$.$ Cooper

had computed the tomato products' contributions.

Mr$.$ Tucker called Mr$.$ Cooper's attention to the fact that, although production capacity was ample, it was

impossible to produce all whole tomatoes because too small a portion of the crop was $"$A$"$ quality. Red

Brand used a numerical scale to record the quality of both raw produce and prepared products. This

scale ran from $0$ to $10,$ the higher number representing better quality. $"A"$ tomatoes averaged $9$ points

per pound and $"$B$"$ tomatoes $5$ points per pound. Mr$.$ Tucker noted that the minimum average input

quality for canned whole tomatoes was $8$ and for juice $6$ points per pound. Paste could be made entirely

from $"$B$"$ grade tomatoes. This meant that whole tomato production was limited to $800,000$ pounds.

Mr$.$ Myers, who had been doing some calculations, said that although he agreed that the Company

"should do quite well this year", it would not be by canning whole tomatoes. It seemed to him that tomato

costs should be allocated on the basis of quality and quantity rather than by quantity only, as Mr$.$ Cooper

had done. Therefore, he had recomputed the marginal profit on this basis and from his results $($see

Exhibit $2).$

Exhibit $2.$ Myers' Marginal Profit Analysis of Tomato Products

$1-$ Write your objective function and constraints for this problem with the objective of maximizing the profit.

The decision variables are the amount of each type of tomato $($ A and $B)$ in pounds used to produce

each product $($Whole tomato, Juice, and Paste$).$

$2-$ Use the provided Excel template $($or develop your own template$)$ to formulate the problem and find the

optimal solution using Solver.