You are working at a retail establishment that sells numerous things in bulk. Customers that buy a

Fantastic news! We've Found the answer you've been seeking!

Question:

You are working at a retail establishment that sells numerous things in bulk. Customers that buy a lot on a consistent basis get larger discounts. Also, your costs are constantly changing and you get discounts if you order more. You want to create a sheet that allows you to quickly change things so you can print off a current sheet to give fast quotes over the phone for different quantities of an item to your different customers.

1. You buy the items from China 10,000 at a time and sometimes more. In cell A2, simply put 10,000. In cell A1 above, put the label, Items.

2. In cell B2, put $36 and label above it Price.

3. In cell B4, put the label Discount and in A5, Quantity.

4. You sell the items by the dozen, literally. Put 12 into A6 and then in A7 create a formula that increases the Quantity by 12 and copy it down until you get to 144 (a gross).

5. For your discount, start with five percent in the cell under Discount, B5. Then, create a formula that adds five percent to the previous five percent and copy it across row 5 until you get to twenty percent.

6. In cell B6, create a formula that solves for the total price at each quantity at each discount. You must use anchoring correctly and when you do, the formula can be copied to the remaining cells to fill in the table. Fill in the table.

7. You also want to have the same table for 100,000 items. First, copy the values of the table you just made to the right of the formula table. Start your copy at A1 and copy the entire table through E17. Place the values starting in G1.

8. Now, go back to the original table and insert 100,000 for items and $34for price. Format all the prices in Currency format. You are done with this problem.

9. We need to start a new sheet. Not a workbook, a sheet. At the bottom of the current sheet are called tabs. Hit the + in the circle near the tab saying Sheet1. You will get and use this new Sheet2. You just inherited $100,000 from your late grandmother. You want to save it for retirement many years from now. You want to see how different interest rates will affect the final sum of money you have.

10. Label cell B1 Rate and A2 Years. In cell A1, put $100,000.

11. Start in A3 with 30 years and increase down by 5 years until you reach 50 years. (30, 35, etc.)

12. In B2 start with five percent and increase each cell to the right by one percent until you reach twelve percent. (.05, .06, etc.)

13. Write a formula in cell B3 that you can copy to the remaining cells that calculates the future value of the $100,000, referencing all the appropriate cells using anchoring. Do not use the Excel FV function or you will not receive credit.

14. Again format the appropriate values in Currency format.

15. NEW PROBLEM. Directly below the table you just created, I want you to recreate the same table only this time use the FV command and the Excel 2-way function. You should first create the PV, FV, PMT, RATE, N, M cells. Begin labeling in A9 and go down. Fill in the inputs accordingly. To recreate the 2-way table, please put it directly to the right of the PV, FV etc. values. You will need to move the Years label. Move it to the bottom of that column. (Note, it does not matter what your starting input values are other than the initial $100,000.) Create the table.

16. Finally, starting in cell A17, you want to find out what would be the value of a house you can buy at different interest rates, if you pay $900 per month on the loan. You will get a 30 year loan paid monthly. Do a 1-way table to see how interest rates affect the value. Start the PV, FV, PMT cells at A17 going down.

17. Start the interest rates at 2.75% in cell D17 and increase by a quarter percent to the right in row 17. 18. Finish the table using the Excel table function