Instructions
Use the Excel sheet given!!!! And use the video to help you also.
Professor Announcement: Welcome everyone to module 5. Your Milestone Two project is due in this module. You will further your analysis from Milestone One by analyzing shipping and production costs to determine average costs and profits. This will help expose operational inefficiencies, and the information you glean will then allow you to investigate optimizing performance, which is part of your final project assignment due in Module Seven. One extremely useful tool used to perform the optimization is Excel Solver which we will be using in this assignment.
We are introducing Solver to you this week so that you will have ample time to explore it before your final project is due. You will not be actually using Solver for your Milestone Two assignment in this module; however, we strongly encourage you to activate it with the instructions provided in the resources and begin exploring it. Solver is a much more advanced tool than any you’ve learned about so far, and it may take a little time to get familiar with it. In Module Five, you will have a quiz about the basic functionality of Solver.
For your Milestone Two assignment, you will be analyzing actual cost to determine the profitability of certain areas of the business. Your spreadsheet is due Sunday 4/7/2024.
Overview
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:
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
Your assignment must be submitted using the Case Study Data Set Microsoft Excel document. Use 11-point Calibri font.