Questions and Answers of Microsoft Excel Data Analysis 7th

Write a formula that returns a person’s age, given his or her date of birth. Use this method in order to calculate age of person if date of birth is 1944 if age is the name of function,
Labor Day is the first Monday in September. Write a formula that determines the date of Labor Day for a given year.
The file named Addresses.xlsx gives people’s names on one line, their street address on the next line, and their city, state, and zip code on the following line. How could you put each person’s
The file named Nancybonds.xlsx gives the bond rating for several bonds in a previous and future month. You want to efficiently count how many bonds were downgraded. Unfortunately, each company is
Martin Luther King Day occurs on the third Monday in January. Write a formula that computes (given the year) the date of Martin Luther King Day. First determine the day of the week for January 1 of
Let’s reconsider our pro forma example. Suppose the interest rate on our debt depends on our financial well-being. More specifically, suppose that if our earnings before interest and taxes (EBIT)
Suppose a stock is currently selling for $32. You buy a six-month European call option with an exercise price of $30 for $2.50 and sell a six-month European call option with an exercise price of $35
A European call option gives you the right to buy at a specified future date a share of stock for a given exercise price. A butterfly spread involves buying one call option with a low exercise price,
On the S&P moving-average example, suppose we still buy shares if the current price exceeds the 15-month moving average, but we sell if the current price is less than the five month moving
In a game of craps, on each roll of the dice after the first roll, the rules are as follows: If the game has not ended and the current roll matches the first roll, you win the game. If the game has
A drug company believes its new drug will sell 10,000 units in 2004. They expect two competitors to enter the market. The year in which the first competitor enters, the company expects to lose 30
We are bidding on a valuable painting. The high bid gets the painting. We estimate the painting’s value at $10,000. Four companies are bidding against us. It costs $400 to prepare the bid. Write a
You are bidding on a construction project. The low bid gets the project. You estimate the project cost at $10,000. Four companies are bidding against you. It costs $400 to prepare the bid. Write a
The same drug company is producing a new drug. The company has made the following assumptions:During Year 1, 100,000 units will be sold. Sales will grow for three years and then decline for seven
Do this problem independently of Problem 13. Suppose our firm wants a debt-to-equity ratio of 50 percent during each year. How would you modify the pro forma statement?Data from in Problem 13Let’s
Suppose the price of a product will change at dates in the future, as follows:Write a formula that computes the price of the product based on the date the product is sold. Date On or before February
A Blue Yonder Airline flight from Seattle to New York has a capacity of 250 people. The airline sold 270 tickets for the flight at a price of $300 per ticket. Tickets are nonrefundable. The variable
A major drug company is trying to determine the correct plant capacity for a new drug. A unit of annual capacity can be built for a cost of $10. Each unit of the drug sells for $12 and incurs
An NBA player is receiving $15 million at the end of each of the next seven years. He can earn 6 percent per year on his investments. What is the present value of his future salary payments?
You have just won the lottery. At the end of each of the next 20 years, you will receive a payment of $50,000. If the cost of capital is 10 percent per year, what is the present value of your lottery
I now have $250,000 in the bank. At the end of each of the next 20 years, I withdraw $15,000. If I earn 8 percent per year on my investments, how much money will I have in 20 years?
I plan to deposit $2,000 per month at the end of each month over the next 10 years. My investments earn 0.8 percent per month. I would like to have $1 million in 10 years. To achieve this, I will
A perpetuity is an annuity that is received forever. If I rent out my house, and at the beginning of each year I receive $14,000, what is the value of this perpetuity? Assume an annual 10 percent
Answer each question in Problem 7 assuming beginning-of-month payments.Data from in Problem 7You are borrowing $200,000 on a 30-year mortgage, with an annual interest rate of 10 percent. Assuming
At the end of each of the next 20 years, I will receive the following amounts:Years 1–5 .............................. $200Years 6–10 ............................
You are borrowing $200,000 on a 30-year mortgage, with an annual interest rate of 10 percent. Assuming end-of-month payments, determine the monthly payment, the monthly interest payment, and
Use the FV function to determine the value to which $100 accumulates in three years if you earn 7 percent per year.
You are going to buy a new car. The cost of the car is $50,000. You have been offered two payment plans:A 10 percent discount on the sales price of the car, followed by 60 monthly payments financed
You have a liability of $1,000,000 due in 10 years. The cost of capital is 10 percent per year. What amount of money do you need to set aside at the end of each of the next 10 years to meet this
Suppose you have borrowed money at a 14.4 percent annual rate, and you make monthly payments. If you have missed four consecutive monthly payments, how much should next month’s payment be to catch
A balloon mortgage requires you to pay off part of a loan during a specified time period and then make a lump sum payment at the end of that period to pay off the remaining balance of the loan.
An adjustable-rate mortgage (ARM) ties monthly payments to a rate index (say, the US T-bill rate). Suppose you borrow $60,000 on an ARM for 30 years (360 monthly payments). The first 12 payments are
I presently have $10,000 in the bank. At the beginning of each of the next 20 years, I plan to invest $4,000, and I expect to earn 6 percent per year on my investments. How much money will I have in
You want to replace a machine in 10 years, and you estimate the cost will be $80,000. If you can earn 8 percent annually on your investments, how much money should you put aside at the end of each
You are considering borrowing $50,000 on a 180-month loan. The annual interest rate on the loan depends on your credit score, as shown in Figure 10-8. Write a formula that gives your monthly payments
You are buying a motorcycle. You pay $1,500 today and $182.50 a month for three years. If the annual rate of interest is 18 percent, what was the original cost of the motorcycle?
Suppose the annual rate of interest for your motorcycle loan is 10 percent. You pay $200 a month for two years, $300 a month for one year, and $400 a month for two years. What is the present value of
You can invest $500 at the end of each six-month period for five years. If you want to have $6,000 after five years, what is the annual rate of return you need on your investments?
I borrow $2,000 and make quarterly payments for two years. The annual rate of interest is 24 percent. What is the size of each payment?
I have borrowed $15,000. I am making 48 monthly payments, and the annual rate of interest is 9 percent. What is the total interest paid over the course of the loan?
I am borrowing $5,000 and plan to pay back the loan with 36 monthly payments. The annual rate of interest is 16.5 percent. After one year, I pay back $500 extra and shorten the period of the loan to
With an ARM, you make monthly payments that vary depending on the interest rate at the beginning of each year. You have borrowed $60,000 on a 30-year ARM. For the first year, monthly payments are
You have a choice of receiving $8,000 each year beginning at age 62 and ending when you die or receiving $10,000 each year beginning at age 65 and ending when you die. If you think you can earn an 8
You have just won the lottery and will receive $50,000 a year for 20 years. What rate of interest would make these payments the equivalent of receiving $500,000 today?
A bond pays a $50 coupon at the end of each of the next 30 years and pays $1,000 face value in 30 years. If you discount cash flows at an annual rate of 6 percent, what would be a fair price for the
You have borrowed $100,000 on a 40-year mortgage with monthly payments. The annual interest rate is 16 percent. How much will you pay over the course of the loan? With four years left on the loan,
I need to borrow $12,000. I can afford payments of $500 per month, and the annual rate of interest is 4.5 percent. How many months will it take to pay off the loan?
You are going to borrow $40,000 for a new car. Calculate the monthly payments and total interest paid for the following situations:48-month loan, 6.85 percent annual rate60-month loan, 6.59 percent
Consider a machine that costs $50,000 and will be depreciated over 10 years to a final salvage value of $5,000. Determine the depreciation during each year for the SLN, SYD, and DDB depreciation
You have invested $10,000 today, which will compound daily at an annual rate of 6 percent. How much money will you have in a year?
I plan to put $200 in the bank at the end of each month for 60 months. The annual rate is 8 percent, compounded monthly. How much money will I have in 60 months?
Suppose $100 grows to $106 when interest is compounded monthly. What annual rate of interest would yield this result?
Use LOOKUP functions to compute for a given date and day of the week the date of the next date that occurs on the listed day of the week. For example, for 4/30/2021 and Monday, your formula should
Write formulas that return the first and last days of the month for the months 12 months before and after 2/28/2025.
Given any date, find a way to compute which day of the year it is. For example, what day of the year will 4/15/2020 be? Hint: The formula DATE(2020,1,0) will return the serial number for day 0 of
Memorial Day is always the last Monday in May. Set up a worksheet in which you can enter the year, and the worksheet determines the date of Memorial Day.
Use LOOKUP functions to compute for a given date and day of the week the date of the most recent day of the week on or before the given date. For example, for 4/30/2021 and Wednesday, your formula
In Fredonia, employees have Tuesdays and Wednesdays off. What date is 200 workdays after 5/3/2013?
Create a worksheet that always lists the next 50 workdays (assuming no holidays and that Monday–Friday are the workdays).
Suppose the first quarter of each year is January–March, the second quarter is April–June, and so on. Write a formula that returns for a given date the first day of the quarter.
In Lower Ampere, employees have Fridays and Saturdays off. Also, Valentine’s Day is a holiday. How many workdays are there between 1/10/2014 and 5/31/2015?
Determine the day that occurs 74 workdays after today’s date (including holidays).
How many workdays (including holidays but excluding Christmas, New Year’s Day, and Independence Day) are there between July 10, 2005, and August 15, 2006?
What is the serial format for January 25, 2006?
What is the serial format for February 14, 1950?
The file named Emailproblem.xlsx gives first and last names of several new Microsoft employees. To create an email address for each employee, you need to follow the first letter of the employee’s
The workbook named Quarterlygnpdata.xlsx contains quarterly GNP data for the United States (in billions of 1996 dollars). Extract this data to three separate columns in which the first column
The file named FootballProblem8.xlsx shows statistics on NFL quarterbacks. Enter a QB’s name in cell G3 and a statistic in cell H3. Then write a formula that returns in cell I3 the QB’s
In the file named Baseballproblem7.xlsx, you are given Major League Baseball teams’ statistics. Enter a team’s name in cell I2 and a statistic in cell J2. Write a formula in cell K2 that returns
The file named Salesdata.xlsx contains the number of computers in a store sold by each salesperson. Create a formula that returns the units sold by a given salesperson. 1 2 345 6700
The file named Buslist.xlsx gives the amount of time between bus arrivals (in minutes) at 45th Street and Park Avenue in New York City. Write a formula that, for any arrival time after the first bus,
The file named Matchthemax.xlsx gives the product ID codes and unit sales for 265 products. Use the MATCH function in a formula that yields the product ID code of the product with the largest unit
The file named Matchtype1.xlsx lists in chronological order the dollar amounts of 30 transactions. Write a formula that yields the first transaction for which the total revenue to date exceeds
Using the distances between US cities given in the file named Index.xlsx, write a formula using the MATCH function to determine (based on the names of the cities) the distance between any two of the
“Lookup functions.” Here is the problem again: The file named Employees.xlsx contains the rating (on a 0–10 scale) that each of 35 workers has given to three jobs. The file also gives the job
Jerry Jones and the Dallas Cowboys are embarking on a road trip that takes them to Chicago, Denver, Los Angeles, Phoenix, and Seattle. How many miles will they travel on this road trip?
Use the INDEX function to compute the total distance from Dallas to the seven other cities listed in Figure 4-1. 123 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 A B Boston-Denver Seattle- Miami 1
The file named Assign.xlsx gives the assignment of workers to four groups. The suitability of each worker for each group (on a scale from 0 to 10) is also given. Write a formula that gives the
The file Grades.xlsx contains students’ grades on an exam. Suppose the curve is as follows:Use VLOOKUP formulas to return each student’s letter grade on this exam. Score Below
The file named Lookupmultiplecolumns.xlsx contains information about several sales made at an electronics store. A salesperson’s name is entered in B17. Write an Excel formula that can be copied
In the file named Pinevalley.xlsx, the first worksheet contains the salaries of several employees at Pine Valley University, the second worksheet contains the ages of the employees, and the third
The file named Qb2013.xlsx gives NFL quarterback (QB) statistics for the 2013 season. Write formulas in cells J2 and K2 that return the QB’s TDs and interceptions when you type a QB’s name in
The file named Employees.xlsx contains the rating (on a 0–10 scale) that each of 35 workers has given to three jobs. The file also gives the job to which each worker is assigned. Use a formula to
Redo Problem 9 using XLOOKUP formulas.Data from in Problem 9The file Grades.xlsx contains students’ grades on an exam. Suppose the curve is as follows:Use VLOOKUP formulas to return each
The file Stock.xlsx contains monthly stock returns for General Motors and Microsoft. Name the ranges containing the monthly returns for each stock and compute the average monthly return on each
Create a range name that averages the last five years of sales data. Assume annual sales are listed in a single column.
A person’s body mass index (BMI) is computed as BMI=703*Weight/Height2. Set up a worksheet to compute a person’s BMI.
In my class, I give five homework assignments, each worth 25 points, and three exams, each worth 100 points. I currently compute a student’s final grade by giving 75 percent of the weight to exams
A curious economist dropped 1,008 wallets in city, rural, and suburban locations. For each wallet, you are told whether or not the wallet was returned. You want to use the results to determine
Your MBA team members are Wayne, Taylor, Britney, and Katy. Create a macro that enters the team members in cells A1:D1.
Create a macro that will place a bold red border around any set of selected cells.
Create a macro that will format any selected range in bold italics.
Pressing Ctrl+A selects all cells in the current worksheet. Given this fact, create a macro that will ensure that each cell in a worksheet is formatted in boldfaced italics.
The annual rate your bank charges you to borrow money for 1, 5, 10, or 30 years is shown in the following table.If you borrow money from the bank for any duration from 1–30 years that’s not
You are thinking of advertising Microsoft products on a popular TV music program. You pay one price for the first group of ads, but as you buy more ads, the price per ad decreases, as shown in the
Often, I want to freeze the results of a range resulting from formulas and then change the formulas to values. Create a macro that will find, based on the selected cell, the largest range of cells
The distance between any two US cities (excluding cities in Alaska and Hawaii) can be approximated by the following formula:The file Citydata.xlsx contains the latitude and longitude of selected US
Create a macro that will highlight an entire column of data in yellow. Your macro should work no matter how many rows of data there are in the column.
You are thinking of advertising Microsoft products on a sitcom. As you buy more ads, the price of each ad decreases, as shown in the following table.For example, if you buy eight ads, you pay $11,000
Write a LET formula that multiplies two numbers and adds a third number to the product.

Showing 700 - 800 of 878