Milestone Two

A: 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 Pallets $1361218244872$

Tranport cost to Portland Portland Portland Portland Portland Portland Portland Portland Portland

Cost per Pallet

Frequency

Cost of Shipments

Size of Shipment in Pallets $1361218244872$

Tranport cost to Riverside Riverside Riverside Riverside Riverside Riverside Riverside Riverside Riverside

Cost per Pallet

Frequency

Cost of Shipments

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

Portland

Riverside

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

Riverside

Gross Profit Total for Each Variety of Wine by DC

DC Wine Type Revenue Transport COGS Gross Profit $\%$GP$/$R

Portland Red

White

Organic

Gross Profit Total for Each Variety of Wine by DC

DC Wine Type Revenue Transport COGS Gross Profit $\%$GP$/$R

Riverside Red

White

Organic

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.