Questions and Answers of Microsoft Excel Data Analysis 7th

The file named Makeupdb.xlsx contains information about the sales of makeup products. For each transaction, you are given the following information:Name of salespersonDate of saleProduct soldUnits
The Problem 3 worksheet contains annual returns on stocks, T-bills, and T-bonds. Find and interpret the correlations between each pair of the annual returns on these three classes of investments.Run
The Problem 2 worksheet contains the daily sales volume (in dollars) of laser printers, printer cartridges, and school supplies. Find and interpret the correlations between each pair of these
The Problem 1 worksheet contains the number of cars parked each day in the outdoor lot and in the parking garage near the Indiana University Kelley School of Business. Find and interpret the
You are given the monthly level of the Dow Jones Industrial Average Index for the years 1947 through 1992. Does this data indicate any unusual seasonal patterns in stock returns?
You have a $100,000 expense budget. In column A, you enter expenses as they are incurred. Set up data validation to ensure that the total expenses listed in column E do not exceed your budget.
In the customer PivotTable example, show the top 15 customers in one table and the bottom five customers in another table.
From the chip PivotTable example, create a new PivotTable that summarizes the monthly sales of Chips 1 and 3 in France and the United States.
For the data in the file named Calcitemdata.xlsx, create calculated fields that summarize the sales of dessert (cakes+puddings) and fruits (apples+grapes).
For the grocery example, create a PivotChart that summarizes the sales of each product at each store for the years 2005 and 2006.
For the grocery example, create a calculated field that computes an average per-unit price received for each product.
For the grocery example, prepare a chart that summarizes the trend over time of the sales at each store.
For the years 1985–1992, you are given monthly interest rates on bonds that pay money one year after the day they’re bought. It’s often suggested that interest rates are more volatile— that
The file named Ptablepartsdata.xlsx contains sales of various parts. Each part’s code begins with either Part (for computer part) or Comp (for computer). Create a PivotTable that shows only the
The file named Analyzesurveydata.xlsx contains answers on a 1–7 scale to various questions on a teaching evaluation survey. Create a PivotChart that charts for each question the fraction of the
The file named Collegedata.xlsx tells you the following information about students who applied to graduate school at Kelley School of Business:Gender Desired major
The file named Cigarettedata.xlsx contains the age of a sample of Americans, whether they smoke cigarettes or cigars, and whether they died during the current year. What can you conclude from this
For the data in Problem 14, summarize the total sales of parts and computers.Data from in problem 14The file named Ptablepartsdata.xlsx contains sales of various parts. Each part’s code begins with
Using the data in the file named Monthtomonth.xlsx, create a PivotTable that shows for the year 2010 how each month’s sales differ from the previous month. Sum of Revenue Column Labels Row
The file named Problem27data.xlsx contains daily Bitcoin prices. Create a PivotTable that summarizes daily returns by year and another PivotTable that summarizes daily returns by day of the week. For
The file named Problem26data.xlsx gives sales information for a department store chain. The tax rate for each state is given, and no tax is paid on non-profit sales. Create a PivotTable that shows
The file named Problem25data.xlsx contains data on 2016 NBA players. A player’s effective field goal percentage (EFG) is computed as EFG=(FG+.5*3P)/FGA, where FG equals field goals made, 3P equals
The file named Problem24data.xlsx contains position and salary data for 2016 Carolina Panthers NFL players. Create a drop-down menu from which a player can be selected. Then create formulas that show
The state of Happyland has nine congressional districts. In the file named Problem23data.xlsx, you are given a voter’s district and whether the voter is a Democrat or Republican. Construct a
The file named Problem22data.xlsx contains information on births during 1995 at Dylan General Hospital. Construct a PivotChart that summarizes the births by day of the week. Then use another
In the file named Problem21data.xlsx, you are given the following information about a representative sample of US residents:Whether they subscribe to Garden and Gun magazineIncome level (low, middle,
More than 1,000 registered voters were asked their political orientation (liberal, moderate, or conservative) and whether they favored the Dream Act. Using the data in the file named
The workbook Gamestop.xlsx contains daily closing prices and returns for GameStop stock. Use a PivotTable to compute the average return for each year/month combination. (Look at early 2021!) Also,
The file named Faberu.xlsx contains salaries, travel expenses, faculty ID, department code, and faculty code for all the business-school faculty. Use this data to answer the following questions.Use
The file named Faberu.xlsx contains salaries, travel expenses, faculty ID, department code, and faculty code for all the business-school faculty. Use this data to answer the following questions.Use
The file named Faberu.xlsx contains salaries, travel expenses, faculty ID, department code, and faculty code for all the business-school faculty. Use this data to answer the following questions.Use
Using the file States.xlsx (in the Templates folder), summarize the total sales in each state by year. 1 23 3456780 9 10 11 12 13 14 15 16 17 18 19 20 A Store B Product 20 food 10 cds 6 dvds 4 dvds 6
Using the named Storesales.txt, summarize the total revenue by store, and create a slicer for the Store field. 1 23 3456780 9 10 11 12 13 14 15 16 17 18 19 20 A Store B Product 20 food 10 cds 6
You can find the data for this chapter’s problems in the file Makeupsubtotals.xlsx. Use the Subtotal command for the following computations: Create a consolidated worksheet that gives the
You can find the data for this chapter’s problems in the file Makeupsubtotals.xlsx. Use the Subtotal command for the following computations:Create a consolidated worksheet that gives the largest
You can find the data for this chapter’s problems in the file Makeupsubtotals.xlsx. Use the Subtotal command for the following computations:Use a SUBTOTAL formula to determine the total number of
You can find the data for this chapter’s problems in the file Makeupsubtotals.xlsx. Use the Subtotal command for the following computations:Use a SUBTOTAL formula to determine the total number of
Use the data in the Dowdata.xlsx file to create each of the four types of stock charts.
Use the data in the Diner.xlsx file to create a sunburst chart that summarizes restaurant sales.
The Diner.xlsx file contains information about lunch and breakfast sales at Mel’s Diner. Create a treemap chart to summarize this sales data. Hint: You will need to aggregate the data.
The file Crimedata.xlsx contains annual rates of violent crimes, property crimes, and murders in the United States. Create a graph showing the number of crimes each year. Use check boxes to control
The Indiana University basketball team considers at least 50 deflections per game a good performance and 30 or fewer deflections a poor performance. In the last six games, Indiana had 25, 55, 45, 43,
The Mavsscores.xlsx workbook contains the Dallas Mavericks’ margin of victory (a number < 0 indicates a loss) in 17 games. Graph the Mavs’ margin of victory. Make sure your chart labels each
The Monthlysales.xlsx workbook contains monthly sales at a local restaurant along with quarterly sales totals. Graph the data first with the quarterly totals and then without the quarterly
The Sports.xlsx workbook contains sales by sport at your local sporting-goods store for the years 2021 through 2024. Summarize this data with three different stacked area charts and a line chart.
The Sports.xlsx workbook contains sales by sport at your local sporting-goods store for the years 2021 through 2024. Summarize this data with three different column charts.
The Sportinglife.xlsx workbook contains this year’s sales revenue for each sport at your local sporting-goods store. Summarize this data with a bar of pie chart, where all sports generating at most
Rescale the vertical axis in Problem 9 in multiples of 100,000.Data from in problem 9The COVIDcases.xlsx workbook contains for each date the number of US inhabitants diagnosed with COVID-19 by that
Analyzing COVID19 Dataset You are given a dataset in relation to COVID19 record up to July 2020 (covid19_July2020_dataset.xlsx). The dataset contains data such as country name, region, population,
The Galton.xlsx workbook contains the heights of fathers and their sons. Summarize this data in a scatter chart with appropriately labeled axes.
In the file Hiddenpivot.xlsx, filter the data so that only 2013 sales appear in the spreadsheet, but all the data appears in the chart.
Use the data in the Bubbledata.xlsx workbook to construct a bubble chart with labels that, for five companies, summarize their number of products, their sales, and their percentage of market share.
The Cakes.xlsx file contains for each month the number of salespeople and revenue for a bakery.Graph the number of salespeople and insert a data table below the chart.
The Cakes.xlsx file contains for each month the number of salespeople and revenue for a bakery.Plot monthly sales using a cake image for the columns rather than a column chart.
The Cakes.xlsx file contains for each month the number of salespeople and revenue for a bakery.Graph monthly revenue and insert data labels on the chart.
The Cakes.xlsx file contains for each month the number of salespeople and revenue for a bakery.Create a combination chart with a secondary axis to summarize the monthly number of salespeople and
The Electoralvotes.xlsx file contains each state’s (and the District of Columbia’s) number of members in the House of Representatives and Senate. Create a 3D map stacked column chart that shows
The Top20.xlsx file contains the population of the 20 largest US cities. Create a 3D map bubble chart that displays this population data.
Use the data in the Seattle.xlsx file to create a map for which the height of each column represents the average inspection score in each city.
Set up a spreadsheet that can be used to create a 3D map showing product sales for Bloomington, Indiana; Bloomington, Illinois; and Bloomington, Minnesota.
The Stateincome.xlsx file contains median income by state for the year 2010. Create a 3D map that summarizes this data.
The Cellphonedata.xlsx file contains the population and number of cell phones for the 20 countries/regions having the most cell phones.Create a 3D map with columns that summarizes cell phones by
The Cellphonedata.xlsx file contains the population and number of cell phones for the 20 countries/regions having the most cell phones.Create a filled map that summarizes cell phones by
For the bank branch data, make your line sparklines use the same scale for each branch. 879 6 E 8 City 9 New York 10 Rochester 11 Utica 12 Syracuse 13 Buffalo 14 Ossining 15 Ithaca 16
The data in the Dow.xlsx file contains the values of the Dow Jones Industrial Average for January 2–August 10, 2010. Create a line sparkline to show the ups and downs of the market. 879 6 E 8
Use win/loss sparklines to capture the market’s ups and downs. Again, use the Dow.xlsx file. 879 6 E 8 City 9 New York 10 Rochester 11 Utica 12 Syracuse 13 Buffalo 14 Ossining 15 Ithaca 16
The Goals.xlsx file shows the percentage amount by which bank branches met or failed to meet their goals each month. Summarize this data with win/loss sparklines. 879 6 E 8 City 9 New York 10
The Delldata.xlsx file contains monthly returns for the Standard & Poor’s stock index and for Dell stock. The beta of a stock is defined as the slope of the least-squares line used to predict
The Delldata.xlsx file contains monthly returns for the Standard & Poor’s stock index and for Dell stock. The beta of a stock is defined as the slope of the least-squares line used to predict
If you believe a recession is coming, would you rather invest in a high-beta stock or a low beta stock?The Delldata.xlsx file contains monthly returns for the Standard & Poor’s stock index and
What percentage of the variation in home value is explained by variation in house size?
You are going to build a 500-square-foot addition to your house. How much do you think your home value will increase as a result? 123 2 4 5 6 7 8 9 10 11 A B Square Footage Value 2000 2200 2400 3000
During a month in which the market goes up 5 percent, you are 95 percent sure that Dell’s stock price will increase between which range of values?
We know that 32 degrees Fahrenheit is equivalent to 0 degrees Celsius, and that 212 degrees Fahrenheit is equivalent to 100 degrees Celsius. Use the trend curve to determine the relationship between
The Betadata.xlsx file contains the monthly returns on the Standard & Poor’s index, as well as the monthly returns on Cinergy, Dell, Intel, Microsoft, Nortel, and Pfizer. Estimate the beta of
A 3,000-square-foot house is selling for $500,000. Is this price out of line with typical realestate values in Bellevue? What might cause this discrepancy?
The Electiondata.xlsx file contains for several elections the percentage of votes Republicans gained from voting machines (counted on election day) and the percentage Republicans gained from absentee
For which company does exponential growth have the worst fit with its revenue growth?
For which company does exponential growth have the best fit with its revenue growth?
The Dailydow.xlsx file gives daily values for the Dow Jones Industrial Average Index during the period 1996–2010. What is the R-squared value when you predict tomorrow’s Dow Jones from today’s
The Oldfaithful.xlsx file gives the duration between eruptions of the Old Faithful geyser and the length of the next eruption. Suppose it has been four minutes since the last eruption. You are 95
For each company, fit an exponential trend curve to its sales data. A1 1 2 3 4 5 6 7 8 10 11 12 13 14 15 16 A Dell sales Year 1 2 3 4 5 6 7 8 9 10 11. x ✓ fx B fx Dell
A drug can be produced by using a 400-degree, 300-degree, or 200-degree oven. You are given the pounds of the drug yielded when various batches are baked at different temperatures. Answer the
The Impalas.xlsx file contains the prices of 2009, 2008, 2007, and 2006 Impalas during 2010. From this data, what can you conclude about how a new car loses its value as it grows older?
The Problem7data.xlsx file contains Google’s annual revenues during the years 2005–2013. Use this data to estimate Google’s CAGR and predict Google’s 2014 revenue.
The Moore.xlsx workbook gives the number of transistors per integrated circuit for the years 1974–2014. The famous Moore’s Law states that chip capacity doubles approximately every two years. Is
Use the fax-machine data to model the relationship between the cumulative fax machines produced and the total production cost.
Use the fax-machine data to model the relationship between the cumulative fax machines produced and the average production cost per machine.
The Dow.xlsx file contains the monthly returns of the 30 stocks that make up the Dow Jones Industrial Average. Find the correlations between each pair of stocks. Then, for each stock, use conditional
NFL teams play 16 games during the regular season. Suppose the standard deviation of the number of games won by all teams is 2, and the correlation between each pair of the numbers of games a team
Determine the relationship between yield, size, temperature, and pressure. Fizzy Drugs wants to optimize the yield from an important chemical process. The company thinks the number of pounds
Discuss the interactions between pressure, size, and temperature. Fizzy Drugs wants to optimize the yield from an important chemical process. The company thinks the number of pounds produced each
What settings for temperature, size, and pressure would you recommend? Fizzy Drugs wants to optimize the yield from an important chemical process. The company thinks the number of pounds produced
The file named Countryregion.xlsx contains the following data for several underdeveloped countries/regions:Infant mortality rateAdult literacy ratePercentage of students finishing primary schoolPer
The file named Baseball96.xlsx gives the runs scored, singles, doubles, triples, home runs, and bases stolen for each Major League Baseball team during the 1996 season. Use this data to determine the
Resolve our drug example assuming that a minimum demand of 200 units for each drug must be met. Report for patient XX_XX_XX Drug Amitriptyline Citalopram Clomipramine Escitalopram Imipramine
The file named Cardata.xlsx provides the following information for 392 different car models:CylindersDisplacementHorsepowerWeightAccelerationMiles per gallon (MPG)Determine an equation that can be
The file named Priceads.xlsx contains weekly unit sales of a product as well as the product price and advertising expenditures. Explain how advertising and product price influence unit sales.Price
The file Teams.xlsx contains the runs scored, hit-by-pitcher, walks, singles, doubles, triples, and home runs for Major League Baseball teams during the 2000–2006 seasons. Solve the following
A hitter’s on-base percentage (OBP) is calculated as follows:(Hit-by-Pitcher + Walks + Hits ) / (At Bats + Hit-by-Pitcher + Walks + Sacrifice Flies)A hitter’s slugging percentage (SLG) is the

Showing 1 - 100 of 878