• Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month
Page
  1. 1
    1
  2. 2
    2
  3. 3
    3
  4. 4
    4
  5. 5
    5
  6. 6
    6
  7. 7
    7
  8. 8
    8
  9. 9
    9
  10. 10
    10
  11. 11
    11
  12. 12
    12
  13. 13
    13
  14. 14
    14
  15. 15
    15
  16. 16
    16
  17. 17
    17
  18. 18
    18
  19. 19
    19
  20. 20
    20
  21. 21
    21
  22. 22
    22
  23. 23
    23
  24. 24
    24
  25. 25
    25
  26. 26
    26
  27. 27
    27

Spreadsheet Report for Tuck Shop

Extracts from this document...

Introduction

Spreadsheet Report for Tuck Shop Introduction: I am going to produce a spreadsheet for Mrs Smith; it will keep record of products in a tuck shop. It will keep record of the following things shown below under 'User Needs'. When the spreadsheet is completed it will have information and data for two weeks. User Needs: My spreadsheet enables Mrs Smith the owner of a tuck shop to: * Find out and keep record of: - The quantity and products that were bought, sold, owned, stored, and expired. - Money spent on each product and received from the product (including total) - Money lost from expired products (including total) - Whether or not a product was a profit or loss - The amount of from the profit or loss (including total) - Whether or not there was a total profit or loss for each product and overall - The total profit or loss at the end of each week - Total money made from all weeks - Average Profit or Loss per week * To analyse products with high profits or losses * To see which products have made up the total money received Data needed: Data needed to construct this database is: * Product information data - names of products. Quantity of each product bought, owned, sold, stored, and lost. * Product expense data - price of each product bought, sold (1 item), the total of each product bought and sold, and the over all price bought and sold for all products. * Profit and losses data - profit or loss for each individual product and the amount of the profit or loss. The total money made for the week, whether or not there was a total profit or loss, the total money made from all weeks, and the average overall profit/loss made so far (per week). The data can be collected in different ways for the different data that is needed, they are: * Product information data - I would collect the ...read more.

Middle

The 'Quantity Stored' needed a simple SUM formula to work out the quantity of products owned with minus the quantity sold. I used the formula =SUM(C5-D5) I then copied the formula down to the other cells in the 'Quantity Stored' column. Using the formula =SUM(C5-D5) I used an If function for the 'Quantity Lost', it tells you whether or not there are any products lost by showing either 'YES' or 'NO'. The formula is: =IF(D5<'Product Information (week 1)'!E5,"YES","NO") The formula is saying that if E5 on the 'Product Information' (week 1)' sheet (Quantity Sold) is less than D5 (on 'Product Information' week 2) the cell should show 'YES' and if it isn't it should show 'NO'. This is correct because the stored food only lasts for one week, so if the amount sold in week 2 is less than the amount stored it is lost. If Statement formula =IF(D5<'Product Information (week 1)'!E5,"YES","NO") I have now finished Sheets 1 and 4(Product Information Sheets 2 and 5 (Product Expenses) I entered the column headings shown below. I also labelled the sheet 'TUCK SHOP' and typed in the week and date, but first I had to format the cell by right clicking on it and selecting 'Format cells'. I also wrote 'Week 1' to indicate which week it is. I formatted a cell for the date and entered it in like I have shown before. I changed the back ground of rows, merged cells for the title and froze panes the same way as I have shown before. I formatted some cells (like I have shown before) in the columns from 'General' to 'Currency' and selected the currency to be '� English (United Kingdom)'. I then entered the data, for 'Price bought each' and 'Price sold each' which I collected from Mrs Smith. I now had to use a formula to work out the 'Total money spent on buying products' To do this I needed to times the 'Price bought each' with 'Quantity Bought' on the 'Product Information' sheet (sheet 1). ...read more.

Conclusion

The only differences were that the data was from week 2 and the chart title was changed to 'Profit and Loss of Products'. The chart for week 2 is shown below. Bar chart to show the profit and loss of products in week 2 Formulas and functions The formulas and functions that I have used in this spread sheet are: * Sum - There are a total of 215 (115 subtraction formulae, 92 multiplication formulae, 8 addition formulae) * Average - There is one average formula * IF - There are a total of 71 IF statements and there are 2 main different ones (ADD MORE) and SUBTRACTION (How will the data be manipulated by formulas and functions?) (borders)(comment on formatting cell sizes) (percentage formula)(errors) Formulas and functions The formulas and functions that I have used in this spread sheet are: * Sum - subtraction formulas, multiplication formulas, and addition formulas) * Average * IF (Add more) Graphs I have created 4 charts using the chart wizard, they are: * Profit and Loss graph (Week 1) * Total of Products Sold (Week 1) * Profit and Loss graph (Week 2) * Total of Products Sold (Week 2) The 'Profit and Loss graph' for both weeks is a bar chart showing the profit (positive number) and loss (negative number) of each different product. It is colour coded and has a key; each colour represents a particular product, for e.g. navy blue is for 'Coca Cola (bottle)'. The 'Y' axis shows the amount of profit or loss in pounds. The 'Total of Products Sold' for both weeks is a pie chart showing which and the amount of products that make up the total money received for both weeks. The pie chart is colour coded and contains a key to show which colour represents a product. What if... queries I have used 5 different If queries they are: * =IF('Product Expenses (week1)'!E11>'Product Expenses (week1)'!D11,"PROFIT","LOSS") This is used to calculate whether or not there was a total profit or loss for each product in week 1. ...read more.

The above preview is unformatted text

This student written piece of work is one of many that can be found in our AS and A Level Structures, Objectives & External Influences section.

Found what you're looking for?

  • Start learning 29% faster today
  • 150,000+ documents available
  • Just £6.99 a month

Not the one? Search for your essay title...
  • Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month

See related essaysSee related essays

Related AS and A Level Structures, Objectives & External Influences essays

  1. This is a detailed business report on Sainbsurys.

    Franchising covers a variety of arrangements under which the owner of a business idea grants other individuals or groups to trade using that name or idea. Although we must consider the fact that a franchise can trade as a sole trader, a partnership or a private limited company.

  2. Business report on J Sainsbury's.

    the total gross profit was �1,162 million in 2001 and rose up to �1,257 by 2002 March. J Sainsbury's also in 2001 made profit on ordinary activities before interest of �513 million and again by 2002 it had risen up to �620 million.

  1. Interpretation of Financial Statements.

    would indicate that the assets have been 'turned-over' 4 times during the year. Another way of expressing this ratio would be that for every �1 invested in assets in the last year, �4 worth of sales was achieved. The higher the resulting figure then the more intensively the business is utilizing its assets.

  2. Outback Steakhouse has always had a formula for success. From its tiny beginnings in ...

    In America's fast paced society, people treasure quality time with family and friends as priceless. Offering the public a dining experience ensures success. This depends on all aspects of the restaurant: food, environment, service, and entertainment. 3) Do a SWOT analysis of Outback.

  1. Looking at The Body Shop as an example of a franchiser.

    This enables the franchiser (the existing well known company) expand faster. The franchisee is the sole trader or partnership willing to buy the name and set up his or her own business with minimal risk to themselves/themselves. This is the safest way to set up their first business or add to their empire.

  2. Business unit 2

    We will inform our head of year which is Mr. Khan that there is a tournament going on and to let him know what our event is so he can approve. Human resources- Permission from teachers and the school to host the event is needed.

  1. Applied Business. Investigating a business Preston Manor High School

    average; and 4 is low This table can be used as Secondary research because this is an Ofsted report. This table shows an overall performance of the school. This table shows the school has excellent standards, which will attract more customers (students)

  2. Identify all the considerations that need to be taken into account when planning a ...

    The leader should also innovate his/her team as this will encourage them to work hard * The Plant- The plant is responsible for coming developing and introducing new proposals; without the plant the business will have nothing to launch into the market so it is vital that the plant is up to scratch.

  • Over 160,000 pieces
    of student written work
  • Annotated by
    experienced teachers
  • Ideas and feedback to
    improve your own work