Excel Assignment 2

Churchill General Store Sales, Profits and Inventory: Part 2

After sending out your one-page flyer advertising your product(s), your popular general store is growing by leaps and bounds. You are no longer able to keep track of your inventory, sales and profits on scraps of paper. Instead, you decided to use Microsoft Excel to create a spreadsheet that will keep track of your inventory, sales and profits.

Now that you have created the basic outline for your spreadsheet, you are going to input the necessary formulae to tabulate your records.

  1. Start Excel, open your CGS Spreadsheet from your folder on the network drive. Make sure you have the necessary toolbars/rulers displayed.

Inventory:

  1. In cell D7 (Quantity in Warehouse for Item 1) enter 22. In cell D8 enter 46, in cell D9 enter 38.
  2. In cell E7 (Quantity in Showroom for Item 1) enter 19. In cell E8 enter 17, in cell E9 enter 15.
  3. In cell F7 enter a formula that calculates the total inventory for Item 1 (Warehouse + Showroom). Do the same in cells F8 and F9 (the totals for Item 2 and Item 3).
  4. Make the font in cells F7, F8 & F9 blue and bold.
  5. In cell D10 enter a formula that calculates your total Warehouse Quantities.
  6. In cell E10 enter a formula that calculates your total Showroom Quantities.
  7. Make the font in cells D10 & E10 red and bold.
  8. In cell F10 enter a formula that calculates your total quantities (Warehouse and Showroom for all items).
  9. Bold the font in cell F10.

1st Quarter Sales:

  1. Enter your 1st quarter sales for Item 1 for January, February and March as 132, 98 and 17.
  2. Enter your 1st quarter sales for Item 2 for January, February and March as 78, 62 and 23.
  3. Enter your 1st quarter sales for Item 3 for January, February and March as 148, 77 and 9.
  4. In cell G15 enter a formula that adds your total sales for January, February and March for Item 1.
  5. In cell G16 enter a formula that adds your total sales for January, February and March for Item 2.
  6. In cell G17 enter a formula that adds your total sales for January, February and March for Item 3.
  7. Make the font in cells G15, G16 & G17 blue and bold.
  8. In cell D18 enter a formula that calculates your total January sales.
  9. In cell E18 enter a formula that calculates your total February sales.
  10. In cell F18 enter a formula that calculates your total March sales.
  11. Make the font in cells D18, E18 & F18 red and bold.
  12. In cell G18 enter a formula that calculates your total sales.
  13. Bold cell G18.

1st Quarter Profits:

  1. In cell D24 enter a reasonable cost for Item 1, in D25 enter a cost for Item 2, in D26 enter a cost for Item 3. NOTE: The cost is the price you pay for your product before selling it.
  2. In cell E24 enter the retail price for Item 1, in E25 enter the retail price for Item 2, in E26 enter the retail price for Item 3. NOTE: The retail price is the amount you sell your product for (it must be higher than your cost).
  3. In cell F24 enter a formula that calculates your profit per unit sold (retail price minus the cost) for Item 1.
  4. In cell F25 enter a formula that calculates your profit per unit sold (retail price minus the cost) for Item 2.
  5. In cell F26 enter a formula that calculates your profit per unit sold (retail price minus the cost) for Item 3.
  6. In cell G24 enter a formula that calculates your total profit for Item 1. This should be equal to your profit per unit for Item 1 multiplied by your total sales for Item 1.
  7. In cell G25 enter a formula that calculates your total profit for Item 2.
  8. In cell G26 enter a formula that calculates your total profit for Item 3.
  9. In cell G27 enter a formula that calculates your total profits.
  10. Bold and italicize cell G27.

Save as Excel 2 in your folder on the network drive.