How can I go about starting the following in excel with formulas and total?
A: Create a spreadsheets that calculate the costs of shipping to Portland and Riverside by pallets based on the frequency distribution used in the Histograms used in Milestone 1 (Link the cost data to the data in the Costs&Distances tab).
B: Create a table that calculates the production costs of the wines sold to Portland and Riverside. Hints: Link the cost data to the data in the Costs&Distances tab. Use a pivot table and some additional programming.
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.
Overview: Once you've uncovered inefficiencies in sales, you can take a look at average costs and profits to determine where inefficiencies lie in these areas. Again, it is important to know what data to analyze and what tools can be used to do so. It is also important to know how to describe the organizational impacts of the inefficiencies. Prompt: In this assignment, you will use your findings and raw data from Milestone One to dive deeper into types of wine and distribution centers. You will need to analyze these factors to determine average costs and profits. All of your analyses need to be submitted in an annotated Excel file, and each analysis needs to include a rationale. Specifically, the following critical elements must be addressed: Calculate costs of shipping to Portland and Riverside by pallets and frequency. Illustrate your results in a table. Use the bin sizes from Milestone One, Part E A. B. Calculate the cost of production for the wine varieties sold in Portland and Riverside. Illustrate your results in a table. C. Generate a labeled table that illustrates gross profit for each variety of wine for each distribution center. Explain why this information is important for informing operation efficiencies Generate a labeled table that shows the profit after state taxes. For Portland, use a tax rate of 6.6% and for Riverside, use 8.8%. 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. D. E. Destination PalletsTotal Cases Cases red Cases white Cases organic Week of Order Pallets (Bins) 4 Oakland CA 4 29 13.00 13.00 11 Oakland CA 26 4 28 Riverside CA 4 30 Riverside CA 31 Riverside CA 33 Riverside CA 34 Riverside CA 36 Riverside CA 38 Riverside CA 35 39 Seattle WA 41 Seattle WA 43 Seattle WA 47 Seattle WA 28 49 Oakland CA 52 Oakland CA 1 Milestone 2 4 A: Create a spreadsheets that calculate the costs of shipping to Portland and Riverside by pallets based on the frequency distribution used in the His 5 State the rationale you used 12 18 24 72 7 Size of shipment in 9 Tranport cos 10 11 cost per pallet 12 Frequen 13 Cost of shipments 14 15 16 17 Size of shipment in 24 72 12 19 Tranport cost to 21 cost per pallet 22 Frequen 23 Cost of shipments 24 26 28nal programing 29 State the rationale you used 30 31 Portland 32Riverside Red White Organic Total 35 36 C: Create a table that calculates Gross Profit and Gross Profit as a percentage of revenue 37 Gross Profit Revenue (from Milestone One) minus Transport (from part A) & Production (from part B) from Portland and Riverside 38 State the rationale you used 39 Total revenue Transport Production Gross profit %GP/R 41 Portland 42 Riverside 43 45 Gross Profit Total For Each Variety of Wine by DC DC Gross profit! %GP/R Wine Type Red White Organic Revenue Transport COGS 47 Portland 49 50 51 Gross Profit Total For Each Variety of Wine by DC 52 53Riverside 54 DC COGS | Gross profit | %GP/R Wine Type Red White Organic Revenue Transport 58 59 D: Create a table that calculates Gross Profit (from part C) minus state taxes (from the Costs&Distances tab) 60 State the rationale you used State tax rate Gross Profit Tax Profit After 62 63 Portland 64 Riverside 65 67 ment decisions. Truck Cost per Fronthaul cost $881.50 $240.00 $1,333.00 $1,720.00 Destination Miles | 96 Deadhead | Return to | $440.75 $240.00 $399.90 $516.00 Total FTL Shipping cost Cost Cost RT 1,322.25 $55.09 $20.00 $72.20 $93.17 410 2 Riverside CA 3 Oakland CA 4 Portland OFR 5 Seattle WA $2.15 $3.00 $2.15 $2.15 50%) 100% 30%) 30%) $480.00 $1,732.90 $2,236.00 0.08 0.03 0.11 0.14 1.6 0.3 2.5 3.2 620 800 1 pallets 112 cases FTL 24 pallets 1 Case 6 bottles Destination Cost to ship one pllet Cost per bottle $500.00 $200.00 600.00 800.00 10 Riverside CA 11 Oakland CA 12 Portland OR 13 Seattle WA 14 15 16 17 18 Whole sale price 19 Product cost 20 21 22 State taxes 23 CA 24 OR $0.74 $0.30 $0.89 $1.19 bottle White bottle Organic case Red case White $12.00 6.30 bottle Red case Organic 7.50 $2.40 $8.00 $3.40 45.00$48.00 $14.40$20.40 $72.00 $37.80 Based on Profit 8.8% 66% Overview: Once you've uncovered inefficiencies in sales, you can take a look at average costs and profits to determine where inefficiencies lie in these areas. Again, it is important to know what data to analyze and what tools can be used to do so. It is also important to know how to describe the organizational impacts of the inefficiencies. Prompt: In this assignment, you will use your findings and raw data from Milestone One to dive deeper into types of wine and distribution centers. You will need to analyze these factors to determine average costs and profits. All of your analyses need to be submitted in an annotated Excel file, and each analysis needs to include a rationale. Specifically, the following critical elements must be addressed: Calculate costs of shipping to Portland and Riverside by pallets and frequency. Illustrate your results in a table. Use the bin sizes from Milestone One, Part E A. B. Calculate the cost of production for the wine varieties sold in Portland and Riverside. Illustrate your results in a table. C. Generate a labeled table that illustrates gross profit for each variety of wine for each distribution center. Explain why this information is important for informing operation efficiencies Generate a labeled table that shows the profit after state taxes. For Portland, use a tax rate of 6.6% and for Riverside, use 8.8%. 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. D. E. Destination PalletsTotal Cases Cases red Cases white Cases organic Week of Order Pallets (Bins) 4 Oakland CA 4 29 13.00 13.00 11 Oakland CA 26 4 28 Riverside CA 4 30 Riverside CA 31 Riverside CA 33 Riverside CA 34 Riverside CA 36 Riverside CA 38 Riverside CA 35 39 Seattle WA 41 Seattle WA 43 Seattle WA 47 Seattle WA 28 49 Oakland CA 52 Oakland CA 1 Milestone 2 4 A: Create a spreadsheets that calculate the costs of shipping to Portland and Riverside by pallets based on the frequency distribution used in the His 5 State the rationale you used 12 18 24 72 7 Size of shipment in 9 Tranport cos 10 11 cost per pallet 12 Frequen 13 Cost of shipments 14 15 16 17 Size of shipment in 24 72 12 19 Tranport cost to 21 cost per pallet 22 Frequen 23 Cost of shipments 24 26 28nal programing 29 State the rationale you used 30 31 Portland 32Riverside Red White Organic Total 35 36 C: Create a table that calculates Gross Profit and Gross Profit as a percentage of revenue 37 Gross Profit Revenue (from Milestone One) minus Transport (from part A) & Production (from part B) from Portland and Riverside 38 State the rationale you used 39 Total revenue Transport Production Gross profit %GP/R 41 Portland 42 Riverside 43 45 Gross Profit Total For Each Variety of Wine by DC DC Gross profit! %GP/R Wine Type Red White Organic Revenue Transport COGS 47 Portland 49 50 51 Gross Profit Total For Each Variety of Wine by DC 52 53Riverside 54 DC COGS | Gross profit | %GP/R Wine Type Red White Organic Revenue Transport 58 59 D: Create a table that calculates Gross Profit (from part C) minus state taxes (from the Costs&Distances tab) 60 State the rationale you used State tax rate Gross Profit Tax Profit After 62 63 Portland 64 Riverside 65 67 ment decisions. Truck Cost per Fronthaul cost $881.50 $240.00 $1,333.00 $1,720.00 Destination Miles | 96 Deadhead | Return to | $440.75 $240.00 $399.90 $516.00 Total FTL Shipping cost Cost Cost RT 1,322.25 $55.09 $20.00 $72.20 $93.17 410 2 Riverside CA 3 Oakland CA 4 Portland OFR 5 Seattle WA $2.15 $3.00 $2.15 $2.15 50%) 100% 30%) 30%) $480.00 $1,732.90 $2,236.00 0.08 0.03 0.11 0.14 1.6 0.3 2.5 3.2 620 800 1 pallets 112 cases FTL 24 pallets 1 Case 6 bottles Destination Cost to ship one pllet Cost per bottle $500.00 $200.00 600.00 800.00 10 Riverside CA 11 Oakland CA 12 Portland OR 13 Seattle WA 14 15 16 17 18 Whole sale price 19 Product cost 20 21 22 State taxes 23 CA 24 OR $0.74 $0.30 $0.89 $1.19 bottle White bottle Organic case Red case White $12.00 6.30 bottle Red case Organic 7.50 $2.40 $8.00 $3.40 45.00$48.00 $14.40$20.40 $72.00 $37.80 Based on Profit 8.8% 66%
