Questions and Answers of Microsoft Excel Data Analysis 7th

The file named Marketbasketdata.xlsx contains sales of various items. For each row, a 1 in columns B through K indicates a purchased item, whereas a 0 marks an item that was not purchased. In the Day
The file named Verizonindirectdata.xlsx contains each employee’s hours of work and employee rating for January–May. Set up a consolidation sheet that enables you to choose any person and then
Using the data in the file named Toysrusformat.xlsx, highlight in red all quarters for which revenue has increased over at least the last two quarters. Highlight all fourth quarter revenues in blue
Suppose each worker in Microsoft’s finance department has been assigned to one of four groups. The supervisor of each group has rated each worker on a scale of 0 to 10, and each worker has rated
The file named Amazon.xlsx contains quarterly revenues for Amazon.com. Use conditional formatting to ensure that the sales during each quarter are highlighted in a different color. 4 Fiscal
The file named Varianceanalysis.xlsx contains monthly profit forecasts and monthly actual sales. The sales variance for a month equals (Actual Sales – Predicted Sales) / Predicted Sales. Highlight
The file named Duedates.xlsx contains due dates for various invoices. Highlight in red all invoices due by the end of the next month.
The file named Accountsums.xlsx (see Figure 24-35) shows weekly salaries in thousands of dollars (listed in chronological order) paid to several top-notch consultants. For example, Britney made
The file named Nbasalaries.xlsx contains salaries of NBA players in millions of dollars. Set up data bars to summarize this data. Players making less than $1 million should have the shortest data
Set up a three-color scale to summarize the NBA salary data. Change the color of the bottom 10 percent of all the salaries to green and the top 10 percent to red.
Use five icons to summarize the NBA player data. Create break points for icons at $3 million, $6 million, $9 million, and $12 million.
How could you set things up so that no icons are shown for players who have salaries between $7 million and $8 million?
Using the data in the file named Amazon.xlsx, highlight quarters in which sales exceeded the sales during each of the last two quarters. 4 Fiscal
The file named Fractiondefective.xlsx contains the percentage of defective units produced daily. A day’s production is considered acceptable if 2 percent or less of the items produced are
The file Globalwarming2011.xlsx contains average world temperatures in degrees Celsius for the years 1881 through 2011. Use color scales to ensure lower temperatures are colored blue, intermediate
Suppose you are saving for your child’s college fund. You are going to contribute the same amount of money to the fund at the end of each year. Your goal is to end up with $100,000. For annual
When students take Vivian’s accounting exam, they are told to enter their email aliases on a Scantron. Often, they make mistakes. Vivian has a list of the actual email aliases for the students in
The file named Top5.xlsx contains the annual revenues of 50 companies. For each year, highlight the companies that have the five largest revenues.
The file named Threetimes.xlsx contains a list of names. Highlight in red each name that appears at least three times.
The file named GNP.xlsx contains records of the quarterly US gross national product. Take advantage of the fact that data bars handle negative values to summarize quarterly GNP percentage growth
In the file named Tableexample.xlsx, set things up so that each salesperson’s rank with regard to total revenue and units sold is included in the worksheet. Of course, if new data is included, your
Using the data in the file named Globalwarming2011.xlsx, highlight the years (not the temperatures) in which the temperature is above average. Average Annual Weather Statistics Atlanta Boston
The file named Shading.xlsx contains 27 quarters of sales. Shade alternate rows in green and yellow. Then alternate shading with four rows in green and four rows in yellow, and so on.
The file named Productlookup.xlsx contains the product sales made each day of the week. Set up a formula that returns the sale of any product on any given day. Of course, if new products are added to
The file named Problem27data.xlsx contains names of several people. A name will be entered in cell E2. Use conditional formatting to highlight each occurrence of the person’s name.
The file named Problem28data.xlsx contains information about voters in 20 congressional districts. In cell I2, we will enter the number of a congressional district. Your job is to set up a format
For the data in the file named Tablemakeuptemp.xlsx (in this chapter’s Templates folder), determine the total units and revenue for all lip gloss or lipstick sold by Jen or Ashley in the East
In the file named Problem29data.xlsx, columns E–I contain the following information:The opening price of a stockWhether we owned stock at the beginning of the dayWhether we sold the stock that
The file named Problem30data.xlsx contains statistics for 2014 NFL quarterbacks. In cell J21, I will enter a number of yards (x). Use conditional formatting to highlight all information for all
In the file named Problem32data.xlsx, you are given information on 2,423 transactions. You believe fraud is likely if either of the following conditions is true:The check amount is at least $500, and
The file named Problem33data.xlsx gives information on 2,000 companies. In K3, a country is entered, and in L3, an industry is entered. Have the spreadsheet highlight all information for companies in
The file named Problem34data.xlsx contains addresses to which checks were mailed. If the address involves a PO box, it is likely to have been a fraudulent transaction. Use conditional formatting to
The file named Problem35data.xlsx lists in numerical order checks that were cashed. Sometimes a check number was skipped. Use conditional formatting to highlight all checks where the previous check
The file Problem36data.xlsx contains the following information for many reimbursements: invoice number, amount, and vendor number. Highlight all transactions for which an amount and invoice number
The file named Singers.xlsx contains a list of songs sung by different singers, as well as the length (in estimated minutes) of each song. Set up your worksheet to compute the total number of songs
In the file named NPV.xlsx in Chapter 8, “The net present value functions NPV and XNPV,” we found that for high interest rates, Project 1 has a larger NPV, and for low interest rates, Project 2
The file named Lookupdata.xlsx contains product codes and prices. Set up the worksheet so that when a product code is entered, your worksheet will return the product price. Of course, when new
For the lemonade stand example in Chapter 17, “Sensitivity analysis with data tables,” use the Scenario Manager to display a report summarizing profit for the following scenarios: Scenario High
The file named Tablepie.xlsx contains sales information for different products sold in a small general store. You want to set up a pie chart to summarize this data. Of course, if new product
For the mortgage-payment example in Chapter 17, use the Scenario Manager to create a report tabulating monthly payments for the following scenarios: Amount borrowed Annual rate Number of monthly
The file named Tablexnpvdata.xlsx lists cash flows received by a small business. Set up a formula to compute (as of January 5, 2007) the NPV of all cash flows. Assume an annual discount rate of 10
The file named Nikedata.xlsx contains quarterly sales revenues for Nike. Create a graph of Nike sales that automatically includes new sales revenue data.
The file named Closest.xlsx contains people’s names and salaries. Set up a worksheet that takes any number and finds the person whose salary is closest to that number. Your worksheet should
The file named Adagency.xlsx contains salaries and ages of employees of an ad agency. Set up a spreadsheet that will compute the average salary and age of the agency’s employees. Of course, your
Use the file named Makeup2007.xlsx for questions 10–16.How much revenue was made on sales transactions involving at least 50 units of makeup? G H 4 Trans Number
The file named Problem11data.xlsx contains statistics on NFL quarterbacks. A QB’s name is entered in cell I2. Set up a format that will highlight the entire row of data for the QB entered in cell
In the car NPV example, determine the following:The direct dependents and all the dependents of the interest rate.The direct dependents and all the dependents of the tax rate.The direct precedents
From the car net present value (NPV) example in Chapter 16, “The auditing tool and the Inquire add-in,” by what rate do annual sales need to grow for total NPV to equal $1 million?
What value of Year 1 unit cost would increase our NPV in the car example in Chapter 16 to $1 million?
Use the file named Makeup2007.xlsx for questions 10–16.Create a table that contains sales of each product made by each person. G H 4 Trans Number Name 456789 10 11 12 13 14 15 16 17 18 19 222 1
Use the file named Makeup2007.xlsx for questions 10–16.How many units of lip gloss did Cici sell in 2004? G H 4 Trans Number Name 456789 10 11 12 13 14 15 16 17 18 19 222 1 Betsy 2 Hallagan 3
Three electricians are wiring my new home. Electrician 1 by himself will need 11 days to do the job. Electrician 2 by himself will need 5 days to do the job. Electrician 3 by herself will need 9 days
Use the file named Makeup2007.xlsx for questions 10–16.What is the average number of units of foundation sold by Emilee? G H 4 Trans Number Name 456789 10 11 12 13 14 15 16 17 18 19 222 1 Betsy 2
Use the file named Makeup2007.xlsx for questions 10–16.Create a table that, when you enter a year in your worksheet, contains sales of each product by person during that year. G H 4 Trans Number
Use the file named Makeup2007.xlsx for questions 10–16.What is the average dollar size of a foundation sale made by Betsy after the end of 2004? G H 4 Trans Number
Use the file named Makeup2007.xlsx for questions 10–16.In transactions in which Ashley sold at least 40 units of lipstick, what is the total dollar amount? G H 4 Trans Number
For the cell range D4:G15, count the cells containing a numeric value. Count the number of blank cells. Count the number of nonblank cells.
The file named NBA.xlsx contains the following information:Columns A and B contain the name of each NBA team and a code number for each team. For example, Team 1 is Atlanta.Column C contains the home
The file named Matchthesecond.xlsx gives a list of names. Some names occur more than once. Determine the row in which, for example, the second occurrence of the name Dave occurs. Set up a worksheet
The file Numbers.xlsx contains a set of numbers. Count how many of these numbers are between 1 and 12 inclusively.
The file named Companydata.xlsx contains information about the 2,000 largest companies in the world. Determine how many of the companies are banks and how many are US banks.
The file named Companydata.xlsx contains information about the 2,000 largest companies in the world. Determine how many banks had sales revenues greater than or equal to $100 billion, greater than or
The file named NBA.xlsx contains the following information:Columns A and B list the name of each NBA team and a code number for each team. For example, team 1 is Atlanta, and so on.Column C lists the
The file named Toysrus.xlsx contains sales revenue (in millions of dollars) during each quarter for the years 1997–2001 and the first two quarters of 2002. Use this data to compute a seasonal index
The file named Sumifrows.xlsx contains sales data during several winter, spring, summer, and fall quarters. Determine average sales during the winter, spring, summer, and fall quarters.
For the file named Numbers.xlsx, find the total of all numbers between 5 and 15, inclusively.
For the file named Numbers.xlsx, find the average of all numbers 10 and 25, inclusively.
The file named Problem19data.xlsx shows each US temperature during each month from 1895 through 2015. For this time period, calculate the average temperature during each month of the year.
The file named Problem20data.xlsx shows the yards gained in rushing and passing plays for NFL teams. Determine the average yards gained per play by each team in running and passing plays.
The file named Problem21data.xlsx has statistics on NFL quarterbacks. Determine the total completions and attempts for each NFL team. Remember, some teams have more than one QB!
Using the data in the file named Problem22data.xlsx, determine the total units of each product sold by each salesperson.
The file named Problem23_24data.xlsx gives data on the 2,000 largest companies in the world. Find the number of banking firms, the average revenue per listed banking firm, and the fraction of all
The file named Problem23_24data.xlsx gives data on the 2,000 largest companies in the world. Find the revenue and name of the largest French retailing firm.
The file named Ch21p1.xlsx contains data about unit sales for 11 products during the years 1999–2003. Write a formula using the MATCH and OFFSET functions that determines the sales of a given
A commonly suggested moving-average trading rule is to buy a stock when its price moves above the average of the last D months and to sell it when its price moves below the average of the last D
A commonly suggested moving-average trading rule is to buy a stock when its price moves above the average of the last B months and to sell it when its price moves below the average of the last S
The file named Lagged.xlsx contains data about the number of magazine ads placed by the US Army recruiting group during each of 60 consecutive months. For each month, the kmonth lagged number of ads
The file named Verizondata.xlsx gives sales of four different Verizon phones in five regions. Determine an efficient method to enter for each of the 20 region-product combinations, the region, type
This problem is a difficult one. The file named Agingdata.xlsx gives the number of insurance claims projected to be received daily and the number of insurance company workers available. Each day, a
Each row of the file named DVDsales.xlsx contains monthly sales of DVDs. Write a formula to determine the sales for each DVD during its first six months on the market.
To obtain a golfer’s handicap, you average the 10 lowest scores of the golfer’s last 20 rounds. Then you subtract 80 and round to the nearest integer. Thus, if the 10 lowest scores of the last 20
Each row of the file named Carsumdata.xlsx contains sales data for a product (car, train, or plane) from January through July. Suppose you enter a month and a product into the worksheet. Write a
The file named Verizon.xlsx contains monthly returns on Verizon stock. Use the OFFSET function to extract all the January returns to one column, all the February returns to one column, and so on.
The file named Casesensitive.xlsx contains product codes and product prices. Note that the product codes are case sensitive. For example, DAG32 is not the same product as dag32. Write a formula that
The file named Reversed.xlsx contains a column of numbers. Use the OFFSET function to reverse the numbers so that the number on the bottom occurs on top, and so on.
The file named Diagonal.xlsx contains a matrix of numbers. Determine how to put the diagonal elements of the matrix in a single column.
The file named Yeartodate.xlsx contains a company’s monthly sales from January 2008 through August 2013. Write a formula that returns for a given year and month of a year the year’s cumulative
The file named Transactiondata.xlsx contains sales transactions in divisions A, B, C, D, and E of a drug company. Use the OFFSET function to modify this data so that the sales for each division
The ADDRESS function yields the actual cell address associated with a row and column. For example, the formula ADDRESS(3,4) yields $D$3. What result would be obtained if you entered the formula
The workbook named P23_2.xlsx contains data for the sales of five products in four regions (East, West, North, and South). Use the INDIRECT function to create formulas that enable you to easily
The file named P23_3.xlsx contains six worksheets. Sheet 1 contains Month 1 sales for Products 1–4. These sales are always listed in the range E5:H5. Use the INDIRECT function to efficiently
The file named Productpaste.xlsx contains data on quarterly sales for four products. Copy this data to another range so that quarterly sales are read across instead of down. Link your copied data to
Consider the following cash flows. If today is November 1, 2001, and r = 0.15, what is the NPV of these cash flows? Date 12/15/01 1/11/02 4/07/03 7/15/04 Cash flow -$1,000 $300 $600 $925
Consider the following two projects, and assume a company’s cost of capital is 15 percent. Find the IRR and NPV of each project. Which projects add value to the company? If the company can choose
Consider a project with the following cash flows. Determine the project’s IRR. If the annual cost of capital is 20 percent, would you undertake this project? Year 1 -$4,000 Year 2 $2,000 Year
Compute all IRRs for the following sequence of cash flows: Year 1 -$10,000 Year 2 $8,000 Year 3 $1,500 Year 4 $1,500 Year 5 $1,500 Year 6 $1,500 Year 7 -$1,500
Find all IRRs for the following project: Year 1 $100 Year 2 -$300 Year 3 $250
Find all IRRs for a project having the given cash flows on the listed dates. 1/10/2003 7/10/2003 5/25/2004 7/18/2004 3/20/2005 4/1/2005 1/10/2006 -$1,000 $900 $800 $700 $500 $500 $350
My airplane is flying the following route: Houston–Los Angeles–Seattle–Minneapolis– Houston. On each leg of the journey, the plane’s fuel usage (expressed as miles per gallon) is 40 –
Consider a project having the following cash flows. Try to find the IRR of this project without simply guessing. What problem arises? What is the IRR of this project? Does the project have a unique

Showing 300 - 400 of 878