Questions and Answers of Microsoft Excel Data Analysis 7th

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
For the cash flows in Problem 1, assume you can borrow at 12 percent per year and invest profits at 15 percent per year. Compute the project’s MIRR.Data from in problem 1Compute all IRRs for the
Suppose that today, you paid $1,000 for the bond described in Problem 8 of Chapter 8, “The net present value functions: NPV and XNPV.” What would be the bond’s IRR? A bond’s IRR is often
After earning an MBA, a student will begin working at an $80,000-per-year job on September 1, 2005. She expects to receive a 5 percent raise each year until she retires on September 1, 2035. If the
Consider a 30-year bond that pays $50 at the end of Years 1–29 and $1,050 at the end of Year 30. If the appropriate discount rate is 5 percent per year, what is a fair price for this bond?
Reciprocal cost allocation is a common method used to allocate costs to support departments. This method can easily be implemented by the use of circular references. To illustrate, suppose Widgetco
Meg Prior is 25 years old and is going to invest $10,000 in her retirement fund at the beginning of each for the next 40 years. Assume that during each of the next 30 years Meg, will earn 15 percent
Give an intuitive explanation of why Project 6 (on the worksheet Which Project in the file named IRR.xlsx) has an IRR of 50 percent.
Before paying employee bonuses and state and federal taxes, a company earns profits of $60,000. The company pays employees a bonus equal to 5 percent of after-tax profits. State tax is 5 percent of
On January 1, 2002, I have $500. At the end of each month, I earn 2 percent interest. Each month’s interest is based on the average of the month’s beginning and ending balances. How much money
We start a year with $200 and receive $100 during the year. We also receive 10 percent interest at the end of the year, with the interest based on our starting balance. In this case, we end the year
The file named FormattingDDAnum.xlsx gives a bunch of text strings, such as DDA : D in cell C4, DDA1250045 in cell C17, and so on. A cell is properly formatted if the first three characters are DDA
Suppose the number of Group 1 members is listed in cell B1, the number of Group 2 members is listed in cell B2, and the number of Group 3 members is listed in cell B3. The total number of group
The file named Dividebyprice.xlsx contains units sold of each product and total revenue. Determine the average price for each product and display each average in column H. Of course, if units sold is
The School of Fine Art has 100 lockers numbered 1 through 100. At present, all lockers are open. We begin by closing every locker whose number evenly divides by 3. Then we toggle each locker whose
Set up a calendar worksheet that takes a given month and year as inputs and tells you the day of the week on which each day of the month occurs.
The file named Matchlist.xlsx contains a list of people who bought your product in February and a list of people who bought it in March. Determine how many of your February customers purchased your
Some rows of column C of the file named Problem28data.xlsx contain the word and. Create formulas in column D that place an X in a row of column D if and only if column C in that row contains the word
Suppose you toss two 20-sided dice. The 400 possible outcomes are (1,1), (1,2), ... (20,20). Use IF statements to systematically generate all 400 possible outcomes.
The file named Footballdata.xlsx gives win-loss records for NFL teams during the 2015 season. Write a formula in H13 that can be copied to H14:H31 that gives the winning percentage for each team and
You are to determine a company’s annual revenues based on the information in cells C1:D5 of the file named Problem34data.xlsx. Unit sales increase at the given rate until a competitor enters;
Cell D2 of the file named Problem35data.xlsx gives Year 1 market size. Cell D3 gives market share (the same each year), cell D4 gives product price (the same each year), and cell D5 gives profit
President’s Day is the third Monday of February. Create a spreadsheet in which the user enters the year, and the spreadsheet returns the date of President’s Day.
You are to create formulas that return the profit made from selling a greeting card. Current information is given in cells B1:B7 of the file named Problem37data.xlsx. We ordered cards at a cost of
Column B of the file named Problem38data.xlsx contains a salesperson’s name in the oddnumbered rows followed by the salesperson’s units sold directly below their name. Use formulas to create in
In the file named Problem39data.xlsx, you are given data on clients audited by QXZ Inc. You are told if a given firm is large or small and if they passed or failed their June audit. If the firm is
The workbook Problem40data.xlsx contains home and away scores for football games. Copy a single formula from I5 to I6:I40 that returns the home team’s margin of victory for each game.
Row 2 of the workbook Problem41data.xlsx contains my original opinion about the correct answer to each test question. The file also contains each student’s exam grade. I later found out that the
Column D of workbook Problem42.xlsx contains information rows in 3 through 109. Using IF and IS functions, determine how many of cells D3:D109 contain numbers, formulas, and text.
Column C of the workbook Problem43data.xlsx lists people working for a company in January and Column D lists people working for the company in February. Enter formulas in F4:G27 that return a list of
The file Marathon.xlsx gives marathon race times for four runners. The following three problems refer to this data. Compute the average time of the runners.How much faster was John than Jill?How many
The file named Jobshop.xlsx gives the start time and date for several jobs and the time required to complete each job. Determine the completion time for each job.
The file named Mavs.xlsx contains statistics for the great 2002–2003 Dallas Mavericks basketball team. Player names are listed in column A, statistical categories are listed in row 3, and
Field-goal percentage, free-throw percentage, and three-point percentage are listed in the Mavs.xlsx file as decimals. For example, Steve Nash made 91.9 percent of his free throws, which is listed as
The file named Productsalespaste.xlsx contains sales of Products X, Y, and Z, in thousands of units. Convert this sales data to sales in actual units.
Place hyperlinks in the file named Multipleworksheetstemp.xlsx to cell A5 in worksheets Sheet 3, Sheet 5, and Sheet 7, as well as to the website Officeblog.com.
For the file named Productmix.xlsx, determine the following:The precedents of profit.The dependents of the amount of Product 1 produced.
Reconsider our hedging example from Chapter 12, “IF, IFERROR, IFS, CHOOSE, SWITCH and the IS functions.” For stock prices in six months that range from $20 to $65 and with the number of puts
For our mortgage example, suppose you know the annual interest rate will be 5.5 percent. Create a table that shows for amounts borrowed from $300,000 to $600,000 (in $50,000 increments) the
The file named Citydistances.xlsx gives the latitude and longitude for several US cities. There is also a formula that determines the distance between two cities by using a given latitude and
The file named Antitrustdata.xlsx shows the starting and ending years for many court cases. Determine the number of court cases active during each year.
The file Sharedata.xlsx contains the numbers of shares you own of each stock and the price of each stock. Compute the value of the shares of each stock with the formula shares*price.
The file named Hr.xlsx gives employee ID codes, salaries, and years of experience. Write a formula that takes a given ID code and yields the employee’s salary. Write another formula that takes a
The file named NBAplayers.xlsx gives the age and salary for several NBA players. Enter formulas in cells J5:K50 that return each player’s age and salary.
Column F of the file Hardware.xlsx gives product codes for hardware products, and column G gives the price of each product. Columns M–O list the quantity and price a hardware store paid for various
Rows 3–13 of the workbook Problem3_15.xlsx contain information on a company’s five employees. In rows 17–21, use VLOOKUP formulas to fill in the indicated data.
Use an XLOOKUP formula to redo Problem 15.Data from in Problem 15Rows 3–13 of the workbook Problem3_15.xlsx contain information on a company’s five employees. In rows 17–21, use VLOOKUP
The workbook Problem3_18.xlsx contains your baseball salary data, but the data is not sorted by salary. Which of the formulas in the workbook XLOOKUP.xlsx will return different answers when applied
The workbook Problem 3_19.xlsx contains the population of seven US cities. Use VLOOKUP formulas with wildcards to extract the correct populations to the range K7:K12.
Use the INDEX function to compute the distance between Los Angeles and Phoenix and the distance between Denver and Miami. 123 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B Boston-Denver Seattle- Miami
The file named Product.xlsx contains monthly sales for six products. Use the INDEX function to compute the sales of Product 2 in March. Use the INDEX function to compute total sales during April.
The file named NBAdistances.xlsx shows the distance between any pair of NBA arenas. Suppose you begin in Atlanta, visit the arenas in the order listed, and then return to Atlanta. How far would you
Suppose the VLOOKUP function has been removed from Excel. Explain how you could still make useful calculations by using the MATCH and INDEX functions.
The file Problem5_9.xlsx contains passing statistics for NFL teams during the 2017 season. Using formulas that do not combine INDEX and MATCH in the same cell, return the desired statistics in the
The file Problem5_9.xlsx contains passing statistics for NFL teams during the 2017 season. Using formulas that do combine INDEX and MATCH in the same cell, return the desired statistics in the range
Cells B2:B5 of the workbook named Showbiz.xlsx contain the fictitious addresses of some of our favorite people. Use text functions to extract each person’s name to one column and each person’s
The workbook named IDprice.xlsx contains product IDs and prices for various products. Use text functions to put the product IDs and prices in separate columns. Then use the Convert Text to Columns
The file named Textstylesdata.xlsx contains information about the style, color, and size for a variety of shirts. For example, the first shirt is style 100 (indicated by digits between the colon and
The file named Lineupdata.xlsx gives the number of minutes played by various five-player combinations (lineups). Lineup 1 played 10.4 minutes, and so on. Use text functions to put this data into a
The file named Reversenames.xlsx gives the first name, middle name or initial, and last name of several people. Transform these names so that the last name appears first, followed by a comma,
The file Incomefrequency.xlsx contains the distribution of starting salaries for MBA graduates of Faber College. Summarize this data by creating a frequency graph.
Recall that CHAR(65) yields the letter A, CHAR(66) yields the letter B, and so on. In a new workbook, use these facts to efficiently populate cells B1:B26 with the sequence A, B, C, and so on,
The file named Capitalizefirstletter.xlsx contains various song titles or phrases, such as “The rain in Spain falls mainly in the plain.” Ensure that the first letter of each song title is
The file named Ageofmachine.xlsx contains data in the following form: S/N: 160768, vib roller,84" smooth drum,canopy Auction: 6/2–4/2005 in Montgomery, AlabamaEach row refers to a machine
When downloading corporate data from the Security and Exchange Commission’s EDGAR site, you often obtain data for a company that looks something like this:Cash and Cash Equivalents $31,848

Showing 400 - 500 of 914