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.

There is no error in this question. The beginning should be set up like this:

 

AutoSave Book3 OFF Home Insert Draw Page Layout Formulas Data Review View Tell me A Share O Comments .Q, X A Insert v Calibri (Body) v 12 v A A = = = ab wrap Text v General E Delete v E Merge & Center v $ % 9 Sensitivity Paste U v Conditional Format Cell Sort & Filter B I Find & Ideas Formatting as Table Styles H Format v Select E17 fx A C F G J 1 Items Price 2 10000 36 3 4 Discount 5 Quantity 0.05 0.1 0.15 0.2 12 7 24 8 36 48 10 60 11 72 12 84 13 96 14 108 15 120 16 132 17 144 18 19 Sheet1 Ready 210%