Use Findings And Raw Data From This Assignment ✓ Solved
In this assignment, use findings and raw data from Milestone
In this assignment, use findings and raw data from Milestone One to analyze types of wine and distribution centers to determine average costs and profits. Specifically address the following: A. Calculate costs of shipping to Portland and Riverside by pallets and frequency. Illustrate results in a table. Use the bin sizes from Milestone One, Part E. B. Calculate the cost of production for the wine varieties sold in Portland and Riverside. Illustrate 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 operational efficiencies. D. 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%. 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.
Paper For Above Instructions
Introduction
This paper describes methods and illustrative calculations for determining shipping costs (by pallet and frequency), production costs by wine variety, gross profit by variety and center, and profit after state taxes for Portland and Riverside distribution centers. The procedures are designed so they can be applied directly to the Milestone One raw dataset; where raw numbers are not provided here, illustrative numeric examples demonstrate calculation methods and expected table formats. The analytical approach follows established cost-accounting and supply-chain practices (Horngren et al., 2013; Chopra & Meindl, 2019).
A. Costs of Shipping to Portland and Riverside by Pallets and Frequency
Methodology: Use the pallet bins from Milestone One, Part E (for example: 0–24, 25–48, 49–72). For each bin, count the number of shipments (frequency) to each center and calculate cost = frequency × cost_per_pallet. If cost per pallet varies by pallet size or carrier, use weighted averages or specific carrier rates. Use Excel functions: COUNTIFS for frequencies, VLOOKUP/INDEX-MATCH for cost rates, and SUMPRODUCT for totals (Kieso et al., 2019).
| Pallet Bin (cases) | Frequency to Portland | Cost per Pallet ($) | Total Cost Portland ($) | Frequency to Riverside | Cost per Pallet ($) | Total Cost Riverside ($) |
|---|---|---|---|---|---|---|
| 0–24 | 12 | 120 | 1,440 | 9 | 130 | 1,170 |
| 25–48 | 20 | 100 | 2,000 | 18 | 110 | 1,980 |
| 49–72 | 8 | 85 | 680 | 10 | 90 | 900 |
| Total | 40 | 4,120 | 37 | 4,050 |
Interpretation: The table shows where pallet frequencies concentrate and the resulting shipping spend. Management can identify which pallet size bins generate disproportionate cost and explore consolidation or mode changes to reduce per-unit transport costs (Coyle et al., 2016).
B. Cost of Production for Wine Varieties
Methodology: Break production cost per case into direct materials (grapes, packaging), direct labor, and allocated manufacturing overhead (utilities, depreciation, facility). Use cost drivers to allocate overhead (machine hours, labor hours). Excel formulas: SUMPRODUCT to compute totals and per-unit averages; pivot tables to aggregate by variety and center (Kaplan & Atkinson, 2014).
| Variety | Direct Materials ($/case) | Direct Labor ($/case) | Overhead Allocated ($/case) | Total Cost per Case ($) |
|---|---|---|---|---|
| Ruby Rd | 6.00 | 1.50 | 2.00 | 9.50 |
| Murky White | 5.00 | 1.25 | 1.75 | 8.00 |
| Whole Earth Organic | 8.50 | 1.75 | 2.50 | 12.75 |
Interpretation: Knowing production cost per case by variety reveals margin potential and helps decide which SKUs to promote to specific centers (Horngren et al., 2013).
C. Gross Profit for Each Variety by Distribution Center
Methodology: Gross profit = Revenue per case × cases sold – Cost of production × cases sold – shipping allocation (if shipping is charged to COGS). Use a pivot table to combine revenue, cost, and shipping for each (variety × center) cell. Include explanatory notes for assumptions used in allocations (Heizer & Render, 2017).
| Variety | Center | Cases Sold | Revenue/Case ($) | COGS/Case ($) | Shipping/Case ($) | Gross Profit ($) | Gross Margin (%) |
|---|---|---|---|---|---|---|---|
| Ruby Rd | Portland | 500 | 18.00 | 9.50 | 2.00 | 3,750 | 41.7 |
| Murky White | Riverside | 400 | 16.00 | 8.00 | 2.50 | 2,600 | 40.6 |
| Whole Earth Organic | Portland | 200 | 25.00 | 12.75 | 3.00 | 1,850 | 34.0 |
Why this matters: Granular gross-profit tables show which product–center combinations drive profitability and which erode margins, enabling targeted operational changes such as SKU rationalization or price adjustments (Kaplan & Atkinson, 2014).
D. Profit After State Taxes
Methodology: Apply the statutory tax rate to pre-tax profit for each center. The instruction specifies 6.6% for Portland and 8.8% for Riverside. Profit_after_tax = Gross_Profit − (Gross_Profit × tax_rate). In practice confirm whether taxes apply at corporate level or as location-specific taxes; here we follow assignment parameters (state tax references: Oregon and California revenue guidance) (Oregon Dept. of Revenue; CA Dept. of Tax and Fee Administration).
| Variety | Center | Gross Profit ($) | Tax Rate | Tax Amount ($) | Profit After Tax ($) |
|---|---|---|---|---|---|
| Ruby Rd | Portland | 3,750 | 6.6% | 247.50 | 3,502.50 |
| Murky White | Riverside | 2,600 | 8.8% | 228.80 | 2,371.20 |
| Whole Earth Organic | Portland | 1,850 | 6.6% | 122.10 | 1,727.90 |
E. Summary of Inefficiencies and Managerial Implications
Key inefficiencies that typically arise (and that the Milestone One raw data can confirm) include: mismatched SKU allocation to centers (low-margin varieties shipped to high-cost centers), suboptimal palletization patterns that increase per-unit shipping costs, and inadequate pricing that does not cover production plus location-specific taxes. These issues reduce profit per case and obscure true product profitability (Chopra & Meindl, 2019; Horngren et al., 2013).
Management implications: Use the tables above to (1) reallocate inventory to centers where each variety yields higher after-tax profit, (2) consolidate shipments into the pallet bins that minimize per-unit transport cost (e.g., encourage orders that fill 48–72 case pallets), (3) revise pricing for low-margin SKUs or renegotiate production and transport contracts, and (4) incorporate tax effects into location-level profitability modeling. Presenting clear, labeled Excel tables and annotated assumptions will allow leadership to make data-driven decisions quickly (Heizer & Render, 2017; Coyle et al., 2016).
Implementation notes: When applying this workflow to the Milestone One dataset, (a) ensure data quality (clean duplicate rows, consistent sku and center names), (b) document assumptions (shipping rates, overhead allocation base), and (c) run sensitivity analyses to test how changes in pallet frequency, shipping rates, or prices impact after-tax profit. Visualizations such as histograms of shipment-pallet sizes and waterfall charts for profit decomposition help communicate findings to stakeholders (Knaflic, 2015).
Conclusion
By calculating shipping costs by pallet bins, production costs by variety, gross profit by variety–center, and profit after taxes, organizations can pinpoint where inefficiencies occur and prioritize corrective actions. The method outlined above is implementable in Excel using pivot tables, COUNTIFS, SUMPRODUCT, and basic arithmetic, and supports management decisions that increase profitability and operational efficiency.
References
- Chopra, S., & Meindl, P. (2019). Supply Chain Management: Strategy, Planning, and Operation. Pearson.
- Horngren, C. T., Datar, S. M., & Rajan, M. (2013). Cost Accounting: A Managerial Emphasis. Pearson.
- Kaplan, R. S., & Atkinson, A. A. (2014). Advanced Management Accounting. Pearson.
- Heizer, J., & Render, B. (2017). Operations Management. Pearson.
- Coyle, J. J., Novack, R. A., Gibson, B., & Bardi, E. (2016). Transportation: A Supply Chain Perspective. Cengage Learning.
- Kieso, D. E., Weygandt, J. J., & Warfield, T. D. (2019). Intermediate Accounting. Wiley.
- Knaflic, C. N. (2015). Storytelling with Data: A Data Visualization Guide for Business Professionals. Wiley.
- Oregon Department of Revenue. State tax guidance. https://www.oregon.gov/dor/ (accessed 2025).
- California Department of Tax and Fee Administration. State and local tax resources. https://www.cdtfa.ca.gov/ (accessed 2025).
- Wine Institute. (2020). Wine industry statistics and distribution insights. https://www.wineinstitute.org/