HERE IS THE LINK TO THE SPREADSHEET TO SEE ALL INFO: https://1drv.ms/x/s!AryG9dGS0v4ag-4NPacXtFBZ2TrARw?e=KwcOgE

I have completed most of this document but I am looking for help with the rest. I am stuck on question C. regarding Gross Profit Total for Each Variety of Wine by DC, specifically Transport, COGS, and Gross Profit. The open table spaces on the A-E tab are what I need help with (C-D). All images are attached.

Questions and Open Answer Sections/Tables A-E

C: Create a table that calculates Gross Profit and Gross Profit as a percentage of revenue, Gross Profit = Revenue (from Milestone One) minus Transport (from part A) and Production (from part B) from Portland and Riverside State the rationale you used Total Revenue Transport Production Gross Profit *%GP/R Portland $10,226,688.00 $192,687.40 $4,010,044.80 $6,023,955.80 58.9% Riverside $12,426,996.00 $156,736.50 $4,765,718.40 $7,504,541.10 60.4% Gross Profit Total for Each Variety of Wine by DC DC Wine Type Revenue Transport COGS Gross Profit %GP/R Portland Red $4,360,320.00 White $4,651,008.00 Organic $1,215,360.00 Gross Profit Total for Each Variety of Wine by DC DC Wine Type Revenue Transport COGS Gross Profit %GP/R Riverside Red $5,642,100.00 White $6,018,240.00 Organic $766,656.00 D: Create a table that calculates Gross Profit (from part C) minus state taxes (from the Costs&Distances tab). State the rationale you used. State Tax Rate Gross Profit State Tax Profit After Portland Riverside E. Provide a summary statement that describes the inefficiencies in the organizational cost and profit analysis, and explain why this information is important for influencing management decisions.Destination Miles Truck Cost per Fronthaul Cost | % Deadhead | Return to Total FTL Shipping Cost Cost per Pallet Cost per Bottle RT Riverside CA 410 $2.15 $881.50 50% $440.75 $1,322.25 $55.09 $0.08 1.6 Oakland CA 80 $3.00 $240.00 100% $240.00 $480.00 $20.00 $0.03 0.3 Portland OR 620 $2.15 $1,333.00 30% $399.90 $1,732.90 $72.20 $0.11 2.5 Seattle WA 800 $2.15 $1,720.00 30% $516.00 $2,236.00 $93.17 $0.14 3.2 1 pallet = 112 cases FTL = 24 pallets 1 case = 6 bottles Destination Cost to Ship One Pallet Cost per Bottle Riverside CA $500.00 $0.74 Oakland CA $200.00 $0.30 Portland OR $600.00 $0.89 Seattle WA $800.00 $1.19 Bottle Red Bottle White Bottle Organic Case Red Case White Case Organic Wholesale Price $7.50 $8.00 $12.00 $45.00 $48.00 $72.00 Product Cost $2.40 $3.40 $6.30 $14.40 $20.40 $37.80 State Taxes Based on Profit CA 8.8% OR 6.6%Ill. Optimizing Performance For the Lodi Winery, you have been asked by management to examine the data collected and analyzed in the previous modules. The objective is for you to help management decide on the right mix of wine bottles to sell based on newly derived profit information while considering the limitations of the particular types of grapes available for production. While doing more research on wine production, you realize that it takes 3.5 pounds of grapes to make a bottle of wine. In addition, you already were provided the price per bottle that the distributors are paying for each variety of wine: Price for Red Wine Price for White Wine Price for Organic Wine 7.5 8 12 After discussing wine production with the operations manager, you also learn that the wineries that supply the grapes to produce the above types of wine can produce up to a total of 200,000 pounds of grapes for a six-month supply of wine bottles for the three markets, with the following expected distribution constraints based on types of grapes. Note that current market demand will not support more than the below constraints for each type: Red Wine Ceiling 22,000 bottles White Wine Ceiling 24,000 bottles Organic Wine 12,000 bottles Note that the production cost per bottle remains the same as before, that is 32% of sales or revenue for red wine, 42.5% of sales for white wine, and 52.5% of sales for organic wine. With additional information you have gathered, you are now ready to determine the optimum production mix to maximize profit.Portland OR Palets of Red Palets of White Palets of Organic 865.1428571 865.1428571 150.7142857 Row Labels T Sum of Cases Red Sum of Cases White Sum of Cases Organic Sum of Pallets Portland OR 96896 96896 16880 1881 Riverside CA 125380 125380 10648 2334 Grand Total 222276 222276 27528 4215 Revenue of Cases I Revenue of Cases WI Revenue of Cases Organic Total Revenue Portland $ 4,360,320.00 $ 4,651,008.00 $ 1,215,360.00 $ 10,226,688.00 Riverside $ 5,642,100.00 $ 6,018,240.00 766,656.00 $ 12,426,996.00A: Create a spreadsheet that calculates the costs of shipping to Portland and Riverside by pallets based on the frequency distribution used in the histograms used in Milestone One. (Link the cost data to the data in the Costs&Distances tab.) State the rationale you used. Size of Shipment in 1 3 6 12 18 24 48 72 Pallets (Bins) Frequency Histogram for Portland OR 15 26 Tranport cost to Portland OR Portland OR Portland OR Portland OR Portland OR Portland OR Portland OR Portland OR 19 30 21 19 6 O 20 15 Cost per Pallet $600.00 $1,732.90 $1,732.90 $1,732.90 $1,732.90 11 $1,732.90 Frequency $3,465.80 $5,198.70 12 21 Frequency 15 19 C 10 21 26 11 6 18 o Cost of Shipments $9,000.00 $32,925.10 $0.00 $36,390.90 $0.00 $45,055.40 $38,123.80 $31,192.20 Total shipping cost: 24 26 Rationale: I coppied over the bin and frequency dat from part five of milestone one. Then I linked the necessary data from the histogram data into this new table $192,687.40 48 11 1 3 6 12 18 24 48 72 More 72 Pallets (Bins) More Size of Shipment in 1 3 6 12 18 24 48 72 Pallets (Bins) Frequency Tranport cost to Riverside CA Riverside CA Riverside CA Riverside CA Riverside CA Riverside CA Riverside CA Riverside CA 12 Histogram for Riverside CA W H 12 Cost per Pallet $500.00 $1,322.25 $1,322.25 $1,322.25 $1,322.25 $1,322.25 $2,644.50 $3,966.75 6 o 58 Frequency 12 12 C 0 58 22 0 12 o 868 Frequency 22 Cost of Shipments $6,000.00 $15,867.00 $0.00 $0.00 $0.00 $76,690.50 $58,179.00 $0.00 Total shipping cost: 18 o 12 12 0 $156,736.50 0 24 0 58 48 22 1 3 6 12 18 24 48 72 More 72 o Pallets (Bins) More B: Create a table that calculates the production costs of the wines sold to Portand and Riverside. Hints: Link the cost data to the data in the Costs & Distances tab. Use a pivot table and some additional programming. State the rationale you used Red White Organic Total Rationale: I created a PivotTable similar to what I did in M1C of milestone one and then created the same revenue table using the PivotTable Data. (Case values Portland $1,395,302.40 $1,976,678.40 $638,064.00 $4,010,044.80 Riverside $1,805,472.00 $2,557,752.00 $402,494.40 $4,765,718.40