PART 1:
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: A. 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. 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. 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.
Rubric Guidelines for Submission: Your assignment must using the Case Study Data Set Microsoft Excel document. Use 11-point Calibri font.
Part 2: Please see the attached copy the image of the task.
Prompt: In this assignment, the scenario is to determine which combination of manufacturing tables and chairs is the most profitable. To complete this assignment, follow the steps outlined below: 1. Part 1 of this assignment is ungraded, but will assist you greatly in understanding how a table is set up to run Solver. a) Open the Module Six Assignment Spreadsheet and make sure you are on the tab (worksheet) titled Solver Part 1. You will see a spreadsheet that has been set up to use Solver. (Note: There is more than one way to set up and program a table; the tables used for this exercise illustrate one method.) The table highlighted in gray shows the specific amounts of material, fabrication time, and output needed for each chair and table, as well as the total cost of producing each item. The table in blue calculates total profit from producing both tables and chairs. The table in green tracks the amount of materials and fabrication consumed to produce the volume that will be decided. Lastly, the yellow cells on the green table indicate the materials and fabrication time as well as the output volume minimums set by management. b) Click on each cell to observe the programing used, but do not change anything. c) After you have reviewed the programing, click on the red cell, go to the menu bar at the top of the page, and click on the Data tab. Then click on Solver. The Solver dialog box should open, and you can observe which cell has been identified as the target cell, which cells Solver will change, and how the constraints have been entered. Do not run Solver at this time; simply click Close on the dialog box. 2. Now you will practice running Solver and viewing two of its associated reports. Click on the tab at the bottom on the screen titled Solver Parts 2 and 3. You will see the same data tables from Part 1. Follow these steps: a) At the top of the worksheet, click on Data, then Solver. b) On the Solver Parameters dialog box, click Solve. c) See how the cells with the zeros now have been assigned values. d) Do not close out of the Solver Results dialog box. e) On the Solver dialog box, run a Sensitivity and a Limits report. f) In the area indicated in the Part 2 section of the Solver Parts 2 and 3 tab, type a short paragraph explaining what these reports indicate. 3. In this step, you will adjust the variables and constraints and run Solver again. a) Suppose management has altered their decision and has increased fabrication time from 480 minutes to 600 minutes and increased the minimum number of chairs to be produced from 10 to 16. Run Solver again and list the number of tables and chairs that should be produced in the provided table under Part 3a of the worksheet. b) Now suppose too many tables have been rejected by quality assurance, and the production line for tables will be slowed, increasing fabrication time to 26 minutes. However, management has also found a way to decrease fabrication time on the chairs to three minutes. Use the original constraints for fabrication time (480 minutes) and the minimum tables (4) and chairs (10) and run Solver again. List the number of tables and chairs that should be produced in the provided table under section Part 3b of the worksheet. 4. Use the tab labeled Solver Part 4 to complete the following: a) Create and program a spreadsheet with data based on the following scenario: Scenario Your company has two trucks that it wishes to use on a specific contract. One is a new truck the company is making payments on, and one is an old truck that is fully paid for. The new truck’s costs per mile are as follows: ? Fuel/additives: 54¢ ? Truck payments: 24¢ ? Driver: 36¢ ? Repairs: 12¢ ? Miscellaneous: 1¢ The old truck’s costs are as follows: ? Fuel/additives: 60¢ ? Truck payments: 0¢ ? Rookie driver: 32¢ ? Repairs: 24¢ ? Miscellaneous: 1¢ The company knows that truck breakdowns lose customers, so they have capped estimated repair costs at $14,000. The total distance involved is 90,000 miles (to be divided between the two trucks). After you set up and program your table, be sure to provide a rationale statement. b) Use Solver to determine the number of miles each truck should be driven. Rubric Guidelines for Submission: Your assignment must be using the Module Six Assignment Spreadsheet. Use 11-point Calibri font
0 comments