Assessment Test

This is the file needed to answer this question: Question 1

You are given unit prices for products sold by a wholesales supplier to a hardware store. In yellow you are given products ordered by a hardware store. To the nearest dollars, what does the hardware store owe the wholesaler?

I would like to create a range name that will expand when I add new data to the bottom of my spreadsheet. After clicking on Define Name from the Formula Tab I should use the _________ function to create my range name. (One Word Answer)

I have a 200 worksheet workbook and need to put the number in cell D1 of each worksheet into the first worksheet. To simplify this tedious task I should use the ___________ function. (One word answer)

This is the file needed to complete this problem: Assessment Question 4 File

If my cursor is in the range C7:D395 and I want to select this entire range of data I can hold down the Control Key, then the shift key, and then which key? (One word answer)

This is the file needed for question 5: NBA Minutes

You are given how many minutes a player played in each of 20 NBA games. How many total minutes (rounded to nearest integer) did they play?

This is the file needed for this problem: MATRIX

The cell range in yellow is a 32 by 32 matrix. The sum of the diagonal entries is:

You are given the sales of products at the Quick-Mart and want to summarize sales with a pie graph. If new products are added you want your chart to automatically update. Before creating your pie chart you should select the range of data and after holding down the Control key you should type what letter?

This is the file needed to answer this question: POINTS

You are given points scored by a football team and their opponent. I tried to determine how many points my team won or lost each game by and got error messages. To eliminate these error messages, I should use the _______ function. (One word answer)

This is the file needed to answer this question: Dates

In Column F, I attempted to write a sentence involving each person’s birth date. The dates don’t show up with the format used in Column D. To remedy this problem I should use the ________ function. (One Word answer)

This is the file needed for this question: Display

I have Excel (2013 or newer) and want to display the Column G formulas in Column H. I should use the _____ function. (One Word Answer)

In Excel 2016 or Office 365 I can summarize hierarchical data using a  ______ chart. (There are two acceptable answers, each of which is a single word, choose either one)

If I wanted to ensure that a cell always contains the current date and time after typing an equal sign what should I type in the cell?

This is the file needed for this problem: Sales

Before 4/10/2015 I charged $5 for a product. Between 4/10/2015 and 5/31/2016 I charge $6 for a product. After that date I charged $7 for a product. You are given the dates 1 unit of the product was purchased by customers. How much in total was I paid, rounded to the nearest integer?

This is the file needed to answer this problem: 100 Passes

I Created a drop-down box in G2 that allows you to select an NFL team. I want the entire row of data to be highlighted in yellow for each QB on the selected team who threw >=100 passes. After selecting the cell range D8:Q50 I chose Conditional Formatting and Use a Formula. Which formula does the trick?

I have a workbook with 12 worksheets, one for each month of the year.Each worksheet contains sales for that month of all my products. If I want to summarize this data in a single PivotTable then I should click the ALT key followed by which sequence of letters?

This is the file needed to answer this question: Touchdowns

How many TD’s did the Quarterbacks in Column B throw?

This is the file needed to answer this question: Ignore Errors

In cells E11:E16 I tried to use a VLOOKUP to find employee salaries. Since JR and Josh do not work here (they are on the Bachelor!) I received an error message. In E18 I tried to add up the salaries with the SUM function and received an error. What Excel function could be used to ignore the error messages when computing the sum? (One word answer)

This is the file needed to answer this question: Product Family

The first character is the Product Family. Everything before the hyphen is a product code and the number after the hyphen is units sold. What are the total unit sales for Product Family B?

This is the file needed to answer this assessment question: Median

You are given the number of points scored by each NFL Team in 2016. The median number of points scored is:

The data in Problem 19 (the previous Problem):

This is the file needed to answer this assessment question: NBA Salary

We are given information on 80 NBA players. The number in each row is the player’s salary (in dollars). To the nearest dollar what is the total salary of these players?

This is the file needed to answer this assessment question: Present Value

If we discount cash flows at 10% per year, what is the present value (as of 1/1/2020) of the cash flows from the file above? (rounded to the nearest integer)

This is the file needed to answer this assessment question: Cash Flow

What annual discount rate makes the present value (as of 1/1/2020) of the cash flows 0?

This is the file needed to answer this assessment question: BETA

For 146 months you are given the monthly return on the market and Pfizer. Based on this data, the Beta for Pfizer is:

If we toss a fair coin 200 times, what is the chance that you observe between 90 and 120 heads inclusive?

Assume that the average number of accidents a teenage driver has in a year is 0.4. What is the chance a teenage driver has >=2 accidents in a year?

Assume demand for a drug in the year 2025 follows a normal random variable with a mean of 100,000 and standard deviation of 15,000 pounds. What is the chance demand in 2025 is <=90,000 pounds?

Assume demand for a drug in the year 2025 follows a normal random variable with a mean of 100,000 and standard deviation of 15,000 pounds. There is a 7% chance demand in 2025 is >= ____ pounds?

This is the file needed to answer this assessment question: Chip Sales

In the above file, you are given data on units sold for 882 transactions. Total sales of Chip 1 in Asia are?

Using the previous file, what is the number of rows involving sales of Chip 5 in Latin America?

Using the previous file, for all transactions involving Chip 6 in Africa in May the average number of units sold is?

This is the file needed to answer this assessment question: World Population

You are given the population of world countries. What percent of the given populations begin with 1,2,3 or 4?

This is the file needed to answer this assessment question: PivotTable

You are given data on 869 transactions which are summarized using a PivotTable in the file above.  For the month of May I would like to extract sales in each location of each product in the range P13:Y18. The results should update if the PivotTable is refreshed. To accomplish this task efficiently, I should use the _____ function. (One word answer)

This is the file needed to answer this assessment question: Income Growth

My income in Year 1 is $80,000. I will work for 35 years and then retire. Assume all income is received at beginning of the year, then I consume from my income and compute my cash position. My cash earns the return on savings given below

Annual Income Growth 5%
Annual Interest Rate 10%
Fraction of income consumed 90%

How much money will I have at end of year 35? Your spreadsheet must work if I change information in C6:C8 I filled in years 1 and 2, and you will need formulas to accomplish this.

In problem 34 suppose you fix annual income growth at 5%. You want to see how sensitive your ending cash is to changes in annual investment return and consumption percentage. You should use a ___   ____. (Two Words)

In problem 34 suppose you fix annual income growth at 5% and annual investment return is 10%. To ensure an ending cash position of exactly $1.5 million your annual consumption percentage should equal?

I am borrowing $400,000 to buy a house. I will make 240 equal end of month payments. If the annual interest rate is 8.4%, each monthly payment should be:

This is the file needed to answer this assessment question: Max Profit

ShoeCo manufactures three types of shoes. A pair of  shoes requires labor and machine times given in the file above. The profit for each pair of shoes is given. Assuming demand is unlimited and 40 hours per week of machine time and labor are available, determine the maximum attainable weekly profit. (Rounded to the nearest integer)

This is the file needed to answer this assessment question: Backpack

I am trying to fill a backpack with items that will give me maximum benefit on a hiking trip. The weight in pounds, and benefit for each item are given in the above file. The backpack can hold at most 26  pounds. At least one drink (water or Gatorade) and at least one protein (cheese or beef jerky) must be packed. What is the maximum benefit I can get from my backpack?

Assume the demand for suits at the Men’s Wearhouse is linear with a price elasticity of 5. At the current price of $500 they sell 60 suits per week. Their cost per suit is $200. The typical man buys 2 ties and one shirt with a suit. Profit per tie is $15 and profit per shirt is $25. What price should the Men’s Wearhouse charge for a suit?

This is the file needed to answer this assessment question: Pass

In column B of the file above, you are given descriptions of Houston Texans plays. A play that has the word pass or sack is a passing play. How many passing plays are there?

Consider a 10 year bond with $1000 face value that pays an 8% annual coupon at the end of years 1,2, … 10. If the annual discount rate is 7% what is a fair price (to the nearest dollar) for the bond?

This is the file needed to answer this assessment question: Run Play

In Column J you are given descriptions of Houston Texans plays Column K is points gained on each play. For example, the row 7 play gained 5.27 points. A play is a run if it is not a pass. What is the average points gained per run play?

This is the file needed to answer this assessment question: Chips Used

The displayed PivotTable in the above file gives total units sold for Chips 5 and 7 for each location. If you want your worksheet to display the Chips used to compute the totals shown you should create the following: (answer is one word)

This is the file needed to answer the assessment question: Slice and Dice

In the file above, the PivotTable summarizes sales of each product in each location. If we want to slice and dice this data by time period we should use the tool shown above. This tool is an example of a ______. (one word answer)

This is the file needed to answer this assessment question: Daily Sales Summary

A bank has branches in 40 US cities. You are given the number of transactions (in 1000’s) in each city. We have used Excel’s ______ feature (one word answer) in column H to summarize daily sales in each city.

Excel 2016 allows you to easily create ______ charts that help you understand the cumulative effect of sequentially introduced positive or negative values. (One word answer)

Which of the following keys enables you to recalculate all formulas in a workbook?

If you want to easily dollar sign a cell references in a formula you can use which key?

At the end of each year for 40 years you put $2,000 into a savings account. If your money earns 8% per year how much money will you have in 40 years?