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

The Cost and Profit analysis Excel: Once you’ve uncovered inefficiencies in sales, you can look at average costs and profits to figure out where the inefficiencies are in these areas. Again, it’s important to know what data to analyze and what tools you can use to do so. It’s also important to know how to describe the impacts that the inefficiencies have on the organization.

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:

  1. Calculate the costs of shippingto Portland and Riverside by pallets and frequency. Show your results in a table. Use the bin sizes from Milestone One, step five.
  2. Calculate the cost of productionfor the wine varieties sold in Portland and Riverside. Show your results in a table.
  3. Generate a labeled table that shows gross profitfor each variety of wine for each distribution center. Explain why this information is important for informing operation efficiencies.
  4. 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%.
  5. 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%

 

attachment_1

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.

Mgmt Sci thru Spreadsheets 21EW5 – Announcements: Milestone Two Part C – Distinction between production cost and Cost of Goods Sold (COGS) mislabeled in given case study data set.

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.

 

Get a 5 % discount on an order above $ 20
Use the following coupon code :
topwritersleague