1. Lists the members of your class.
Redo the list to have them in first name last name order, no comas.
Useful functions include find, left, right, length, concatenate.
The formula in the column with the answer should not reference any cells containing a formula.

2. Sum the column of numbers.
End down will get you to the bottom of the list.
To determine if you are right use the formula n*(n+1)/2 to determine the value you should have gotten.
You did not get the right answer because some of the numbers are text.
Use one of the text formulas for each cell in column A cell and place the result in the corresponding cell in column B.
Sum column B.
You should now have the right answer.

3. Add the column of numbers.
The answer should be 1,900.51.
If that is not what you get, think of the shape of a circle.

4. This is an exercise in drop and drag and the benefits of the F4 key. Just follow the directions.

5. To calculate present value, the formula is (1/((1+r)^n)).
Enter the formula only once.
The F4 key will be useful.
Drag it into the remaining cells.
Format your answer to four decimal places.
The answer for 6.5 percent for thirty years is .1518.

6. Calculate the ratios.
Use conditional formatting to format the results.
• If the ratio is less than 1, four decimal places.
• If the ration is greater than 1, two decimal places.
Select all the cells you want to format.
Select conditional formatting in the home tab.
Select “use a formula to determine which cells to format” to determine format.
Use two formulas, one for two decimal places and one for four decimal places.

7. Generate eight income statements in the following format using the rand command.
Only differences should not include a rand command.
Create your formulas so there are no losses.

8. Use COUNTIF to determine how many grades each person has.
Use SUMIF to determine their total score.
Calculate average grade.
Use VLOOKUP to complete table 3.
Note: The names are for the people in the classes, but the grades were generated randomly so they do not reflect actual grades.

9. Use IFERROR to calculate average cost so when the quantity is zero, the average cost is zero and not div/0

10. Create an amortization schedule for a $100,000 loan, six percent interest rate, payments monthly, term five years, payment due the last day of the month, first monthly payment due January 31, 2017.
Determine the payment using the PMT function.
The last payment must be different to make the balance zero.

11. Determine the following statistics for the column of numbers:
a. Sum
b. Average
c. Median
d. Standard deviation
e. Minimum
f. Maximum
g. The average of those numbers greater than 99.

12. In this problem you will be solving for interest rate.
You lease a vehicle that cost $35,000 for three years.
The expected residual value is $24,000.
The monthly payments are $465.
Determine the present value of the residual value.
Set up an amortization table.
Use WHAT IF ANALYSIS GOAL SEEK to determine the interest rate.

13. Transpose the table so that rows are A-P and the columns are 1-20 and the corresponding values are correct.
• Select the range of data you want to rearrange, including any row or column labels, and press Ctrl+C. NOTE: Make sure you copy the data to do this. Using the Cut command or Ctrl+X won’t work.
• Right-click cell A25 and select paste and pick Transpose.

14. Determine the age of each of people with the birthdates in the list.
• Use MONTH, DAY, and YEAR functions and then combine the results in year month day format as an eight digit number.
• Subtract the result from today in year month day format
• The first two digits are the age

15. Calculate the interest for each quarter of a $10,000 investment with an annual interest rate of 4.28% based on the number of days in the quarter for 2017.
Determine the number of days in the quarter using the YEARFRAC function.

16. Your client does not understand the difference between US GAAP and IFRS for lower of cost or market. You must prepare a table that presents an example which you will use in a presentation to him and his staff. Since you do not know the difference you research the difference and find the following:

US GAAP
• Cost is based on invoice price or the cost of manufacturing
• Replacement cost is current purchase price or cost of manufacturing.
• You pick the smaller of the two.
• The result cannot be more than the current selling price minus the cost to sell it.
• The result has to be more than the current selling price, minus the cost to sell it, minus the normal profit margin for that product.
IFRS
• Cost is based on invoice price or the cost of manufacturing
• Net realizable value is calculated as the current selling price minus the cost to sell it.
• You pick the smaller of the two.

17. Take the data in the spreadsheet and copy it into a Word table and make it look like this: (A B C D E F)

1 5.08 7.43 11.38 16.32 22.57 31.28
2 6.02 10.53 20.81 32.24 34.99 36.59
3 11.30 18.79 25.10 44.57 83.79 86.95
4 15.36 20.20 35.28 79.12 145.75 255.66
5 19.69 27.48 43.06 121.36 234.82 321.43
6 29.66 35.86 64.23 173.54 313.57 494.23
7 46.94 88.72 135.49 294.35 424.21 530.90
• The table size is 8X7.
• The first column is .4 wide.
• The remaining columns are .7 wide.
• Select the design from the design tab.
• Center the table on the page. (right click, select Table Properties)

18. Create confirmations using Word’s mail merge, the format presented below, and the data from this tab. Replace any italicized words with the data from the table.

First name last name
Address
City State, zip

Dear first name,

We have examined the property tax rolls for Wood County Ohio and determined based on similar residences in the county you are overpaying real estate tax in the amount of amount. We will present your case to the proper authority for the amount we reduce your real estate tax bill in the first year as our fee, but the tax savings will continue until the next county wide assessment which is not scheduled until 2021. You have nothing to lose and everything to gain. Contact our office if you would like us to represent you in this matter by returning the enclosed form in the prepaid envelope.

Sincerely

Hedy Rosenthal
Rosenthal, Cohen and Stephens, Attorneys at Law

1. Lists the members of your class. Redo the list to have them in...