WebAssign is not supported for this browser version. Some features or content might not work. System requirements

WebAssign

Welcome, demo@demo

(sign out)

Tuesday, April 1, 2025 05:30 EDT

Home My Assignments Grades Communication Calendar My eBooks

My Cengage Home Notifications Help My Account

Cengage Testing Assignments, Fall 2019
My Assignments

Ragsdale - Spreadsheet Modeling & Decision Ana 9/e (Homework)

James Finch

Cengage Testing Assignments, Fall 2019

Instructor: Sarah Anders

Current Score : 11 / 102

Due : Tuesday, December 30, 2025 11:00 EST

Last Saved : n/a Saving... ()

Ask Your Teacher Extension Requests Print Assignment

Question

Points

1	2	3	4	5	6	7
7/8	1/9	0/11	0/5	0/47	3/5	0/17

Total

11/102 (10.8%)

Instructions

Clearly present important spreadsheet and business analytics skills with Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Business Analytics, 9e, written by spreadsheet instructional innovator and business analytics leader Cliff Ragsdale. Your students master today's most widely used business analytics techniques as they become proficient with the latest Excel® capabilities and functions in Microsoft® Office 365 or Office 2019. A new full-color presentation and succinct instructions highlight commonly used business analytics techniques and demonstrate how to implement these tools using the latest version of Excel®. Students develop both algebraic and spreadsheet modeling skills. This edition features Frontline Systems' Analytic Solver® and Data Mining add-ins for performing optimization, simulation and decision analysis, data mining and predictive analytics in Excel®. WebAssign digital resources provide customizable teaching tools and author-created videos.

Question 1 is a randomized multipart question that contains graphs of the feasible region with randomized values for a seamless experience for the student.

Question 2 is a randomized multipart question that features grading for an objective function as well as constraint inequalities.

Question 3 accepts alternative optima as an ordered list that satisfies constraints developed by the student.

Question 4 allows students to enter a simple linear regression model and then use this model to make a prediction.

Question 5 gives randomized time series data where students are to develop a quadratic regression function and then use this to calculate seasonal predictions and indices.

Question 6 guides students through the decision analysis process.

Question 7 covers basic Excel functionality that will be useful throughout the textbook. This demo assignment allows many submissions and allows you to try another version of the same question for practice wherever the problem has randomized values.

Assignment Submission

For this assignment, you submit answers by question parts. The number of submissions remaining for each question part only changes if you submit or change the answer.

Assignment Scoring

Your last submission is used for your score.

1. 7/8 points | Previous Answers RagSMDA9 2.E.013. My Notes

Bibbins Manufacturing produces softball and baseballs for youth recreation leagues. Each softball costs $11 to produce and sells for $16 while each baseball costs $11.25 and sells for $15. The material and labor required to produce each item is listed here along with the availability of each resource. The company wants to maximize its profit. (Let X₁ = number of softballs to produce and X₂ = number of baseballs to produce.)

Amount Required Per			Amount
Resource	Softball	Baseball	Available
Leather	5 oz	4 oz	6,300 oz
Nylon	6 yds	3 yds	5,400 yds
Core	4 oz	2 oz	4,000 oz
Labor	2.5 min	2 min	3,500 min
Stitching	1 min	1 min	1,500 min

(a)

Formulate an LP model for this problem to maximize profit (in dollars).

MAX:

5X1+3.75X2

$webMathematica generated answer key$ Subject to: Leather (in oz)

5X1+4X2≤6300

$webMathematica generated answer key$ Nylon (in yds)

6X1+3X2≤5400

$webMathematica generated answer key$ Core (in oz)

4X1 + 2X2≤4000

$webMathematica generated answer key$ Labor (in min)

2.5X1+2X2≤3500

$webMathematica generated answer key$ Stitching (in min)

X1 + X2≤15

X₁, X₂ ≥ 0

(b)

Sketch the feasible region.

A graph has a horizontal axis labeled "X₁" with values from 0 to 2,000 and a vertical axis labeled "X₂" with values from 0 to 2,000. There are 5 lines and a shaded region on the graph.
The line labeled "Leather" enters the window at approximately X₂ = 1,580 on the positive X₂ axis, goes down and right, and exits the window at X₁ = 1,260 on the positive X₁ axis.
The line labeled "Nylon" enters the window at X₂ = 1,800 on the positive X₂ axis, goes down and right, and exits the window at X₁ = 900 on the positive X₁ axis.
The line labeled "Core" enters the window at X₂ = 2,000 on the positive X₂ axis, goes down and right, and exits the window at X₁ = 1,000 on the positive X₁ axis.
The line labeled "Labor" enters the window at X₂ = 1,750 on the positive X₂ axis, goes down and right, and exits the window at X₁ = 1,400 on the positive X₁ axis.
The line labeled "Stitching" enters the window at X₂ = 1,500 on the positive X₂ axis, goes down and right, and exits the window at X₁ = 1,500 on the positive X₁ axis.
The shaded region is below the lines labeled "Leather" and "Nylon", above the horizontal axis, and right of the vertical axis.

(c)

What is the optimal solution?

(X₁, X₂) =

300,1200

$webMathematica generated answer key$

Viewing Saved Work Revert to Last Response

2. 1/9 points | Previous Answers RagSMDA9 3.E.013. My Notes

A furniture manufacturer produces two types of tables (country and contemporary) using three types of machines. The time required to produce the tables on each machine is given in the following table.

Machine	Country	Contemporary	Total Machine Time Available per Week
Router	1.5	2.0	1,000
Sander	3.0	4.5	1,700
Polisher	2.5	1.5	1,400

Country tables sell for $350 and contemporary tables sell for $450. Management has determined that at least 20% of the tables made should be country and at least 30% should be contemporary. How many of each type of table should the company produce if it wants to maximize its revenue? (Let X₁ be the number of country tables to produce and X₂ the number of contemporary tables to produce.)

(a)

Formulate an LP model for this problem to maximize revenue (in dollars).

MAX:

350X1+450X2

$webMathematica generated answer key$ Subject to: Router

$webMathematica generated answer key$ Sander

$webMathematica generated answer key$ Polisher

$webMathematica generated answer key$ Minimum country tables

5√3

$webMathematica generated answer key$ Minimum contemporary tables

$webMathematica generated answer key$

X₁, X₂ ≥ 0

(b)

Create a spreadsheet model for this problem and solve it using Solver. How many of each type of table should the company produce if it wants to maximize revenue? (Round your answers to two decimal places.)

(X₁, X₂) =

(c)

What is the maximum revenue (in dollars) for the optimal solution? (Round your answer to the nearest dollar.)

187,246

(d)

How will your spreadsheet model differ if there are 25 types of tables and 15 machine processes involved in manufacturing them?

The spreadsheet model would have only more variables. correct

The spreadsheet model would have more variables and constraints. The spreadsheet model would have only more constraints. The spreadsheet model would not change.

Viewing Saved Work Revert to Last Response

3. 0/11 points | Previous Answers RagSMDA9 6.E.011. My Notes

Garden City Beach is a popular summer vacation destination for thousands of people. Each summer, the city hires temporary lifeguards to ensure the safety of the vacationing public. Garden City's lifeguards are assigned to work five consecutive days each week and then have two days off. However, the city's insurance company requires them to have at least the following number of lifeguards on duty each day of the week.

	Minimum Number of Lifeguards Required Each Day
	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
Lifeguards	18	17	16	16	16	15	19

The city manager would like to determine the minimum number of lifeguards that will have to be hired.

(a)

Formulate an ILP for this problem. (Let X₁ be the number of lifeguards whose shift starts on Sunday, X₂ be the number of lifeguards whose shift starts on Monday, …, and X₇ be the number of lifeguards whose shift starts on Saturday.)

MIN:

X1+X2

$webMathematica generated answer key$ Subject to: Sunday constraint

Monday constraint

Tuesday constraint

Wednesday constraint

Thursday constraint

Friday constraint

Saturday constraint

all X_i ≥ 0
all X_i must be integers

(b)

Implement your model in a spreadsheet and solve it.

(X₁, X₂, X₃, X₄, X₅, X₆, X₇) =

(c)

What is the optimal solution?

The optimal solution is to use a total of lifeguards.

(d)

Several lifeguards have expressed a preference to be off on Saturdays and Sundays. What is the maximum number of lifeguards that can be off on the weekend without increasing the total number of life guards required?

lifeguard(s)

Viewing Saved Work Revert to Last Response

4. 0/5 points | Previous Answers RagSMDA9 9.E.012. My Notes

The O-rings in the booster rockets on the space shuttle are designed to expand when heated to seal different chambers of the rocket so that solid rocket fuel is not ignited prematurely. According to engineering specifications, the O-rings expand by some amount, say at least 5%, in order to ensure a safe launch. Hypothetical data on the amount of O-ring expansion and the atmospheric temperature in Fahrenheit at the time of several different launches are given in the file O-ring.xlsx.

(a)

Prepare a scatter plot of the data. Does there appear to be a linear relationship between these variables?

Yes, there does appear to be a linear relationship. No, there appears to be no relationship. No, there appears to be a nonlinear relationship.

(b)

Obtain a simple linear regression model to estimate the amount of O-ring expansion as a function of atmospheric temperature. What is the estimated regression function? (Let X₁ represent the temperature in Fahrenheit and Y represent the percentage of O-ring expansion. Round your numerical values to three decimal places.)

(c)

Interpret the R² statistic for the model you obtained. (Enter your answer as a percent. Round your answer to one decimal place.)

The R² statistic indicates that approximately % of the total variation in the percentage of O-ring expansion is accounted for by temperature.

(d)

Suppose that NASA officials are considering launching a space shuttle when the temperature is 23 degrees. What amount of O-ring expansion (in %) should they expect at this temperature according to your model? (Round your answer to two decimal places.)

(e)

On the basis of your analysis of these data, would you recommend that the shuttle be launched if the temperature is 23 degrees? Why or why not?

No, our estimate in part (d) is below 5% expansion. correct

No, our estimate in part (d) is an example of extrapolation and we can not be sure the model will be accurate for temperatures this low. Yes, our estimate in part (d) is above 5% expansion. Yes, the scatter plot shows a linear relationship between temperature and O-ring expansion. No, the scatter plot shows no relationship between temperature and O-ring expansion. No, the scatter plot shows a nonlinear between temperature and O-ring expansion.

Viewing Saved Work Revert to Last Response

5. 0/47 points | Previous Answers RagSMDA9 11.E.036. My Notes

Question Part

Points

Submissions Used

–/1

0/1

–/1

0/100

1/100

0/100

Total

0/47

Consider the following data representing 2 years of monthly health insurance claims for a self-insured company. Use regression analysis to fit a quadratic trend model to the data set below.

Year	Month	Time Period	Time Period^2	Claims
2015	1	1	1	10,149
	2	2	4	11,165
	3	3	9	12,320
	4	4	16	12,456
	5	5	25	13,233
	6	6	36	16,422
	7	7	49	17,385
	8	8	64	14,243
	9	9	81	14,596
	10	10	100	12,959
	11	11	121	14,000
	12	12	144	14,775
2016	1	13	169	12,310
	2	14	196	13,214
	3	15	225	13,626
	4	16	256	13,649
	5	17	289	16,422
	6	18	324	17,324
	7	19	361	19,595
	8	20	400	19,007
	9	21	441	15,961
	10	22	484	15,730
	11	23	529	16,909
	12	24	576	18,951

(a)

What is the estimated regression function? (Use t for time period and t² for (time period)². Round your answers to four decimal places.)

_t =

(b)

Compare the adjusted-R² value for this model to that of the linear trend model. What is implied by this comparison? (Round your answers to three decimal places.)

The adjusted-R² value for the quadratic model is . The adjusted-R² value for the linear model is . These results suggest that the quadratic model is an improvement over the linear model.

(c)

Prepare a line graph comparing the quadratic trend predictions against the original data.

A line graph has a horizontal axis labeled "Time Period" with values from 0 to 25 and a vertical axis labeled "Monthly Claims" with values from 8,000 to 20,000. A quadratic trend curve and the data points are imposed on the graph.
There are 24 data points plotted in the graph. A pattern goes up and right becoming more steep from (1, 10,100) to (24, 19,600).

The quadratic trend curve begins at the approximate point (1, 11,088) goes up and right becoming more steep and ends at the approximate point (25, 19,151)

(d)

What are the forecasts, in dollars, for each of the first 6 months in 2017 using quadratic trend model? (Round your answers to the nearest dollar.)

Year	Month	Trend
2017	1	$
	2	$
	3	$
	4	$
	5	$
	6	$

(e)

Calculate multiplicative seasonal indices for each month using the results of the quadratic trend model. (Express your answers as a percentage. Round your answer to two decimal place)

Seasonal Factors
1	%
2	%
3	%
4	%
5	%
6	%
7	%
8	%
9	%
10	%
11	%
12	%

(f)

Use these seasonal indices to compute seasonal forecasts, in dollars, for each of the first 6 months in 2017. (Round your answers to the nearest dollar.)

Year	Month	Trend
2017	1	$
	2	$
	3	$
	4	$
	5	$
	6	$

(g)

Calculate additive seasonal indices, in dollars, for each month using the results of the quadratic trend model. (Round your answers to the nearest cent.)

Seasonal Factors
1	$
2	$
3	$
4	$
5	$
6	$
7	$
8	$
9	$
10	$
11	$
12	$

(h)

Use these seasonal indices to compute seasonal forecasts, in dollars, for each of the first 6 months in 2017. (Round your answers to the nearest dollar.)

Year	Month	Trend
2017	1	$
	2	$
	3	$
	4	$
	5	$
	6	$

Viewing Saved Work Revert to Last Response

6. 3/5 points | Previous Answers RagSMDA9 14.E.005. My Notes

Lori Henderson runs a specialty ski clothing shop outside of Boone, North Carolina. She must place her order for ski parkas well in advance of ski season because the manufacturer produces them in the summer months. Lori needs to determine whether to place a large, medium, or small order for parkas. The number sold will depend largely on whether the area receives a heavy, normal, or light amount of snow during the ski season. The following table summarizes the payoffs Lori expects to receive under each scenario.

	Amount of Snow
Size of Order	Heavy	Normal	Light
Large	18	15	11
Medium	16	16	14
Small	12	12	12

Lori estimates the probability of heavy, normal, and light snowfalls as 0.27, 0.56, and 0.17, respectively.

(a)

What decision should be made according to the maximax decision rule?

Small Order Medium Order correct

Large Order

(b)

What decision should be made according to the maximin decision rule?

Small Order correct

Medium Order Large Order

(c)

What decision should be made according to the minimax regret decision rule?

Small Order correct

Medium Order Large Order

(d)

What decision should be made according to the EMV decision rule?

Small Order correct

Medium Order Large Order

(e)

What decision should be made according to the EOL decision rule?

Small Order Medium Order Large Order

Viewing Saved Work Revert to Last Response

7. 0/17 points | Previous Answers RagSMDA9 A.ET.004. My Notes

Microsoft Excel File

Introduction

Frequently, you will have data that, instead of being numerical, is text based. The data could be names of people, businesses, or longer form text you would like to mine for information. While Excel is primarily designed to deal with and manipulate numbers, it also has a set of tools that help you work with text information efficiently and easily. This tutorial is designed to familiarize you with those tools in a common and easy to understand situation.

The data has been collected in the Microsoft Excel file. Open the spreadsheet and perform the required analysis to answer the questions below.
Common Text Functions

Functions such as PROPER, LEN, LEFT, and RIGHT allow you to clean up and parse text information that may arrive to you in a less than ideal format. Practice using these functions in cells D2:D5 using the data in column C. (Enter your answers exactly as they appear in the excel spreadsheet.)

Name data to proper case Number of characters in a text string Find first 4 characters of a text string Find final 3 characters of a text string

While LEFT and RIGHT are straightforward, the MID function will provide the middle portion of a text string. You just need to tell it where to begin and how many characters you want after that location.

In cell D6, get three characters from the text string in C6 starting on the third character. (Enter the characters exactly as they appear.)
Concatenation

Concatenation is a very common task in Excel (and in many other programming languages). To concatenate means to bring or "stick" things together. A common example is to have first name and last name and a need to bring those together for a full name. In Excel there are several ways to concatenate text strings.

The easiest is the CONCAT function. All you do is tell it which cells you want stuck together. Try doing that in cell D7. (Enter the result for D7 exactly as it appears in the spreadsheet in the blank below.)

Concatenate (bring together) text strings:

However, CONCAT is very literal. It doesn't know that there should be a space between a first and last name for example. Therefore, you need to add that space to the CONCAT function. In Excel, a blank space is represented by the " " string of characters; quote-space-quote. Add that to the CONCAT function and practice in cell D8. (Enter the result for D8 exactly as it appears in the spreadsheet in the blank below.)

Concatenate (bring together) text strings adding a space, " ":

You can also concatenate by using ampersand (&) calculation operator instead of the CONCAT function. Again, include a space represented by the " " into your formula. Practice that in cell D9. (Enter the result for D9 exactly as it appears in the spreadsheet in the blank below.)

Concatenate (bring together) text strings using ampersand (&) and adding a space, " ":
Number Conversion

Not all numbers should be treated as "numbers" in a dataset. For example, zip codes, phone numbers, addresses, etc., are not meant to be added, subtracted, divided, or multiplied. Adding zip codes makes no sense. Therefore, those types of data should actually be treated as text. Luckily, Excel has the TEXT function which will do that easily. The TEXT function also requires that you tell Excel how to format the result. In the case of a zip code, we want five characters: "#####" (use the quotation marks). Use the TEXT function and the "#####" formatting rule to convert the number in C10 to a text zip code in cell D10. (Enter the result for D10 exactly as it appears in the spreadsheet in the blank below.)

Convert a number (like a zip code) to text:

One challenge with converting zip codes and other numbers is that some of those values start with a leading zero. For numbers, a leading zero does not mean anything. However, for a zip code, it means a lot! Therefore, when converting a zip code that is supposed to have a leading zero, a slightly different formatting is required: "0####" which instructs Excel to format the text as 0 then the four numbers. Use that formatting technique in cell D11. (Enter the results for D11 exactly as it appears in the spreadsheet in the blank below.)

Convert a number (like a zip code) to text adding back leading zero:
Searching and Splitting

Sometimes you need to search for the location of the first occurrence of a character or sub-string within a larger string. A common example is to find the location of the "@" symbol in an email address so you can isolate the username of the address. In cell D12, find the location of the "@" symbol in the email address in cell C12.

Search for the location of the text string "@" within an email address:

Finally, you can combine SEARCH and LEFT to isolate the username of the email address. SEARCH tells you where the "@" symbol is located and LEFT needs to know how many characters on the left you want to keep; which SEARCH can provide. Try combining SEARCH and LEFT in cell D13 to isolate the email username from cell C13. (Enter the result for D13 exactly as it appears in the spreadsheet in the blank below.)

Note: there is one slight modification you will need to make to the SEARCH part of the formula to get just the username. See if you can figure it out.

Split off username from email address. Hint: search for @ symbol:

Text Data Cleaning and Manipulation

Beginning in row 16, columns B and C contain raw names and numeric zip codes. In columns D through H, you are asked to clean and manipulate that data in specific ways. The good thing is that you only have to create formulas for the first row and then you can fill down. Enter the values for the last row below.

Note: These are meant to be challenging. So just take your time and work though each column logically using trial and error if necessary.

Hint: The last column for zip code, will require a nested IF function to first see if the numeric zip code is 5 or 4 characters long so you then know which formatting to apply. (Enter your answers exactly as they appear in the excel spreadsheet. Remember the first letter of the First name and the first letter of the Last name should be capitalized.)

Proper Name	First Name	Last Name	Short Name (First L.)	New Zip Code

Viewing Saved Work Revert to Last Response

Home My Assignments Extension Request

1	2	3	4	5	6	7	8
1/1	1/1	1/1	1/1	1/1	1/1	0/1	1/1
4/100	3/100	3/100	3/100	3/100	3/100	3/100	2/100

WebAssign

Welcome, demo@demo

Ragsdale - Spreadsheet Modeling & Decision Ana 9/e (Homework)

Current Score : 11 / 102

Due : Tuesday, December 30, 2025 11:00 EST

Last Saved : n/a Saving... ()

Instructions

Assignment Submission

Assignment Scoring

Introduction

Common Text Functions

Concatenation

Number Conversion

Searching and Splitting

Text Data Cleaning and Manipulation