Question

In: Operations Management

Your summer intern has taken data from the Bureau of Labor Statistics on the changes in...

Your summer intern has taken data from the Bureau of Labor Statistics on the changes in the Consumer Price Index on food items from 2009 through January 2017. The index values are set to 100 for the same items in the years 1982-84, so if an item has an index value of 80, then it has dropped in price by 20% since 1984. If an item has an index value of 135, then it has increased in cost by 35% since 1984.

Unfortunately, your intern just copied the data from a PDF file into Excel, with very little formatting. Use your knowledge of Excel to perform the following operations and answer the questions. Your answers to the questions should be on the FIRST tab in your Excel workbook, labeled Answers.

Please answer all questions and provide instructions for your answer using EXCEL. Upload final table(s) for reference:

(1) Use the Table format option in Excel to improve the format of the data. Also, round each CPI value in the table to the nearest cent (in other words, display only 2 decimal places after the decimal point.) Sort the data so that you can easily answer the following questions: According to the most recent available data,

- Which item has increased percentagewise the most?

- Which item has increased percentagewise the least?

(2) Your instructor has added a Code column for the category of several food items: FF for fresh fruit, FV for fresh vegetables, CFV for canned fruit and vegetables, and so on. Convert the table so you can use the Subtotal feature of Excel to answer the following question:

- Which had a greater average increase in the CPI value from 2009 to 2017: Items categorized as fresh fruit (FF) or fresh vegetables (FV)?

(3) Finally, create a pivot table and pivot chart comparing the average CPI in 2009 and 2017 for just fresh fruits and fresh vegetables.

- Which category (FF or FV) had the greatest percentage increase?

Hint: You may need to add a column to a table and perform an additional calculation in that column to answer one or more questions. See CPI Data below:

Item

Code

2009

2010

2011

2012

2013

2014

2015

2016

Jan-17

Fresh fruits and vegetables

FFV

315.247

322.087

325.075

332.405

331.492

344.932

347.583

336.988

335.844

Fresh fruits

FF

325.602

335.845

334.015

352.393

345.395

357.915

361.76

356.987

354.863

Apples

FF

273.996

284.299

304.597

344.961

323.923

316.514

334.632

337.515

330.947

Bananas

FF

193.304

196.94

204.013

204.104

201.906

200.464

201.757

198.865

198.357

Citrus fruits

FF

187.089

204.075

196.409

202.769

208.489

219.822

223.155

227.649

231.109

Oranges, including tangerines

FF

377.682

394.652

395.553

408.569

427.519

443.228

453.648

456.098

467.489

Other fresh fruits

FF

120.84

122.394

118.771

125.522

121.944

129.506

128.843

123.948

122.115

Fresh vegetables

FV

303.191

306.775

314.28

310.458

315.671

330.043

331.419

314.786

314.669

Potatoes

FV

278.568

293.671

315.537

292.126

313.252

307.533

307.171

323.004

332.772

Lettuce

FV

329.458

304.919

304.989

295.211

301.064

314.445

341.425

283.702

273.082

Tomatoes

FV

348.514

311.927

315.907

331.061

330.412

384.81

345.131

328.824

324.704

Other fresh vegetables

FV

293.958

314.163

320.226

318.008

319.855

327.225

335.785

320.377

321.452

Processed fruits and vegetables

PFV

145.397

144.007

154.065

154.256

154.779

155.373

155.052

155.193

156.407

Canned fruits and vegetables

CFV

149.489

146.923

155.275

156.1

159.547

159.292

158.43

159.139

159.891

Canned fruits

CF

139.841

136.168

147.415

149.702

151.56

152.286

154.734

156.161

157.086

Canned vegetables

CV

159.591

157.333

165.062

164.24

168.798

168.766

165.431

166.277

167.038

Frozen fruits and vegetables

FrF

135.621

135.91

149.25

147.205

143.679

145.799

145.167

146.034

147.6

Frozen vegetables

FrV

188.807

188.774

206.012

201.556

197.121

198.928

197.343

199.509

202.583

Other processed fruits and vegetables including dried

148.847

147.8

156.601

159.122

159.05

159.39

161.08

158.367

160.272

Dried beans, peas, and lentils

176.524

172.09

195.782

197.969

195.994

205.107

204.769

197.172

194.81

Nonalcoholic beverages and beverage materials

161.216

159.229

168.52

168.204

165.767

166.978

167.482

165.965

167.074

Juices and nonalcoholic drinks

124.645

122.283

127.526

128.378

127.728

127.822

128.685

128.1

128.622

Carbonated drinks

151.851

149.589

159.013

159.079

155.629

157.881

158.32

158.624

160.93

Frozen noncarbonated juices and drinks

150.282

149.81

169.472

167.736

172.675

176.729

177.198

178.861

177.187

Nonfrozen noncarbonated juices and drinks

116.601

113.993

116.896

118.261

118.896

117.754

118.9

117.802

117.494

Beverage materials including coffee and tea

112.391

113.31

125.197

121.842

116.614

119.604

118.748

116.406

117.912

Coffee

180.802

185.379

221.236

211.723

195.725

202.772

199.729

193.557

197.848

Roasted coffee

185.174

191.511

231.504

221.087

201.214

209.577

209

201.867

206.886

Instant and freeze dried coffee

196.843

199.021

219.097

210.585

207.489

208.007

198.181

194.307

198.027

Other beverage materials including tea

124.96

124.029

126.698

126.541

127.355

128.569

129.259

129.127

128.863

Other food at home

189.921

190.147

200.566

204.626

203.72

206.831

208.915

208.301

208.804

Sugar and sweets

198.712

203.098

210.846

213.265

207.795

210.019

215.451

213.943

214.976

Sugar and artificial sweeteners

179.643

191.919

199.499

197

178.971

179.248

184.873

180.166

187.509

Candy and chewing gum

132.313

134.049

138.172

140.308

139.408

141.883

145.966

145.06

144.758

Other sweets

141.122

142.349

151.239

154.711

151.621

151.259

153.059

154.077

153.622

Fats and oils

197.391

200.476

227.601

231.54

226.091

228.352

227.037

223.004

224.516

Butter and margarine

150.847

164.832

183.182

182.281

181.251

202.213

202.867

195.422

202.262

Butter

160.781

195.956

199.637

194.493

196.193

240.358

239.169

224.015

236.434

Margarine

234.357

237.245

285.391

289.844

282.49

289.968

295.025

296.285

298.282

Salad dressing

125.704

127.917

138.083

138.353

136.045

130.185

132.242

129.464

128.638

Other fats and oils including peanut butter

142.856

138.535

164.205

170.837

164.288

160.21

156.275

155.628

155.086

Peanut butter

132.636

127.215

161.81

184.152

170.331

164.221

158.143

155.94

152.853

Other foods

203.832

202.776

211.986

216.708

217.204

220.909

223.012

223.068

223.347

Soups

224.677

221.226

226.858

227.765

227.87

226.529

230.038

239.686

240.672

Frozen and freeze dried prepared foods

166.386

164.252

169.202

169.6

167.933

171.202

170.74

169.675

166.034

Snacks

215.081

215.73

231.599

240.261

242.341

246.823

251.559

249.405

250.968

Spices, seasonings, condiments, sauces

208.868

206.76

217.254

222.847

223.367

228.318

231.446

233.682

241.006

Salt and other seasonings and spices

121.482

121.107

132.684

133.78

134.655

141.105

144.876

148.891

151.976

Olives, pickles, relishes

130.724

127.279

127.752

136.069

133.597

133.807

137.193

130.974

137.629

Sauces and gravies

124.327

123.617

127.154

131.056

131.568

133.791

135.448

135.333

138.255

Other condiments

217.733

234.488

258.486

263.72

256.219

260.94

268.179

263.684

274.381

Baby food

139.287

138.061

148.108

151.937

152.123

155.245

154.439

156.954

159.088

Other miscellaneous foods

122.422

122.419

126.293

129.455

130.119

132.149

133.205

132.488

131.139

Prepared salads

107.366

107.253

110.563

112.984

116.321

120.827

125.398

128.25

127.277

Food away from home

224.789

227.722

234.435

240.359

245.3

252.628

259.097

265.104

266.079

Full service meals and snacks

140.112

141.962

146.057

149.583

152.736

157.454

161.042

164.876

165.361

Limited service meals and snacks

143.407

144.795

149.265

153.136

156.133

161.08

165.079

169.009

169.832

Food at employee sites and schools

139.858

143.335

148.359

153.468

157.276

160.136

168.165

172.372

173.004

Food at elementary and secondary schools

117.561

120.445

124.494

128.976

131.727

134.766

140.92

143.948

143.907

Food from vending machines and mobile vendors

131.765

134.605

138.306

141.887

143.585

144.278

147.383

153.031

153.249

Solutions

Expert Solution

- Which item has increased percentage-wise the most? - > Butter with 47.05% growth

- Which item has increased percentagewise the least? - > Lettuce with a negative growth of -17.11 %

The formula used = ( CPI in Jan 2017 - CPI in 2009) / CPI in 2009

- Which had a greater average increase in the CPI value from 2009 to 2017: Items categorized as fresh fruit (FF) or fresh vegetables (FV)? - FF has the greater average increase

- Which category (FF or FV) had the greatest percentage increase? FF has the greater percentage increase

FF vs FV

keosha answered 1 year ago
Next > < Previous

Related Solutions

The following data are from the Bureau of Labor Statistics and shows the outcome of women...
The following data are from the Bureau of Labor Statistics and shows the outcome of women at age 46: Less than High school High school grad Some college Bachelor’s degree + Ever Married 0.09 0.30 0.23 0.24 Ever Divorced 0.05 0.15 0.11 0.07 Never Married 0.02 0.05 0.03 0.04 Note that ever married and ever divorced are not mutually exclusive—the ”ever divorced” category is a subset of the ”ever married” category. (a) What percent of women were ever married? (b)...
The Bureau of Labor Statistics (BLS) reports on the number of jobs created using data from...
The Bureau of Labor Statistics (BLS) reports on the number of jobs created using data from the ____________. United States Census Annual Business Survey (ABS) Current Employment Statistics (CES) survey Current Population Survey (CPS)
#2) Based on data from the Bureau of Labor Statistics, in 2015 the mean annual salary...
#2) Based on data from the Bureau of Labor Statistics, in 2015 the mean annual salary for a registered nurse was $71,000 with a standard deviation of $1,140. Assuming that the salaries are normally distributed, find the probabilities indicated in parts a and b. Show your work to calculate the necessary z-scores, include a sketch showing the corresponding areas under the standard normal curve (similar to those shown on page 239), and use the standard normal distribution curve in the...
The table below is taken from the Australian Bureau of Statistics website.
  The table below is taken from the Australian Bureau of Statistics website. It provides data on mortgage repayment (monthly) by Australian households living in different dwelling structures in the year 2011. Dwelling Structure Total occupied private dwellings Separate house Semi-detached, row or terrace house, townhouse Flat, unit or apartment Mortgage Repayment (monthly)         Total 2 709 429 2 324 546 190 411 187 375 $0-$299 114 197 99 624 7 352 6 694 $300-$449 64 357...
Data from the Bureau of Labor Statistics’ Consumer Expenditure Survey show that annual expenditures for cellular...
Data from the Bureau of Labor Statistics’ Consumer Expenditure Survey show that annual expenditures for cellular phone services per consumer unit increased from $263 in 2001 to $583 in 2007. Let the standard deviation of annual cellular expenditure be $51 in 2001 and $206 in 2007. a. What is the probability that the average annual expenditure of 100 cellular customers in 2001 exceeded $248? (Round answer to 4 decimal places.) b. What is the probability that the average annual expenditure...
Data from the Bureau of Labor Statistics' Consumer Expenditure Survey show customers spend an average of...
Data from the Bureau of Labor Statistics' Consumer Expenditure Survey show customers spend an average of (µ) $608 a year for cellular phone services. The standard deviation of annual cellular spending is (σ) $132. The random variable, yearly cellular spending, is denoted by X. We plan to select a random sample of 100 cellular customers. 11. The sampling distribution of X¯X¯ Select one: a. is not normal because the sample size is too small b. is normal due to the...
Data from the Bureau of Labor Statistics’ Consumer Expenditure Survey (CE) show that annual expenditures for...
Data from the Bureau of Labor Statistics’ Consumer Expenditure Survey (CE) show that annual expenditures for cellular phone services per consumer unit increased from $237 in 2001 to $634 in 2007. Let the standard deviation of annual cellular expenditure be $52 in 2001 and $207 in 2007. What is the probability that the average annual expenditure of 125 cellular customers in 2001 exceeded $220? (Round “z” value to 2 decimal places, and final answer to 4 decimal places.)   Probability   ...
Use the Bureau of Labor Statistics website to search for income data on the occupation of...
Use the Bureau of Labor Statistics website to search for income data on the occupation of 15-1212 Information Security Analysts. The BLS has wage information for hundreds of jobs and data can also be found by state. Once you arrive at the site, complete the following steps to gather the data you need to answer the questions. Step 1 Search for information on the occupation (15-1212 Information Security Analysts) by clicking on national wage data 800 occupations. This will take...
The Bureau of Labor Statistics (BLS) adjusts the numbers from the household survey to estimate the...
The Bureau of Labor Statistics (BLS) adjusts the numbers from the household survey to estimate the employment picture for the entire population. Once the adjustment is made, the BLS uses the figures to generate key employment indicators. The following table presents information from the household surveys conducted for November 2017 and, for purposes of comparison, November 2016. The numbers in the table are listed in thousands. For example, the number of unemployed in the table for November 2017 indicates that...
Xander, summer statistics intern in the Superintendent’s Office for the Palisades Point School District, wonders if...
Xander, summer statistics intern in the Superintendent’s Office for the Palisades Point School District, wonders if the homerun teacher referrals in the 7th grade for two week periods are comparable. He tests this claim very preliminarily at the 1% significance level as a pilot study, and presumes that the distribution of referrals among these teachers is reasonably normal. He collects independent, simple random samples. The following data tables represent the numbers of referrals made by these seventh grade teachers: Alcott...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT