Given the following spreadsheet calculate the values of the Excel formulas below. Do NOT use excel to figure out the answers. Rather use a paper and pencil (or pen). Make sure to follow the order of operations. Rewrite the entire formula at each intermediate step.

1. =sum(b2:c3,average(1,9,5))*average(sum(3,b3),10)
2. =average(5,average(sum(c3,7,a1:a3)*5,3+2*4))*(2+3)*2

The following formula is in cell B7:

    =C9+$C9+C$9+$C$9

Answer the following questions

1. If the formula is copied to cell D10, what will the new formula in D10 look like?
2. If the formula is copied to cell A9, what will the new formula in A9 look like?

A spreadsheet contains the words "How are you doing?" (without the quotes) in cell A1.

Create formulas whose value is each of the following. The formulas should ONLY contain references to A1, calls to the LEFT and RIGHT Excel functions, and the concatenation (i.e. &) operator. Try to make your formulas as short as they can be.

1. are you doing
   (notice that there is no question mark here)
   
   
2. do you wing?
   
   
3. we are aging

Using the following spreadsheet, figure out the values for each of the following formulas. Show all your work - do not just write the answer.:

1. =A1>B1+B2*2-A2
2. =A1*A2<>A2*(B1+2*B2)
3. =B1+B2*2+1>=A1-A2+B1+31

The following spreadsheet is used to calculate the commissions for the salesmen in a company. The values of the commissions are based on the following rules:

• If sales is more than $1000 then commission is 10 percent of sales
• Otherwise commission is 5 percent of sales

The following shows the formula-view for the same spreadsheet (the first 5 rows are hidden).

The following questions present alternative rules for calculating commission amounts. For each question, write the formula that should be placed into cell C7 so that the proper commission is evaluated. (Only write the formula as it would appear in cell C7. It is understood that this formula would then be copied to cells C8, C9, etc.) The formula for each question should evaluate to a dollar amount.

1. Rules for commission: If sales > $1000 then commission is 10% of sales plus a $500 bonus. Otherwise the commission is exactly $200.
2. Rules for commission:The commission for the top selling salesmen is 20% of sales. (If two or more people tie for the most sales, then all of those salesmen get 20%.) The commission for all other salesmen is 10%.
3. Rules for commission: The person who sold the least amount gets no commission. (If two or more people tie for the least sales, then they all get no commission.) All other salesmen get 10% if sales is more than $1000 and 5% if sales is less than or equal to $1000. (HINT: you will need to use two IF functions - one inside the other.)

Question 1

Create the following spreadsheet

The spreadsheet shows the following calculations for squares and cubes of various sizes

• area of square (side * side)
• surface area of cube (6 * side * side)
• volume of cube (side * side * side)

Your spreadsheet should include the calculations for sides from 1 through 100 (the picture only shows the first few rows). Type in the formulas just ONCE on the first row of the chart (for a side of 1) and then copy the formulas to subsequent rows. Make sure to use "absolute" cell references where appropriate.

Question 2

Create the following spreadsheet

The spreadsheet shows the areas for rectangles with different lengths and widths (area of a rectangle is length*width). Use formulas to calculate the areas. DO NOT JUST TYPE IN THE NUMBERS.

Include the areas for combinations of lengths from 1 through 100 and widths from 1 through 100. (the picture only shows the first few values for length and width).

Type in the formula for the area only ONCE (i.e. in the cell that shows the area when length and width are both 1 - the area will be 1 too). If you use "absolute" cell references in your formula correctly, you will then be able to copy the formula to all the other cells and have the formula automatically be correct in the new cells.

Use the Excel PV, FV and PMT functions to figure out the answers to the following questions. Each answer should consist of the appropriate function call that will evaluate to desired value.

1. How much money do I need to put in the bank today to wind up wiht $100,000 after 10 years at a 5% annual interest rate that is compounded daily? For the purpose of this question you should assume that every year has 365 days, i.e. do NOT worry about leap years. (HINT: use the PV function)
2. How much money do I need to contribute every year to a wind up with one million dollars after 35 years. The money will be placed in an interest bearing account at 7% annual interest compounded annually. (HINT: use the PMT function)
3. How much money will I have after 10 years if I invest $10 today in an account with a 9% interest rate compounded monthly? (HINT: use the FV function)