Microsoft Excel Data Analysis And Business Modeling 7th Edition Wayne Winston - Solutions

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 banking revenue earned by banks with revenues at least $20 billion.
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 product during a given year. Can you think of another way to solve this problem without using the MATCH
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 months. In Chapter 12, “IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions,” I showed that for 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 months. In Chapter 12, I showed that for B = S = 15, this trading rule outperformed the Standard &
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 is defined to equal the number of ads placed k months ago. For months 7 through 60, you want to
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 of phone, and sales of each phone into one row.
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 worker can process up to 30 claims. Workers process the oldest claims in the system first. In the
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 rounds add up to 864, the handicap would be 6. The file named Golfdata.xlsx contains a golfer’s
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 formula that gives the total sales of that product during the given month.
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 gives the product price for any product code. Hint: You might need to use the EXACT function. The
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 sales to date. For example, if you enter a 6 for the month and 2010 for the year, your formula should
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 appear in a single row.
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 =INDIRECT(ADDRESS(3,4))?
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 calculate the total sales of any combination of consecutively numbered products, such as Products 1–3,
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 tabulate the sales of each product by month in a separate worksheet.
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 the original data so that your computation of annual sales in the copied data range will reflect
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 only a single project, which project should it choose? Project 1 Year 1 -$40 Year 2 $130 Year
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 3 $4,000
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 – 0.02 * (Average Fuel En Route). Here, Average Fuel En Route = 0.5 * (Initial Fuel En Route + Final Fuel
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 IRR? Year 1 -$70,000 Year 2 $12,000 Year 3 $15,000
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 following sequence of cash flows: Year 1 -$10,000 Year 2 $8,000 Year 3 $1,500 Year 4 $1,500 Year
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 called the yield of the bond.Data from in problem 8Consider a project having the following cash flows.
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 cost of capital is 8 percent a year, determine the total present value of her before-tax earnings.
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 has two support departments: Accounting and Consulting. Widgetco also has two product divisions:
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 on her investments, and during the last 10 years before she retires, her investments will earn 5
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 profits (after bonuses are paid). Federal tax is 40 percent of profits (after bonuses and state tax
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 will I have after 12 months?
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 with $320. Determine the ending balance for the year if interest accrues on the average of our
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 and the last seven characters are a number 1 million or larger (with no spaces). Determine which
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 members is always 100. Suppose that Group 1 has 50 members, Group 2 has 30 members, and Group 3 has 20
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 0, then there is no average price. Error-trap the file named Dividebyprice.xlsx to ensure that all
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 number is divisible by 4. (Toggling means opening a closed locker and closing an open locker.) Next,
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 product in March.
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 and.
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 gives no error messages.
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; thereafter, unit sales remain constant. Each year, the product price increases by the inflation rate.
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 margin (the same each year). Cell D6 gives growth in market size during each year of growth, and D7
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 $3.50 per card. Cards sold at full price are sold for $6. Full-price demand is entered in cell B7. The
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 the odd-numbered rows of column C the person’s name followed by their units sold (separated by a
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 large and failed the June audit, their next audit will be in July. If the firm is large and passed the
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 correct answer to Problem 1 was B, not A. Write IF statements to efficiently update each student’s
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 people joining the company in February and a list of people leaving the company in February.
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 total minutes did each runner take?How many total seconds did each runner take?
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 statistical results are listed in rows 4 through 20. Change the worksheet so that all player names are listed
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 0.919. Change the worksheet so that all shooting percentages are listed as numbers, from 1 through
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 purchased varying between 0 to 100 (in increments of 10), determine the percentage return on your
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 difference in payments between a 15-year, 20-year, and 30 year mortgage.
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 longitude. Create a table that computes the distance between any pair of cities listed.
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 given ID code and yields the employee’s years of experience.
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 products. Determine the total cost of the hardware store’s purchases.
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 formulas to fill in the indicated data.
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 to this data?
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 1 Boston 2 Chicago 3 Dallas 4 Denver 5 LA 6 Miami 7 Phoenix 8 Seattle C 1991 3389 0 983 1815 1991
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 travel?
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 range O12:Q26.
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 O12:Q26.
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 street address to another.
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 command on the Data tab on the ribbon to accomplish the same goal.
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 hyphen). Its color is 65, and its size is L. Use text functions to extract the style, color, 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 form suitable for numerical calculations—for example, transform 10.4m into the number 10.4.
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, followed by the first and middle names. For example, transform Gregory William Winston into Winston,
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, through Z.
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 capitalized.
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 purchase. Determine the year of each purchase.
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 $31,881How would you efficiently extract the cash and cash equivalents data for each company?

Showing 400 - 500 of 914

Microsoft Excel Data Analysis And Business Modeling 7th Edition Wayne Winston - Solutions

Step by Step Answers