# Cost and Profit analysis Excel

Cost and Profit analysis Excel

Thank you for reading this post, don't forget to subscribe!# Cost and Profit analysis Excel

**Directions**

In this assignment, you’ll use your findings and raw data from Milestone One to dive deeper into the types of wine and distribution centers. You’ll need to analyze these factors to figure out average costs and profits. All of your analyses need to be submitted in an annotated **Excel** file. Each analysis must include your reasons for using the appropriate Excel functions.

Specifically, you must address the following rubric criteria:

- Calculate the
**costs of shipping**to Portland and Riverside by pallets and frequency. Show your results in a table. Use the bin sizes from Milestone One, step five. - Calculate the
**cost of production**for the wine varieties sold in Portland and Riverside. Show your results in a table. - Generate a labeled table that shows
**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%. For Riverside, use 8.8%. - Write a summary statement that describes the inefficiencies in the organizational
**cost and profit analysis**. Explain why this information is important for influencing management decisions.

**What to Submit on **Cost and Profit analysis Excel

Your assignment must be submitted using the __Case Study Data Set__ Microsoft Excel document. Use 11-point Calibri font.

Milestone Two Rubric |
|||||

Criteria |
Exemplary |
Proficient |
Needs Improvement |
Not Evident |
Value |

Costs of Shipping |
Meets “Proficient” criteria and explains well-thought-out reasons for using appropriate Excel functions (100%) | Calculates the costs of shipping to Portland and Riverside by pallets and frequency, and shows results in a table (85%) | Shows progress toward proficiency, but with errors or omissions (55%) | Does not attempt criterion (0%) | 20 |

Cost of Production |
Meets “Proficient” criteria and explains well-thought-out reasons for using appropriate Excel functions (100%) | Calculates the cost of production for the wine varieties sold in Portland and Riverside, and shows results in a table (85%) | Shows progress toward proficiency, but with errors or omissions (55%) | Does not attempt criterion (0%) | 20 |

Gross Profit |
Meets “Proficient” criteria and explanation is detailed and well-thought-out (100%) | Generates a labeled table that shows gross profit for each variety of wine for each distribution center; explains why this information is important for informing operation efficiencies (85%) | Shows progress toward proficiency, but with errors or omissions (55%) | Does not attempt criterion (0%) | 20 |

State Taxes |
N/A |
Generates a labeled table that shows the profit after state taxes (100%) | Shows progress toward proficiency, but with errors or omissions (55%) | Does not attempt criterion (0%) | 20 |

Cost and Profit Analysis |
Meets “Proficient” criteria and shows a well-rounded understanding of how cost and profit analyses influence management decisions (100%) | Writes a summary statement that describes the inefficiencies in the organizational cost and profit analysis; explains why this information is important for influencing management decisions (85%) | Shows progress toward proficiency, but with errors or omissions (55%) | Does not attempt criterion (0%) | 15 |

Articulation of Response |
Submission is free of errors related to citations, grammar, spelling, syntax, and organization and is presented in a professional and easy-to-read format (100%) | Submission has no major errors related to citations, grammar, spelling, syntax, or organization (85%) | Submission has major errors related to citations, grammar, spelling, syntax, or organization that negatively impact readability and articulation of main ideas (55%) | Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas (0%) | 5 |

Total: |
100% |

Hi Class,

In the M2 A-E tab of the case study data set for cells B9 and B19, you need to calculate the actual **Transport cost** to Portland and to Riverside, respectively. From the Costs&Distances tab, the cost to ship one pallet to **Portland** is $600.00 (cell B/C 12); For shipping more than one pallet, row 4 shows the Total FTL (full truck load) cost of $1,732.90 (cell G4) for Portland based on Cost per mile multiplied by no. of miles to go to the specific destination. Since a full truck load (FTL) = 24 pallets of wine as stated in cell L7, 48 pallets would double the transport cost of $1,732.90 or $3,465.89, and so on for Portland.

Please see below calculations for the Portland example:

Size of shipment in pallets | 1 | 3 | 6 | 12 | 18 | 24 | 48 | 72 |

Transport cost to Portland |
$600.00 | $1,732.90 | $1,732.90 | $1,732.90 | $1,732.90 | $1,732.90 | $3,465.80 | $5,198.70 |

Another point to note: If you review the Milestone One case study assignment prompt for Vinho winery, it states, “Vinho contracts with a private trucking company to move full truckloads of wine. A full truck will consist of 24 pallets of wine, totaling 2,688 cases (16,128 bottles). The minimum shipment they will sell is a pallet of wine (112 cases), and they contract out delivery of the pallets unless the cost will exceed the cost of using one of their private trucking company’s trucks. “. Cell G4 = $1,732.90 (Total FTL cost) from Costs & Distances tab. Since the cost to ship one pallet by company’s truck is $600, it would cost the company less to ship 3 pallets (size of first bin in histogram from Milestone 1) up to 24 pallets for a cost of $1,732.90. Correspondingly for **Riverside**, from the Costs&Distances tab, the cost to ship one pallet to Portland is $500.00 (cell B/C 10), and the total FTL shipping cost of $1,322.25 (cell G2).

Hope this helps,

** **

**Mgmt Sci thru Spreadsheets 21EW5 – Announcements: How to calculate cost of shipment to a destination in Part A of Milestone Two.**

Hi Class,

Now that we know how to obtain the transport cost to Portland and Riverside, we need to calculate the cost of shipment by size in pallets. The simplest way to do this is to multiply the corresponding transport cost by the Frequency (bin size of histogram from Milestone One). For example, cost of shipping 3 pallets to Portland = $1,732.90 x 19 = $32,925.10. Using this approach, we don’t need to calculate the cost per pallet in the table.

Hi Class,

In the M2 A-E tab of the case study data set, row 37 states that Gross Profit = Revenue (from Milestone One) minus Transport (from part A) & Production (from part B) from Portland and Riverside. However, the title in column E of the tables for Portland and Riverside for Part C mislabeled it as COGS (Cost of Goods Sold) that requires knowing the beginning and ending finished goods inventory, which is not our focus here; in our case study, it simply means the production cost obtained from Part B, as mentioned in row 37.

Mgmt Sci thru Spreadsheets 21EW5 – Announcements: Calculation of gross profit in Part C of Milestone Two.

Hi Class,

For the calculation of gross profit in Part C, please note that transport cost by wine type = (Revenue by wine type by distributor/Total revenue by distributor) x total transport cost by distributor. For example, in the M2 A-E tab, cell D47 = C47/B41*C41 (the associated transport cost proportional to the revenue of the wine type sold by the distributor). Each transport cost element in the two tables labeled “Gross Profit Total For Each Variety of Wine by Distribution Center” Portland and Riverside is a percent of revenue by wine type by distributor from Milestone 1 Part C. They are not associated with shipment cost from Milestone 2 Part A.

**5 %**discount on an order above

**$ 20**

Use the following coupon code :

topwritersleague