Advisors at your service 24/7

Facebook
Twitter
WhizzAcademics.com

Calculate Price

Get a 10 % discount on an order above $ 100
Use the following coupon code :
Whizz15

Management Information Systems

Module 3 Directions

Notes:

This week we’re going to explore the production and market conditions of Rampant Lion more in depth. By using the Solver tool in Excel we can project optimal solutions for product mixes and logistical operations. We will also start using some charts to visually present data. Charts are a powerful tool, particularly in business applications, because they allow you to convey a great deal of information in a quick, simple format that people can easily understand.

  1. On this and all assignments be sure to save your work early and often!
  2. Name your file “Module3FirstNameLastName” using your First and Last Name.
  3. Because we will be using Solver this week you will need to be sure that Solver is available in your installation of Excel. To see if you have the Solver function available go to the Data tab and look for Solver. If Solver is not available then you will need to install it as directed in step two. If Solver is already loaded you can skip to step 3.
  4. To install Solver open Excel, click the File tab, then click Options. Click Add-Ins, then in the Manage box click on Excel Add-Ins. Click Go. In the Add-Ins Available box click the Solver Add-In check box and then click OK. (If the Solver Add-In isn’t available in the Add-Ins Available box you can locate it by clicking on Browse.)
  5. To start with this week’s work, go to the Product Data worksheet.
  6. To calculate the Profit per Unit enter a formula in cell C10 to subtract the Variable Price from the Unit Price. Copy this formula across the range C10:I10.
  7. To calculate the total profit use the SUMPRODUCT function in cell C13 with range 1 being the Units Produced (C3:I3) and range 2 being the Profit per Unit (C10:I10).
  8. To calculate the labor hours and raw material used use the SUMPRODUCT function in cells C16 and C17 to calculate the total required labor hours and raw material for production levels.
  9. To calculate the optimal production levels to maximize profits use the Solver to calculate the maximum result in cell C13 and set a constraints where the range C16:C17 <= F16:F17, the range C3:I3 >= 50,000, and the range C3:I3 <= 118,000
  10. In order to determine how to best produce and ship our products we will move to the Transportation Data worksheet. Begin here by entering “Total Cost” in cell A16.
  11. To calculate the total cost of shipping use the SUMPRODUCT function to multiply the cost of shipping by the projected volume shipped for all regions. To do this you can set array 1 as B4:E6 and array 2 as B10:E12.

 

  1. Next, enter “Shipped” in cell A13 and then enter a SUM formula to calculate the total volume shipped from each location. Place the first calculation in cell F10 and then you can copy down to cells F11 and F12.
  2. In order to ensure that our shipping totals don’t exceed capacity we need a reference to the capacity of each plant. Set cell H10 equal to F4 and then copy down to H11:H12.
  3. Next, we will calculate the total amount shipped to each region by entering a SUM function in cell B13. Copy this SUM function across to the range B13:E13 to calculate a total for all 4 regions.
  4. To reach a final solution use SOLVER to calculate the minimum price to supply all of the regions with the product they need. This needs to be done without exceeding the capacity of each plant.
  5. In the SOLVER function set the output cell as B16. The variable is the volume shipped from each plant to each region so the range that SOLVER will change is B10:E12. In order for the solution to work with the limitations of the plants we need to include 3 constraints. The first is that the solution can’t include negative numbers because that is a physical impossibility. For this, the constraint should define that the range B10:E12 >= 0. We also need to supply customers with everything that they need so the second constraint will set the amount shipped, B13:E13, as greater than or equal to the amount demanded, B14:E14. The final constraint will avoid demanding more from any one plant than that plant can produce. This sets the range F10:F12<= H10:H12.
  6. The final step for this week’s work in evaluating operations at Rampant Lion revisits the production errors calculated last week. Start by going to the Production Errors worksheet and calculating a sum for the total of errors for each component with your output in the range A39:G39.
  7. To display which components most frequently generate errors we’re going to create a chart.
  8. To create thischart the data needs to be displayed as two columns. Our data will be the column headings and the totals that are currently in rows 2 and 39. To create columns, highlight range A2:G2, Copy, then click on cell J2. On the Home tab click the bottom half of the Paste button to open the menu and choose Paste Special. At the bottom of the menu click the Transpose check box and then click OK. This converts what was our row of data into a column.
  9. Our totals will also be transposed using Paste Special, starting in cell K2, but we need to add one extra step. Because the totals are calculated from a formula we only want to copy the values of the cells, not the formula. To do this, when you Paste Special select both the Values radio button and also the Transpose checkbox.
  10. To arrange our errors in order of most frequent occurrence select the range K2:K8. On the Home tab click the Sort & Filter button and select Sort Largest to Smallest. Excel will ask if you want to expand the selection – click the radio button beside Expand the selection and then click Sort. This will link the titles in the range J2:J8 to the numbers in K2:K8 and keep everything together when the sort procedure runs.
  11. With the data properly formatted we can begin the Histogram by first selecting the range J2:K8 and then going to the Insert tab, clicking on Column in the Chart menu, and choosing a 2-D Clustered Column chart.
  12. With the chart selected you should have three available tabs for chart formatting: Design, Layout, and Format. Go to the Layout tab.
  13. On the Layout tab click the Legend button and choose None to remove the legend.
  14. Also on the Layout tab click the Chart Title button and choose Above Chart. Enter the title “Errors by Category”.
  15. Next, click on one of the columns in the chart to select the data series. Now right-click and choose Format Data Series. Set your Gap Width to 5% and then click Close.
  16. For the final step in creating the chart select the data series and right-click again but this time choose Add Data Labels. This places numerical data on each bar displayed on the chart to show exactly what number of each error occurred.
  17. Save your file and submit to your instructor by uploading to Blackboard.

 

Get a 10 % discount on an order above $ 100
Use the following coupon code :
Whizz15

Category: Sample Questions