Questions and Answers of Microsoft Excel Data Analysis 7th

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
The file named Lookuptwocolumns.xlsx gives the model, year, and price for each of a series of cars. Set up formulas that enable you to enter the model and year of a car and return its price.
The file named Moviedata.xlsx contains the names of several movies followed by the number of copies of the movie DVD purchased by a local video store. Extract the title of each movie from this data.
Again with Moviedata.xlsx, for each movie, extract the number of copies purchased from this data. You probably want to use the SUBSTITUTE function. The syntax of the SUBSTITUTE function is
The file named Problem16data.xlsx contains the number of people who responded 1 – 5 on a marketing questionnaire (1 = very unlikely to buy product, ..., 5 = very likely to buy product). Summarize
The file named Problem17data.xlsx contains people’s names (such as Mr. John Doe). Use text functions to extract each person’s title and first name to separate columns.
The file named Weirddata.xls contains three numbers imported from an internet site. When you try adding the numbers, you will see the total is incorrect. Modify the data so that the SUM
Use Flash Fill to change the names in the worksheet named Flashfilltemplate.xlsx (in the Templates folder) to lowercase.
Use Flash Fill to extract the number from each row in the file named Movienumbers.xlsx.
For the data in the file named Movienumbers.xlsx, use Flash Fill to create a column that inserts the number in the movie title at the end of the phrase The number in the title of this movie is.
Each row of the file named Problem22data.xlsx gives the name of a city, the city’s state, and population of the city. Use text functions to extract the state to a separate column.
The file named Problem23data.xlsx gives the name of a quarterback (QB), followed by the player’s QB rating, followed by the number of completed passes thrown by the QB. Use text functions to
In each row of the file named Problem24data.xlsx, you will find a city’s zip code, city name, state (always Texas!), latitude, and longitude. Use formulas to extract the name of the city in each
Each row of the file named Problem25data.xlsx contains between the hyphen and comma a QB’s number of completions. Use text functions to extract each QB’s number of completions.
The file named Problem26data.xlsx contains sales data on the world’s 2,000 largest companies. If an integer between 1 and 2000 is entered in cell B3, then cell C3 should return a sentence such as
Consider the following set of cash flows over a four-year period. Determine the NPV of these cash flows if r = 0.15 and cash flows occur at the end of the year. Year 1 2 3 4 Cash
Beginning one month from now, a customer will pay $25 per month to her internet provider for the next five years. Assuming all revenue for a month is received at the beginning of the month, use the
Beginning one month from now, a customer will pay his internet provider $25 per month for the next five years. Assuming all revenue for a year is received at the middle of a year, estimate the NPV of
Using the definitions of quarters given in Problem 19, write a formula that computes for any given date the last day of the previous quarter.Data from in Problem 19Suppose the first quarter of each
Given any date, find a way to have Excel compute the last day of the date’s month.
Given any date, find a way to have Excel compute the day of the week of the first day of the date’s month.
The file named Machinedates.xlsx contains dates on which several machines were bought and sold. Determine how many months and years each machine was kept.
For the file Datep.xlsx, express each date in serial format.
For the file Datep.xlsx, determine the month, year, day of the month, and day of the week for each date.
To what date does a serial format of 45000 correspond?
To what date does a serial format of 4526 correspond?
The workbook Problem27data.xlsx contains phone number, zip code, and Social Security number data. Apply the correct formats to this data. 12345 123 3 Zip code +4 A 11 07039-4123 6 7 8 Social Security
Given the latitude and longitude of any two cities, the file Citydistances.xlsx computes the distance between the two cities. Define range names for the latitude and longitude of each city and ensure
Open a worksheet and name the range containing the cells A1:B3 and A6:B8 Red.
You own six local coffee shops. The revenues and customer count for each coffee shop are given in the following table.Set up a workbook that makes it easy to enter the revenue and customer count for
Assume that at the end of each of the next 40 years, you put the same amount in your retirement fund and earn the same interest rate each year. Show how the amount of money you have at retirement
The payback period for a project is the number of years needed for a project’s future profits to pay back the project’s initial investment. A project requires a $300 million investment at Time 0.
A software development company is thinking of translating a software product into Swahili. Currently, 200,000 units of the product are sold per year at a price of $100 each. Unit variable cost is
Currently, you sell 40,000 units of a product for $45 each. The unit variable cost of producing the product is $5. You are thinking of cutting the product price by 30 percent. You are sure this will
The annual demand for a product equals 500 – 3p + 10a.5, where p is the price of the product in dollars and a is hundreds of dollars spent on advertising the product. The annual fixed cost of
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 investment
You’ve been assigned to analyze the profitability of Bill Clinton’s autobiography. The following assumptions have been made:Clinton is receiving a one-time royalty payment of $12 million.The

Showing 400 - 500 of 878