Creating a Methodology

/in /by

Chapter 1

Introduction to Statistics

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Copyright © 2008 by Hawkes Learning Systems/Quant Systems, Inc.

All rights reserved.

 

Statistics is the science of gathering, describing, and analyzing data.

 

Statistics is the numerical description of sample data.

Definitions:

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

    • Data – information, in particular, information prepared for a study.

 

    • Population – a particular group of interest.

 

    • Parameter – a numerical description of a particular population characteristic.

 

  • Census – when data is collected from every member of the population.

Definitions:

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Sample – a subset of the population from which data is collected.

 

Sample Statistic – a numerical description of a particular sample characteristic.

Definitions:

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

 

 

 

 

 

 

 

 

 

Population Sample
Whole group Part of the group
Group I want to know about Group I do know about
Characteristics are called parameters Characteristics are called statistics
Parameters are generally unknown Statistics are always known
Parameter is fixed Statistics change with the sample

 

 

 

 

 

 

 

 

 

 

 

 

  • In a survey, 359 college students were asked if they had tried Vanilla Coke. 83 said yes.

Population = all college students

Sample = 359 college students

 

  • A survey of 1125 households in the U.S. found that 65% subscribe to digital cable.

Population = all U.S. households

Sample = 1125 households

Identify the population and the sample.

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • The average grade on Test 1 in Ms. Shirley’s statistics class is an 87.

Parameter

 

  • The average Math score for all high school graduates on the SAT was a 519.

Parameter

 

  • In a survey of 135 college students, 52% of them said they plan to attend graduate school.

Statistic

Is the numerical value a parameter or a statistic?

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • Identify the population and sample.

Population = all families of the students at the school

Sample = 195 families

  • Does the value “76%” represent a parameter or a statistic?

Statistic

 

The principal of a large K-12 school is considering adopting a dress code for the upcoming school year. Before going to the school board, he wants to know the percentage of parents who would favor a dress code. He randomly chooses 15 families from each grade to interview. Out of the 195 families surveyed, 76% favor a dress code.

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

    • Descriptive statistics – gathers, sorts, summarizes and displays the data; “just the facts”.

 

  • Inferential statistics – uses descriptive statistics to estimate population parameters; an educated guess about the population based on the facts.

Branches of Statistics:

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

In a survey of 100 students, 83.2% of students are happy with the food in the cafeteria.

 

 

Identify the descriptive statistic(s). What inferences can be made?

Descriptive statistic: 83.2% of the 100 students surveyed are happy with the food in the cafeteria.

 

Possible inferences: 83.2% of all students are happy with the food in the cafeteria.

 

Introduction to Statistics

1.1 Getting Started

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Qualitative

Quantitative

 

Descriptions and labels

 

Counts and measurements

Qualitative vs. Quantitative:

 

Introduction to Statistics

1.2 Data Classifications

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • The weights of members of the football team.

Quantitative

  • The flavors of Ben and Jerry’s Ice Cream.

Qualitative

  • The jersey numbers of a women’s basketball team.

Qualitative

  • Student ID numbers

Qualitative

Classify as Qualitative or Quantitative:

 

Introduction to Statistics

1.2 Data Classifications

 

HAWKES LEARNING SYSTEMS

math courseware specialists

Qualitative

Quantitative

Continuous

DISCRETE

Usually measurements

Usually counts of things

Continuous vs. Discrete:

 

Introduction to Statistics

1.2 Data Classifications

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • The temperature in Fahrenheit of cities in North Mississippi.

Continuous

  • The number of rooms in a house.

Discrete

  • The number of peanuts in a jar.

Discrete

  • The height of a door in inches.

Continuous

 

Classify as discrete or continuous:

 

Introduction to Statistics

1.2 Data Classifications

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Levels of Measurement

Nominal

Names

Ordinal

Order

Interval

0 is a placeholder

Ratio

0 means the absence of something

 

Introduction to Statistics

1.2 Data Classifications

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • Today’s high temperature (in Fahrenheit) for varying cities across the U.S.

Quantitative – Interval

  • The colors contained in a box of crayons.

Qualitative – Nominal

  • The boiling point (in Kelvin’s) for varying chemical compounds.

Quantitative – Ratio

  • The individual page numbers at the bottom of each page in the statistics book.

Quantitative – Ordinal

 

Determine the level of measurement:

 

Introduction to Statistics

1.2 Data Classifications

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Collect Data

Defines Population/Variables

Question

Calculate Statistic

Estimate Parameter

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

    • Observational Study – researcher observes data that already exists.

 

    • Experiment – researcher seeks to create data to identify cause and effect relationships.

 

 

Types of Studies:

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • A medical researcher wants to examine the effects of exercise on cardiovascular health.

Experimental

  • A recording company is interested in knowing the percentage of teenagers that download music off of the internet.

Observational

  • A chain of grocery stores wants to know how much the average family spends on produce each month.

Observational

 

 

Observational Study or Experimental Study?

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

    • Ask, “How will the data be gathered?”

 

    • Very Important:
    • It is essential that a representative sample is chosen out of the population.
    • A good sampling frame is the first step to choosing a representative sample. The sampling frame is a list of members of the population.

 

 

Observational Studies:

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • Census
  • Random Sample
  • Stratified Sample
  • Cluster Sample
  • Systematic Sample
  • Convenience Sample

 

Sampling Techniques:

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

 

 

Census:

Every member of the population is included.

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Random Sample:

Every member has an equal chance of being selected.

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Stratified Sample:

The population is divided into subgroups called strata and the

same number of subject are chosen from each strata.

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Cluster Sample:

The population is divided into subgroups, called clusters, that are

similar to the population and whole clusters are chosen at random.

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

 

 

Systematic Sample:

Selecting every nth member of the population.

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Convenience Sample:

The sample is convenient for the researcher to select.

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • Chosen at random, 200 rural and 200 urban persons age 65 or older were asked about their health and experience with prescription drugs.

Stratified

  • For quality assurance, every 12th engine part is selected from an assembly line and tested for durability.

Systematic

  • A survey concerning the taste of a new soda flavor was performed at the local grocery store from 5:00 PM to 8:00 PM.

Convenience

 

Identify the sampling method:

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • To determine how a particular political candidate was going to fare in an upcoming election, different counties in the state were randomly selected and every eligible voter in the randomly selected counties was surveyed.

Cluster

  • To determine who will win a $100,000 shopping spree at the local mall, the owner of the mall draws a name out of a box.

Random

 

Identify the sampling method:

 

Introduction to Statistics

1.3 Process of a Statistical Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

    • Subjects – people, animals, or things being studied.
    • Participants – term used when the subjects of a study are people.
    • Treatment – characterizes an experiment; applied to some or all subjects.
    • Treatment Group – a group in which researchers apply a treatment.
    • Control Group – a group identical to the treatment group except that no treatment is applied.

 

Experiment:

 

Introduction to Statistics

1.4 Reality of Conducting a Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

    • Single-Masked – when the subjects do not know in which group they have been placed.

 

    • Double-Masked – when neither the subjects nor the people interacting with the subjects know to which group the subjects belong.

 

  • Placebo – a substance that appears identical to the actual treatment.

 

Experiment:

 

Introduction to Statistics

1.4 Reality of Conducting a Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

  • Institutional Review Board (IRB)
  • Informed consent
  • Human or animal subjects
  • Confidentiality

 

Ethical Concerns:

  • Bias
  • Researcher bias
  • Sampling errors
  • Non-sampling errors

 

Practical Concerns:

 

Introduction to Statistics

1.4 Reality of Conducting a Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Errors:

Sampling Errors

Non-Sampling Errors

 

Dropout – a participant who begins the study but fails to complete it.

Poor sampling frame

Participation bias – when there is a problem with either the participation, or lack thereof, of those chosen for the study.

Processing error – an error as simple as a typo.

Lying or faulty memory

Non-adheres – subjects who stray from the directions they were given.

Confounding variables – factors that influence the results of the study that the researchers did not, or could not, account for.

 

Introduction to Statistics

1.4 Reality of Conducting a Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

Consider preparing a research proposal for the following question, “what percentage of 11 to 16 year olds smoke cigarettes?”

Something to Ponder:

What are some possible issues of getting informed consent, confidentiality, going to the IRB, dealing with human subjects, bias?

 

Introduction to Statistics

1.4 Reality of Conducting a Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

CompuTech is a large computer company with hundreds of thousands of customers across the country. They are interested in knowing the percentage of customers that are satisfied enough with their purchases to continue buying CompuTech products in the future. The company hires an independent research firm to conduct the study. The firm randomly selects 50 customers from each of the following metropolitan areas: New York, Los Angeles, and Chicago. They find that 77% of the customers surveyed are satisfied with their CompuTech purchase and plan to continue buying CompuTech products in the future. Using statistical methods, the firm estimates that between 74% and 80% of all CompuTech customers will continue doing business with the company.

Identify the population.

All CompuTech customers

Identify the sample.

150 customers

Identify any statistics.

77% of the customers surveyed are satisfied

 

Introduction to Statistics

1.4 Reality of Conducting a Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

 

CompuTech is a large computer company with hundreds of thousands of customers across the country. They are interested in knowing the percentage of customers that are satisfied enough with their purchases to continue buying CompuTech products in the future. The company hires an independent research firm to conduct the study. The firm randomly selects 50 customers from each of the following metropolitan areas: New York, Los Angeles, and Chicago. They find that 77% of the customers surveyed are satisfied with their CompuTech purchase and plan to continue buying CompuTech products in the future. Using statistical methods, the firm estimates that between 74% and 80% of all CompuTech customers will continue doing business with the company.

Identify the parameter.

Between 74% and 80% of all customers are satisfied

What sampling technique was used?

Stratified sampling

Is the study biased? If so, what type of bias occurred?

Sampling errors since the population of the U.S. was not represented

 

Introduction to Statistics

1.4 Reality of Conducting a Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

Consider the following phrase, “the firm estimates that between 74% and 80% of all CompuTech customers will continue doing business with the company.”

Is this an example of descriptive or inferential statistics?

Inferential

 

Introduction to Statistics

1.4 Reality of Conducting a Study

 

HAWKES LEARNING SYSTEMS

math courseware specialists

Excel Modules 9-16 SAM Capstone Project 1

/in /by

Illustrated Excel 2016 | Modules 9-16: SAM Capstone Project 1a

 

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png Illustrated Excel 2016 | Modules 9-16: SAM Project Capstone 1a

Breeze Smart Home Devices

AUTOMATING AND ANALYZING DATA

GETTING STARTED

Open the file IL_EX16_CS9-16a_FirstLastName_1.xlsm, available for download from the SAM website.

Save the file as IL_EX16_CS9-16a_FirstLastName_2.xlsm by changing the “1” to a “2”.

0. If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

1. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

1. Support_IL_EX16_CS9-16a_Orders.accdb

With the file IL_EX16_CS9-16a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

· If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

If you see a Message Bar with a security warning at the top of the Excel window, click the Enable Content button in the Message Bar to enable the macros contained in the file.

This project requires you to use the Solver and the Analysis ToolPak add-ins. If these add-ins are not available on the Data tab in the Analysis group (or if the Analysis group is not available), click the File tab, and then click the Options button. In the left pane of the Excel Options dialog box, click the Add-Ins option. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. In the Add-Ins dialog box, click the Analysis ToolPak and the Solver Add-In check boxes and then click the OK button. Follow any remaining prompts to install the Analysis ToolPak and Solver.

To complete this project you will need to display the Developer tab. To add this tab to the Excel Ribbon, click the File tab to open Backstage View and then click the Options button. In the Excel Options dialog box, click the Customize Ribbon option and click the Developer check box. Click the OK button to close the Excel Options dialog box and confirm that the Developer tab appears in the Excel Ribbon.

 

PROJECT STEPS

Will Deyes is the sales manager for Breeze Smart Home Devices, a growing company that sells smart devices such as lights, locks, plugs, and thermostats that can be controlled with a smartphone. Will has asked you to update a workbook to show recent sales data and identify trends. He has reviewed the workbook and inserted comments about its contents.

Switch to the Sales Overview worksheet and respond to a comment as follows:

a. Read Will’s comment in cell C5, and then change the value in cell C16 to $416,000 to respond to Will’s request.

b. At the end of Will’s comment in cell C5, press ENTER, and then add OK, it’s done. (including the period).

In response to a comment, modify the Total Sales by Region chart in the range D1:G12 as follows:

c. Read Will’s comment in cell C10, and then change the chart type to a Clustered Bar chart using the first chart option.

d. Add the data in the range B9:C9 to the chart.

e. Apply the Style 5 chart style to coordinate with other charts in the worksheet.

f. Change the colors of the chart to Monochromatic Palette 5 in the Monochromatic section of the Change Colors gallery. (Hint: Depending on your version of Office, the name of the color may appear as Color 9.)

g. Delete Will’s comment in cell C10.

Modify the axes to identify the data in the bar chart as follows:

h. Add 2019 Sales as the primary horizontal axis title.

i. Display the units on the primary horizontal axis in Thousands.

j. Add Inside End data labels to the bars.

k. Remove the legend from the chart.

To show trends in the quarterly sales data for each region, add sparklines to the worksheet as follows:

l. Add Column Sparklines to the range G15:G21 based on data in the range C15:F21.

m. Apply the Blue, Accent 5 sparkline color (9th column, 1st row of the Theme Colors palette) to coordinate with the colors in the other charts.

n. Display a High Point marker in the sparklines to quickly identify the highest sales quarter for each region.

o. Change the High Point marker color to Orange, Accent 2 (6th column, 1st row of the Theme Colors palette) so that the high points use the orange accent color in the worksheet.

Make the Total Quarterly Sales – 2019 chart in the range I1:O12 easier to interpret as follows:

p. Add Sales as the primary vertical axis title.

q. Increase the font size of the primary vertical axis title to 12 pt.

r. Add Quarters as the primary horizontal axis title.

s. Increase the font size of the primary horizontal axis title to 12 pt.

Will wants to make the Quarterly Sales by Region chart easier to interpret. Format the clustered column chart in the range H14:O27 as follows:

t. Change the chart type to a Stacked Column chart grouped by Quarter.

u. Change the fill color of the Mid-Atlantic data series to Blue in the Standard Colors palette (8th column) to make the data series less prominent.

v. Remove the legend.

w. Add a Data Table With Legend Keys to the chart.

x. Resize the stacked column chart so it covers the gray shading in the range H14:O34.

Switch to the Sales by Product worksheet. Modify the Quarterly Sales Trend clustered column chart in the range C1:F11 so it compares products across all quarters by switching the row and column data.

Modify the column sparklines in the range F15:F19 to make it easier to compare the product sales in each quarter as follows:

y. Change the sparkline type to Line.

z. Apply the Blue, Sparkline Style Dark #5 sparkline style.

aa. Display the High Point and the Low Point in the sparklines.

Modify the 2019 Sales by Product Type chart in the range G1:L19 to make the chart easier to analyze as follows:

ab. Change the chart type to a Combo chart.

ac. Use Clustered Column as the chart type for the Lights, Locks, Plugs, and Thermostats data series.

ad. Use Line as the chart type for the Average data series.

ae. Change the color of the line in the Average data series to Orange, Accent 2 (6th column, 1st row of the Themes Colors palette) to make the line more noticeable.

af. Remove all the gridlines from the chart.

ag. Change the Maximum Bounds of the vertical axis to 900,000.

ah. Add a Linear Trendline based on the Thermostats data series.

ai. Change the weight of the new trendline to 2¼ pt.

aj. Apply the White, Background 1, Darker 15% shape fill color (1st column, 3rd row of the Theme Colors palette) to the Plot area of the chart to separate the plot area from the rest of the chart.

ak. Remove the primary horizontal axis title from the chart.

Switch to the Sales by Rep worksheet. Will created a macro to insert a chart of sales by each sales representative. From the last time he ran the macro, the worksheet displays a chart in the incorrect format (a bar chart). The chart also includes sales data for Harsia and Lewison, but not Sattinger. Will has already updated the macro to produce a column chart instead of a bar chart. Troubleshoot and edit the data range in the macro as follows:

al. Assign the SalesChart macro to the button labeled Chart Sales by Rep.

am. Edit the SalesChart macro by changing the selected range in the following lines of code to include the Sattinger label in cell A8 and sales value in cell C8:

Range(“A5:A7,C5:C7”).Select

and

ActiveChart.SetSourceData Source:=Range( _ “‘Sales by Rep’!$A$5:$A$7,’Sales by Rep’!$C$5:$C$7”)

an. Save the changes, then delete the bar chart.

ao. Click the Chart Sales by Rep button to run the updated SalesChart macro to verify the chart includes the information for all three sales reps.

ap. Resize and reposition the column chart so the upper-left corner is in cell E4 and the lower-right corner is in cell K18.

Will also created a macro named RepBonus, which determines which sales reps receive a bonus each quarter. Cell D8 incorrectly indicates Sattinger should not receive a bonus though her sales exceeded the quota. Troubleshoot the RepBonus macro as follows:

aq. Edit the RepBonus macro in the Visual Basic Editor. Correct the following code at the end of the module so that Sattinger will receive a bonus when her sales exceed $450,000:

If Range(“C8”) >= 4500000 Then

Range(“D8”).Select

ActiveCell.Formula = “Yes”

Add a button to the worksheet as follows so that Will can run the RepBonus macro by clicking the button:

ar. Below the Chart Sales by Rep button, insert a Rectangle to use as a button.

as. Reposition and resize the rectangle so it covers the range A13:C14.

at. Enter Assign Bonuses as the button text.

au. Change the font size of the button text to 14 pt.

av. Apply the Middle Align and Center alignment options.

aw. Apply the Moderate Effect – Blue, Accent 5 shape style to the new button.

ax. Assign the RepBonus macro to the Assign Bonuses button.

ay. Run the RepBonus macro. (Hint: The value in cell D8 should be “Yes”.)

Will asks you to work with the company’s current revenue and sales data to develop scenarios and what-if analyses that identify ways to increase profits on thermostats.

Switch to the Thermostats worksheet, which contains a few errors. Correct the errors as follows before performing the analyses that Will requests:

az. Select cell C8 and then use Error Checking to determine the cause of the divide by zero error. Examine the formula and then modify it so that revenue amount is divided by the total sales amount to calculate the percentage of total.

ba. Format the value in cell C8 using the Percentage number format with 0 decimal places.

bb. Select cell F11. Use the Trace Precedents button to locate the cells on which that figure is based, then correct the formula so that it divides the total R&D cost for Aurora models by the number of Aurora units sold.

bc. Format the value in cell F11 using the Comma Style number format with 2 decimal places.

Will has already created two scenarios in the Thermostats worksheet. Create a third scenario to examine the effects of a 10 percent sales reduction in thermostat models as follows:

bd. In the Scenario Manager, add a new scenario and use Unit Sales Decrease 10% as the scenario name.

be. Change the units sold values in the range F12:H12.

bf. Use the information shown in Table 1 below as the values for the changing cells.

Table 1: Unit Sales Decrease 10% Scenario Values

 

Changing Cell Value
Aurora_Units_Sold (F12) 19800
Crystal_Units_sold (G12) 18315
Glide_Units_Sold (H12) 15930

 

 

bg. Show the results of the Current scenario.

Compare the results of the three scenarios as follows:

bh. Create a Scenario Summary Report for result cells B8:B10, F13:H13.

bi. On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column E.

bj. Delete the contents of the range B16:B18 to remove the notes.

Return to the Thermostats worksheet. Determine how changing the number of Aurora thermostats sold will affect gross profit as follows:

bk. In cell F17, enter a formula that references cell F13, the gross profit of Aurora thermostats.

bl. Select the range E17:F22 and then create a one-input data table.

bm. Use the value in cell F12 as the column input cell because cell F12 contains the units sold amount for Aurora thermostats.

Will asks you to determine how many Crystal thermostats the company needs to sell to earn a gross profit per unit of $18.00. Provide this information to Will as follows:

bn. Use Goal Seek to set cell G14 (the gross profit per Crystal thermostat) to a value of 18.

bo. Change the number of units sold in cell G12 to achieve the goal.

Switch to the Controllers worksheet. Breeze Smart Home Devices is developing a controller for managing smart home devices from a single control panel. The company plans to work with three vendors to manufacture the controller. Will wants to know how to minimize the total manufacturing cost. Run Solver to solve this problem as follows:

bp. Set the objective as minimizing the value in cell E12 (Mfg_Total_Cost). (Hint: Be sure to select the Min option to minimize the value.)

bq. Use the range B11:D11 as the changing variable cells.

br. Adjust the total manufacturing cost by each vendor using the constraints in Table 2 below:

Table 2: Solver Constraints

 

Requirement Cell Reference Comparison Operator Constraint
The units produced values must be integers. B11:D11 int integer
Vendors must produce at least 5,000 units. B11:D11 >= B16
Beckes can produce a maximum of 12,000 units. Beckes_Units <= B17
SmartEnergy can produce a maximum of 7,500 units. SmartEnergy_Units <= B18
TW Assembly can produce a maximum of 12,000 units. TW_Assembly_Units <= B19
Breeze Smart Home Devices requires 25,000 controllers. Total_Units = B20

 

 

bs. Make unconstrained variables non-negative and use GRG Nonlinear as the solving method, and then run Solver. Keep the solution, then return to the Solver Parameters dialog box. Save the model beginning in cell D16, then close the Solver Parameters dialog box.

Will wants to compare the values you found using Solver to the original values. Provide this information as follows:

bt. In the Scenario Manager, add a scenario using Optimal Cost as the scenario name. (Hint: The Controllers worksheet already contains a predefined scenario containing the original values of cells B11:D11.)

bu. Accept the current values for the changing cells, which show the results of the Solver model.

bv. Create a Scenario Summary report using cell E11 (which has the defined name Total_Units) as the result cell.

bw. Change the name of the Scenario Summary 2 worksheet to Controllers Scenario to distinguish it from the other Scenario Summary worksheet.

Will asks you to produce summary statistics on the controllers data. Provide this information as follows:

bx. Return to the Controllers worksheet, and then use the Data Analysis tool to generate Descriptive Statistics.

by. Use the range H12:L12 (the gross profit from years 1 through 5) as the input range.

bz. Indicate that the data is grouped by rows.

ca. Display the statistics in the output range starting in cell G16 of the Controllers worksheet.

cb. Produce summary statistics.

cc. Enter Controller Projections in cell G16 to provide a descriptive heading for the statistics.

Will has an Access database containing customer order data for the first quarter of 2020. He asks you to bring this data into the Excel workbook.

Switch to the Orders worksheet. Import and format the Access data as follows:

cd. Import the data from the Orders table of the Access file Support_IL_EX16_CS9-16a_Orders.accdb into the worksheet at cell A4.

ce. Import the data as an Excel table.

cf. Apply the Table Style Light 13 table style to the imported data. (Hint: Depending on your version of Office, the table style may apear as Blue, Table Style Light 13 instead.)

cg. Apply the Accent 5 cell style to the range A4:G4.

Calculate the total sales for each order and sort the table as follows:

ch. Add a column named Total Sales to the right of the UnitPrice column.

ci. Resize column H to its best fit.

cj. In cell H5, insert a formula without using a function that multiplies the Quantity by the UnitPrice. If the formula does not automatically fill the rest of the table column, copy and paste the formula into the range H6:H67.

ck. Format the UnitPrice and Total Sales values using the Currency number format with the $ symbol and 2 decimal places.

cl. Sort the records by the Total Sales field from Smallest to Largest.

Calculate the total number of units sold as follows:

cm. Add a Total Row to the table.

cn. Use the Total Row to calculate the sum of the values in the Quantity column.

Use Orders as the defined name of the table.

Will wants to display the quantity of products ordered by customer and type. To provide this information, create and format a PivotTable as follows:

co. Insert a PivotTable on a new worksheet based on the Orders table.

cp. Use Pivots as the name of the worksheet.

cq. Add the Product field to the Rows area of the PivotTable.

cr. Add the CustomerID field to the Rows area below the Product field.

cs. Add the Quantity field to the Values area of the PivotTable. (Hint: The field appears as Sum of Quantity.)

ct. Apply Pivot Style Medium 13 to the PivotTable to use a design similar to the one applied to the Orders table. (Hint: Depending on your version of Office, the PivotTable style may appear as Sky Blue, Pivot Style Medium 13.)

cu. Turn off the field headers in the PivotTable.

cv. Display the PivotTable using the Show in Compact Form report layout.

Return to the Orders worksheet and add the record shown in Table 3 on the next page as the last record in the Orders table before the Total row. (Hint: The value for the Total Sales column is calculated automatically.)

 

Table 3: New Record for the Orders Table

 

Column Title Value
OrderID 64
CustomerID BR209
Region South Atlantic
OrderDate 3/28/2020
Product Thermostat
Quantity 5
Unit Price $219.00

 

 

Switch to the Pivots worksheet and refresh the data source so it displays accurate data.

Will also wants to display the quantity and total sales of products by region. To provide this information, create and format a PivotTable as follows:

cw. Insert a PivotTable in cell D3 of the Pivots worksheet based on the Orders table.

cx. Add the Product field to the Columns area of the PivotTable.

cy. Add the Region field to the Rows area of the PivotTable.

cz. Add the Total Sales field to the Values area of the PivotTable. (Hint: The field appears as Sum of Total Sales.)

da. Change the Value Field Settings for the Sum of Total Sales values to apply the Currency number format with 0 decimal places and the $ symbol.

db. Apply Pivot Style Medium 13 to the PivotTable to use a design similar to the one applied to the Orders table. (Hint: Depending on your version of Office, the PivotTable style may appear as Sky Blue, Pivot Style Medium 13.)

To distinguish between the two PivotTables, use Products in cell A2 to identify the first PivotTable. Use Regions in cell D2 to identify the second PivotTable.

Provide another way to visualize the sales by region data by creating a PivotChart as follows:

dc. Create a Clustered Column PivotChart based on the PivotTable in the range D3:I11.

dd. Move and resize the PivotChart so that its upper-left corner is in cell D14 and its lower-right corner is in cell I35.

de. Add the title Sales by Region to the PivotChart.

df. Display the legend at the bottom of the chart.

dg. Change the colors of the chart to Monochromatic Palette 5 in the Monochromatic section of the Change Colors gallery. (Hint: Depending on your version of Office, the name of the color may appear as Color 9.)

dh. Hide all of the field buttons in the chart.

Will is interested in the performance of thermostats in certain regions. Filter the Regions PivotTable to display thermostat orders from the Midwest, Mountain, and Pacific regions as follows:

di. Insert a slicer based on the Region field in the range J3:K13, then use the slicer to filter the Regions PivotTable and display Midwest, Mountain, and Pacific orders only.

dj. Add a second slicer based on the Product field.

dk. Move the Product slicer to the right of the Region slicer so that its upper-left corner is in cell L3 and the lower-right corner is in cell M13.

dl. Use the Product slicer to filter the Regions PivotTable and display Thermostat orders only.

dm. Apply the Slicer Style Light 5 style to both slicers. (Hint: Depending on your version of Office, the Slicer style may appear as Sky Blue, Slicer Style Light 5.)

Return to the Orders worksheet. Complete the Quantity per Product section as follows:

dn. In cell K5, enter a formula that extracts data from cell B4 of the Products PivotTable on the Pivots worksheet.

do. In cell K6, enter a formula that extracts data from cell B9 of the Products PivotTable on the Pivots worksheet.

dp. In cell K7, enter a formula that extracts data from cell B17 of the Products PivotTable on the Pivots worksheet.

dq. In cell K8, enter a formula that extracts data from cell B24 of the Products PivotTable on the Pivots worksheet.

Your workbook should look like the Final Figure on the following pages. In the Thermostats worksheet, cell G12 is intentionally blurred. The Controllers worksheet Final Figure has been intentionally omitted. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project.

 

 

Final Figure 1: Sales Overview Worksheet

Final Figure 2: Sales by Product Worksheet

 

Final Figure 3: Sales by Rep Worksheet

Final Figure 4: Scenario Summary Worksheet

 

Final Figure 5: Thermostats Worksheet

Final Figure 6: Controllers Scenario Worksheet

 

Final Figure 7: Pivots Worksheet

 

Final Figure 8: Orders Worksheet

2

Technology And Information Management

/in /by

Weichih Sun 3/14/17 TIM 125

Homework 7

Problems Estimated Time Actual Time
Problem 1 1 Hour 1 Hour
Problem 2 1 Hour 1 Hour
Problem 3 2 Hours 1.5 Hours
Problem 4 3 Hours 2.5 Hours
Problem 5 2 Hours 1.5 Hours
Problem 6 3 Hours 3 Hours
Problem 7 4 Hours 3.5 Hours

 

Qualitative Problems

1. Transportation

Step 1: Define the Problem

1. Consider two products with the same margin carried by a retail store. Any leftover units of one product are worthless. Leftover units of the other product can be sold to outlet stores. Which product should have a higher level of availability? Why? 2. What transportation challenges does online grocer Peapod face? Compare transportation costs at online grocers and supermarket chains.

Step 2: Create a Plan

1. Determine which product should have a higher level of availability 2. Research and describe the challenges online grocer Peapod faces

Step 3: Execute the Plan

1. Determine which product should have a higher level of availability The leftover units of a product which become worthless at the end of the selling season should have a higher level of product availability than the leftover units of the other product that can be sold to outlet stores because unsold items for this product are useless and no salvage value. Maintaining higher level of availability for this product will help a firm match demand uncertainty and maximizes profits.

2. Research and describe the challenges online grocer Peapod faces Peapod is responsible for congestion in the releasing area and has to bear a high-priced outbound transportation costs. These kinds of facilities were not available with traditional grocers. Peapod delivers its items in trucks that are climate controlled. These kinds of trucks should be offered with the incentive facilities for peak and off-peak seasons for the goods to be delivered at the right time to the customers. Customers are aware of the transportation constituent of their purchase and Peapod can encourage customers to purchase products of a higher amount. Peapod and traditional grocers need to pay inbound transportation charges for their merchandise. There does not seem to be a large margin in this unless a trader has a market share to gain cost benefits.

Step 4: Check your Work

Using the book as reference and reading over the chapters. I can be sure my assumptions made for this answer are correct with minimal amounts of error.

2. Facilities/Network Design

Step 1: Define the Problem

1. How do the location and size of warehouses affect the performance of a firm such as Amazon.com? What factors should Amazon.com take into account when making this decision? 2. Amazon.com has built new warehouses as it has grown. How does this change affect various cost and response times in the Amazon.com supply chain? 3. Consider a firm such as Dell, with very few production facilities worldwide. List the pros and cons of this approach and why it may or may not be suitable for the computer industry.

Step 2: Create a Plan

1. How do location and size of warehouses affect Amazon.com performance and what factors should be taken into account when making these decisions? 2. How does new warehouses affect Amazon.com response time and costs? 3. What are the pros and cons of having very few production facilities worldwide?

Step 3: Execute the Plan

1. How do location and size of warehouses affect Amazon.com performance and what factors should be taken into account when making these decisions? Responsiveness and efficiency of any company depends upon its size and location of warehouses. It was a time when Amazon used to run its bookstore with only one warehouse, which was established in Seattle. This warehouse was not capable of meeting the demands of customers as they were ineffective in delivering the products at given time. Due to single warehouse, its cost increases with less responsiveness of customers. Since the time, they supplemented warehouses in different locations for distribution purpose, their demand increased at faster rate. This spreading of warehouses helped the customers to get products on time due to proximity of warehouses, where shipment can be done from locations nearer to the customer. Amazon should work on areas underserved, which can prove to be profitable, responsive and efficient from the company’s point of view.

2. How does new warehouses affect Amazon.com response time and costs? With the change in figure of locations, facilities and capacities, there is a change in logistic and facility cost. Amazon’s cost increases with increase in the warehouses at different locations. This cost includes inventory, facility and logistics cost. This result in higher fixed cost that can be utilized for diminishing transportation cost. Fixed cost can be reduced by scattering the warehouses at different locations for distribution purpose, which helps in adding the responsiveness at same expenditure or same responsiveness at less cost. Inventory cost can be reduced by distributing the warehouses at different locations. Inventory costs also changes with an increased number of warehouses.

3. What are the pros and cons of having very few production facilities worldwide? The greatest benefit the Dell has is that it can be located in a large number of countries, which will help in reducing facility cost by removing tariffs and lessening risk of demand and exchange rate. The biggest drawback with Dell is its lack of responsiveness from customers. Customers wait for their order, because they know that it will take enough time to proceed. Orders are further delated when shipped from other production facilities. Orders are further delayed when shipped form other production facilities that are listed on the web site. Cost of shipping may be an area of concern for customers, but they worth provided by Dell is quite high that this shipping cost seems negligible.

Step 4: Check your Work

Using the book as a reference and the information we learned from lectures. I can say that the answers I have put for these discussion questions are correct.

Quantitative Problems

3. Chapter 11: Exercise 11.5

Step 1: Define the Problem

1. Return to the Sam’s Club store in Exercise 4. Assume that the supply lead time from HP is normally distributed, with a mean of 2 weeks and a standard deviation of 1.5 weeks. How much safety inventory should Sam’s Club carry if it wants to provide a CSL of 95 percent? How does the required safety inventory change as the standard deviation of lead time is reduced from 1.5 weeks to zero in intervals of 0.5 weeks?

Step 2: Create a Plan

1. Determine how much safety inventory should Sam’s Club carry if it wants to provide a CSL of 95 percent. How does the required safety inventory change as standard deviation of lead time is reduced from 1.5 to zero in intervals of 0.5 per week

Step 3: Execute the plan

Given information Average demand per period, D = 250 Standard deviation of demand per period, σD = 150 Lead time, L = 2 weeks Reorder point, ROP = 600 Lot size, Q = 1,000 Cycle service level, CSL = 0.95 Standard deviation of lead time, sL = 1.5 weeks

1. Determine how much safety inventory should Sam’s Club carry if it wants to provide a CSL of 95 percent. How does the required safety inventory change as standard deviation of lead time is reduced from 1.5 to zero in intervals of 0.5 per week Standard deviation of lead time 1.5 weeks Expected demand during L periods, DL = L x D = 2 x 250 = 500 Standard deviation of demand during lead time = σL = = = = 430.842 Safety inventory, ss = = 1.65 x 431 = 708.672 Safety inventory of 709 HP printers should be carried by Sam’s Club Standard deviation of lead time 1 week σL = = = = 327.871 Safety inventory, ss = = 1.65 x 328 = 539.300 Safety inventory of 539 HP printers should be carried by Sam’s Club Standard deviation of lead time 0.5 week Standard deviation of demand during lead time = σL = = = = 246.221 Safety inventory, ss = = 1.65 x 246 = 404.997 Safety inventory of 405 HP printers should be carried by Sam’s Club Standard deviation of lead time 0 week Standard deviation of demand during lead time = σL = = = = 212.123 Safety inventory, ss = = 1.65 x 212 = 348.708 Safety inventory of 349 HP printers should be carried by Sam’s Club

Step 4: Check your Work

Using the book as reference for formulas as well as using lecture notes. I believe that the numbers I have calculated for the safety inventory is correct

4. Safety Inventory (Aggregation)

Step 1: Define the Problem

1. Epson produces printers in its Taiwan factory for sale in Europe. Printers sold in different countries differ in terms of the power outlet as well as the language of the manuals. Currently, Epson assembles and packs printers for sale in individual countries. This distribution of weekly demand in different countries is normally distributed, with means and stand deviations as shown in Table 11-6. Assume demand in different countries to be independent. Given that the lead time from the Taiwan factory is eight weeks, how much safety inventory does Epson require in Europe if it targets a CSL of 95 percent? Epson decides to build a central DC in Europe. It will ship base printers (without power supply) to the DC. When an order is received, the DC will assemble power supplies, add manuals, and ship the printers to the appropriate country. The base printers are still to be manufactured in Taiwan with a lead time of eight weeks. How much saving of safety inventory can Epson expect as a result? 2. Return to the Epson data in Exercise 7. Each printer costs Epson $200, and the holding cost is 25 percent. What saving in holding cost can Epson expect as a result of building the European DC? If final assembly in the European DC adds $5 to the production cost of each printer, would you recommend the move? Suppose that Epson is able to cut the production and delivery lead time from its Taiwan factory to four weeks using good information systems. How much savings in holding cost can Epson expect without the European DC? How much savings in holding cost can the firm expect with the European DC?

Step 2: Create a Plan

1. Assume demand in different countries to be independent. Given that the lead time from the Taiwan factory is eight weeks, how much safety inventory does Epson require in Europe if it targets a CSL of 95 percent? Epson decides to build a central DC in Europe. It will ship base printers (without power supply) to the DC. When an order is received, the DC will assemble power supplies, add manuals, and ship the printers to the appropriate country. The base printers are still to be manufactured in Taiwan with a lead time of eight weeks. How much saving of safety inventory can Epson expect as a result? 2. What saving in holding cost can Epson expect as a result of building the European DC? If final assembly in the European DC adds $5 to the production cost of each printer, would you recommend the move? Suppose that Epson is able to cut the production and delivery lead time from its Taiwan factory to four weeks using good information systems. How much savings in holding cost can Epson expect without the European DC? How much savings in holding cost can the firm expect with the European DC?

Step 3: Execute the Plan

1. Assume demand in different countries to be independent. Given that the lead time from the Taiwan factory is eight weeks, how much safety inventory does Epson require in Europe if it targets a CSL of 95 percent? Demand during lead time, DL = D x L Standard deviation of demand during lead time, σL = k = number of countries Safety inventory, ss =

Weekly Demand for Epson Printers in Europe      
Country Mean Demand Standard Deviation Lead time Demand during lead time, DL Std of demand during lead time, σL Safety inventory, ss
France 3,000 2,000 8 24,000 5,656.85 9,304.70
Germany 4,000 2,200 8 32,000 6,222.54 10,235.17
Spain 2,000 1,400 8 16,000 3,959.80 6,513.29
Italy 2,500 1,600 8 20,000 4,525.48 7,443.76
Portugal 1,000 800 8 8,000 2,262.74 3,721.88
UK 4,000 2,400 8 32,000 6,788.23 11,165.64
            48,384

The amount of safety inventory that Epson requires in Europe with a CSL of 95 percent is 48,384 units

Epson decides to build a central DC in Europe. It will ship base printers (without power supply) to the DC. When an order is received, the DC will assemble power supplies, add manuals, and ship the printers to the appropriate country. The base printers are still to be manufactured in Taiwan with a lead time of eight weeks. How much saving of safety inventory can Epson expect as a result? First we would have to calculate the variance of standard deviation by square rooting each country standard deviation of demand. Then taking the sum of each countries variance and finding the average. Then we calculate the standard deviation of average demand, . Then we can calculate the value of central demand, . Finally, we can calculate the safety inventory, ss =

Weekly demand for Epson printers in Europe        
Country Mean Demand Standard deviation of demand Variance Average of demand Standard deviation of average demand, Value of central demand, Safety inventory, ss
France 3,000 2,000 4,000,000  

 

 

329,333.33

  

 

 

1,814.75

  

 

 

4,445

  

 

 

20,861
Germany 4,000 2,200 4,840,000        
Spain 2,000 1,400 1,960,000        
Italy 2,500 1,600 2,560,000        
Portugal 1,000 800 640,000        
UK 4,000 2,400 5,760,000        

If Epson decides to build a central DC in Europe the company needs to maintain a safety inventory of 20,861 units.

2. What saving in holding cost can Epson expect as a result of building the European DC? We know that the safety inventory with decentralized distribution is 48,384 units and safety inventory with centralization is 20,861 units. Savings = (SSwithoutDC + SSwithDC) x unit cost x holding rate Savings = (48,384 – 20,681) x 200 x 0.25 = $1,385,150 savings with a distribution center If final assembly in the European DC adds $5 to the production cost of each printer, would you recommend the move? Increase in total cost = weekly demand x 52 weeks x increased amount Increase in total cost = 16,500 x 52 x 5 = $4,290,000 Since the increase in cost is more than the savings it is not recommend to move

Suppose that Epson is able to cut the production and delivery lead time from its Taiwan factory to four weeks using good information systems. How much savings in holding cost can Epson expect without the European DC? How much savings in holding cost can the firm expect with the European DC?

Weekly Demand for Epson Printers in Europe      
Country Mean Demand Standard Deviation Lead time Demand during lead time, DL Std of demand during lead time, σL Safety inventory, ss
France 3,000 2,000 4 12,000 4,000 6,600
Germany 4,000 2,200 4 16,000 4,400 7,260
Spain 2,000 1,400 4 8,000 2,800 4,620
Italy 2,500 1,600 4 10,000 3,200 5,280
Portugal 1,000 800 4 4,000 1,600 2,640
UK 4,000 2,400 4 16,000 4,800 7,920
Total           34,320

Savings = (SS8week – SS4week) x unit cost x holding rate Savings = (48,384 – 34,320) x 200 x 0.25 = $2,419,200 savings with reduced lead time with a distribution center

Weekly demand for Epson printers in Europe        
Country Mean Demand Standard deviation of demand Variance Average of demand Standard deviation of average demand, Value of central demand, Safety inventory, ss
France 3,000 2,000 4,000,000  

 

 

329,333.33

  

 

 

1,814.75

  

 

 

4,445

  

 

 

14,669
Germany 4,000 2,200 4,840,000        
Spain 2,000 1,400 1,960,000        
Italy 2,500 1,600 2,560,000        
Portugal 1,000 800 640,000        
UK 4,000 2,400 5,760,000        

Savings = (SS8weeks – SS4weeks) x unit cost x holding rate Savings = (20,684 – 14,669) x 200 x 0.25 = $300,750 savings with a distribution center

Step 4: Check your Work

Using the book as a reference for formulas on how to solve for the savings and other equations as well as using lecture notes to calculate the needed information. I believe that the numbers I have calculated are correct.

 

5. SC Facilities/Network Optimization for SunOil

Step 1: Define the Problem

1. Work though the SunOil Case Study in Chapter 5 (4th Edition) using Excel and Solver

Step 2: Create a Plan

1. Work though the SunOil Case Study using Excel and Solver

Step 3: Execute the Plan

1. Work though the SunOil Case Study using Excel and Solver

Cost and Demand Data for SunOil  This shows annual demand for each of the give regions. Cell D5 shows that it costs $92,000 to produce 1 million units in North America and sell them to South America.

Inputs for capacitated plant location are: n = number of potential plant locations/capacity (each level of capacity will count as a separate location) m = number of markets or demand points Dj = annual demand from market j Ki = potential capacity of plant i fi = annualized fixed cost of keeping factory i open cij = cost of producing and shipping one unit from factory i to market j (cost includes production, inventory, transportation, and tariffs) yi = 1 if plant i is open, 0 otherwise xij = quantity shipped from plant i to market j

Spreadsheet Area for Decision Variables

The decision variables determine the amount produced in a supply region and shipped to a demand region.

Constraint and Object Function  Cells B22:B26 contain the capacity constraints and cells B28:F28 contain the demand constraints. The object function is shown in cell B31 and measure the total fixed cost plus the variable cost of operating the network

Optimal Regional Network Configuration for SunOil  

This shows that lowest-cost network will have facilities in South America, Asia and Africa. Further, a high-capacity plant should be planned in each region. The plant in South America meets the North American demand, whereas the European demand is met from plants in Asia and Africa

Step 4: Check your Work

Carefully going through each step of the SunOil Case Study the implementations is all correct. Except for the fact that I have unknown decimal values in some of the cells. The answer still came out the be correct so I assume that Excel Solver did something that made it like that.

6. Facility Design for Dry Ice, Inc.

Step 1: Define the Problem

1. DryIce, Inc., is a manufacture of air conditioners that has seen its demand grow significantly. The company anticipates nationwide demand for the year 2006 to be 180,000 units in the South, 120,000 units in the Midwest, 110,000 units in the East, and 100,000 units in the West. Managers at DryIce are designing the manufacturing network and have selected four potential sites – New York, Atlanta, Chicago, and San Diego. Plants could have a capacity of either 200,000 or 400,000 units. The annual fixed costs at the four locations are shown in table 5-6, along with the costs of producing and shipping an air conditioner to each of the four markets. Where should DryIce build its factories and how large should they be?

Step 2: Create a Plan

1. Determine from the data given where DryIce should build its factories and how large they should be

Step 3: Execute the Plan

1. Determine from the data given where DryIce should build its factories and how large they should be Inputs: n = number of plant locations per capacity m = number of demand points Dj = annual demand from market j Ki = potential capacity of plant i fi = fixed costs associated with keeping plant i cij = the production and transportation cost from site i to region j

Using Excel and Solver   From the results we can see that building plants at Chicago and San Diego of capacity 100,000 and 410,000 respectively will be able to fulfill the demand at minimum cost. The annual total cost in terms of plants and transportation is $168,290,000.

Step 4: Check your Work

Using the book and lecture notes as a guide and reference. And following steps on using excel solver from the book. I can assume that the numbers that has been created by solver is correct.

 

7. Transportation for Books-on-Line

Step 1: Define the Problem

1. Books-on-Line, an online bookseller, charges its customers a shipping charge of $4 for the first book and $1 for each additional book. The average customer order contains four books. Books-on-Line currently has one warehouse in Seattle and ships all orders from there. For shipping purposes, Books-on-Line divides the United States into three zones – Wester, Central, and Easter. Shipping costs incurred by Books-on-Line per customer order is $2 within the same zone, $3 between adjacent zones, and $4 between nonadjacent zones. Weekly demand from each zone is independent and normally distributed, with a mean of 50,000 and a standard deviation of 25,000. Each book costs on average $10, and the holding cost incurred by Books-on-Line is 25 percent. Books-on-Line replenishes inventory every week and aims for a 99.7 percent CSL. Assume a replenishment lead time of one week. A warehouse is designed to carry 50 percent more than the replenishment order + safety stock. The fixed cost of a warehouse is $200,000 + x, where x is its capacity in books. The weekly operating cost of a warehouse is $0.01y, where y is the number of books shipped. Books-on-Line is planning its network strategy. Which zones should have warehouses? Detail all costs involved.

Step 2: Create a Plan

1. Create a detailed structure for Books-on-Line for which zone they should have warehouses

Step 3: Execute the Plan

1. Create a detailed structure for Books-on-Line for which zone they should have warehouses

Given information Weekly mean demand, Dm = 50,000 Standard deviation, = 25,000 Holding Cost, h = 0.25 Replenishment lead time, L = 1 week Reorder interval, T = 1 week Cycle service level, CSL = .997 Average cost per book, p = $10 Fixed cost = 200,000 + x Weekly Operating cost = 0.01y

Warehouses only in the Western Zone

50,000 books are shipped to each zone from the Western zone Safety inventory, ss = = 2.76 x = 168,267 Shipment cost for each zone differs Cost of shipment from Western zone to Eastern zone is $4 per order = 50,000 x 4/4 = 50,000 Cost of shipment from Western zone to Central zone is $3 per order = 50,000 x 3/4 = 37,500 Cost of shipment from Western zone to Western zone is $2 per order = 50,000 x 2/4 = 25,000 Since the warehouse can carry 50 percent more than the replenishment order + safety inventory. We can calculate this by (200,000 + 1.5 x (168,267 + 150,000))/52 = 13,027 We can calculate weekly operating cost by, 0.01(150,000) = 1,500 Cycle inventory cost = = / 52 = 3,606 Safety inventory cost = ss x hC = 168,267 x .25 x 10 /52 = 8,090 The total weekly cost of setting up the warehouse only in the Western zone is $138,722

  Eastern Central Western
Eastern 0 0 0
Central 0 0 0
Western 50,000 50,000 50,000
Total Zone Demand 50,000 50,000 50,000
       
Safety Inventory     168,267
       
Shipment Cost      
Eastern 0 0 0
Central 0 0 0
Western 50,000 37,500 25,000
       
Weekly Warehouse fixed cost     13,027
Warehouse operating cost     1,500
       
Weekly cycle inventory cost     3,606
Weekly safety inventory cost     8,090
       
Total weekly cost 50,000 37,500 51,222

 

Warehouse only in the Central Zone

50,000 books are shipped to each zone from the Central zone Safety inventory, ss = = 2.76 x = 168,267 Shipment cost for each zone differs Cost of shipment from Central zone to Eastern zone is $3 per order = 50,000 x 3/4 = 37,500 Cost of shipment from Central zone to Central zone is $2 per order = 50,000 x 2/4 = 25,000 Cost of shipment from Central zone to Western zone is $2 per order = 50,000 x 3/4 = 37,500 Since the warehouse can carry 50 percent more than the replenishment order + safety inventory. We can calculate this by (200,000 + 1.5 x (168,267 + 150,000))/52 = 13,027 We can calculate weekly operating cost by, 0.01(150,000) = 1,500 Cycle inventory cost = = / 52 = 3,606 Safety inventory cost = ss x hC = 168,267 x .25 x 10 /52 = 8,090 The total weekly cost of setting up the warehouse only in the Central zone is $126,222

  Eastern Central Western
Eastern 0 0 0
Central 50,000 50,000 50,000
Western 0 0 0
Total Zone Demand 50,000 50,000 50,000
       
Safety Inventory   168,267  
       
Shipment Cost      
Eastern 0 0 0
Central 37,500 25,000 37,500
Western 0 0 0
       
Weekly Warehouse fixed cost   13,027  
Warehouse operating cost   1,500  
       
Weekly cycle inventory cost   3,606  
Weekly safety inventory cost   8,090  
       
Total weekly cost 37,500 51,222 37,500

 

Warehouse in each Zone

50,000 books are shipped to each zone from each zone Safety inventory, ss = = 2.76 x = 97,149 Shipment cost for each zone differs Cost of shipment from Eastern zone to Eastern zone is $3 per order = 50,000 x 2/4 = 25,000 Cost of shipment from Central zone to Central zone is $2 per order = 50,000 x 2/4 = 25,00 Cost of shipment from Western zone to Western zone is $2 per order = 50,000 x 2/4 = 25,000 Since the warehouse can carry 50 percent more than the replenishment order + safety inventory. We can calculate this by (200,000 + 1.5 x (97,149 + 50,000))/52 = 8,091 We can calculate weekly operating cost by, 0.01(50,000) = 500 Cycle inventory cost = = / 52 = 1,202 Safety inventory cost = ss x hC = 97,149 x .25 x 10 /52 = 4,671 The total weekly cost of setting up the warehouse in each zone is $118,390

  Eastern Central Western
Eastern 50,000 0 0
Central 0 50,000 0
Western 0 0 50,000
Total Zone Demand 50,000 50,000 50,000
       
Safety Inventory 97,149 97,149 97,149
       
Shipment Cost      
Eastern 25,000 0 0
Central 0 25,000 0
Western 0 0 25,000
       
Weekly Warehouse fixed cost 8,091 8,091 8,091
Warehouse operating cost 500 500 500
       
Weekly cycle inventory cost 1,202 1,202 1,202
Weekly safety inventory cost 4,671 4,671 4,671
       
Total weekly cost 39,463 39,463 39,463

Since the total cost of setting up warehouse in each zone is the lowest, it is recommended to have warehouse in each zone.

Step 4: Check your Work

Using the book as a reference for formulas and step by step from examples. I can say that the numbers I have calculated are correct.

Explain why it is useful to describe group work in terms of the time/place framework.

/in /by

Chapter 10:

1. Based upon the current state of the art of robotics applications, which industries are most likely to embrace robotics? Why?

2. Watch the following two videos: https://www. youtube.com/watch?v=GHc63Xgc0-8 and https://www.youtube.com/watch?v=ggN8wCWSIx4 for a different view on impact of AI on future jobs. What are your takeaways from these videos? What is the more likely scenario in your view? How can you prepare for the day when humans indeed may not need to apply for many jobs?

E1. Identify applications other than those discussed in this chapter where Pepper is being used for commercial and personal purposes.

E7. Conduct research to identify the most recent developments in self-driving cars.

Chapter 11:

1. Explain why it is useful to describe group work in terms of the time/place framework.

2. Describe the kinds of support that groupware can provide to decision makers.

3. Explain why most groupware is deployed today over the Web.

4. Explain in what ways physical meetings can be inefficient. Explain how technology can make meetings more effective.

E4. Compare Simon’s four-phase decision-making model to the steps in using GDSS.

Assignment:

Portfolio Project: In this assignment discuss (in a 2 page essay) a current business process in a specific industry.  Note the following:

-The current business process itself.

-The industry the business process is utilized.

After explaining the current situation, take the current learning from the course and:

· Explain a new technology that the business should deploy.  Be specific; don’t only note the type of technology but the specific instance of technology.  (For example, a type of technology is smart automation a specific type of automation is automated light-dimming technology).

· Note the pros and cons of the technology selected.

· Note various factors the business should consider prior to deploying the new technology

The above submission should be two pages in length.  Remember the total length does not include the APA approved cover page or the references.  There should be at least three APA approved references to support your work.