Spreadsheet Assignment #8: Shop Stock Calculator (Marked/Summative)

PROGRAMS: MS Excel

FOUNDATIONAL OBJECTIVE(S):

  • To be familiar with basic hardware and software terminology and features.
  • To develop information processing skills that will be helpful in a student’s personal life.
  • To use computer hardware and software to produce simple personal use documents.
  • To use a variety of computer software applications efficiently and productively.
  • To develop communication skills necessary for information processing.

SKILLS: Wrapping Text, Merging Cells, Calculating: Unit Cost, Units Sold, Sales, Profits, Profit Margins

Stock Calculator

You have been hired to track sales at a local store. They are committed to taking inventory at the beginning of each week and comparing that to the inventory remaining at the end of the week. Inventory is taken according to the number of unopened boxes and the loose stock in opened boxes.

 

The following spreadsheet is a suggestion of what a candy shop stock calculator would look like to calculate the sales and profits for the items sold in the store over one week. The spreadsheet should be set to calculate a number of values that help to track cash flow and inventory ordering. For this assignment, you will be creating a spreadsheet that provides similar information. You can choose to create a candy shop similar to this one using numbers and items of your own choosing, or you can create a store that peaks your interest – an electronic shop, the servery, etc. You must insert FORMULAS as much as possible when determining calculations.

You need to have at least 16 items in your spreadsheet in at least 4 categories. None of the costs for any of your items should be the same as others in your list (Although their selling prices may be the same). You may include additional items or categories if you would like.

  1. Create a chart to include the title of the business, as well as the same criteria listed in the sample below: # of items per box, cost per box, opening stock, closing stock, etc.
  2. You can determine your 4 categories, and shade them in a color of your choosing.
  3. Under each category, you must list at least 4 items.
  4. Your first column is for #/box. On the sample below, Kit-Kats can be ordered in boxes of 20. You can fill in the #/box based on your own estimates. You will also need to determine the cost per box. You can research this if you wish, or come up with reasonable estimates.
  5. For the Opening Stock column, how many boxes will you have at the start of the week, and how many loose items will you have? Your loose stock is any remaining items that are leftover and do not make up a full box. For example, at the start of the week we have 2 boxes of Kit-Kats and 7 individual Kit-Kat bars.
  6. At the end of the week, how many boxes remain and how many individual items are left over? You can estimate these numbers, based on how popular you think they would be and how many sales you might get in a week.
  7. To determine the unit price, take the cost per box and divide it by the number of items per box. This will tell you how much the shop owner is paying per item.
  8. The selling price is how much the owner is selling the item to the public. This should be higher than the unit cost in order to create a profit.
  9. To determine units sold, you must calculate the total stock you had at the beginning of the week (items per box X # of boxes plus loose stock) and subtract it from how many were remaining after closing stock (items per box X remaining # of boxes plus loose stock).
  10. Determine sales by the number of units sold X selling price.
  11. Determine your profit by the number of units sold X the unit price, then subtract that number from the sales.
  12. Create totals for sales and profits.
  13. Once you have completed the table and inputted the information, you need to create the following graphs. Please create each graph on a separate worksheet in the spreadsheet and label the graphs according to their contents.
    1. A bar chart for the profit totals for each category.
    2. A separate pie chart for each category which shows the sales for each item. (For example, a pie chart titled Chocolate Bars which shows the number of sales for each of the 4 chocolate bars)

 

Sweets

 

***Click HERE for the rubric you will be marked on!!

When you are satisfied with your assignment, hand in the completed document to the appropriate In Basket folder. (Remember to save it lastname,firstname…)