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 names of each product from recording the different products in Mrs Smith’s tuck shop, I would then enter these into my spreadsheet. I would collect the data for products bought and products stored from Mrs Smith. I would then use this data to work out the products sold. I would also collect the data for expired food from Mrs Smith.

Product expense data – I would collect data for price of each product bought and sold from Mrs Smith. I would then find out how many products were bought and sold from the ‘product information data’. I would then use formulas to work out the total of each product bought and sold, and the over all price bought and sold for all products.

Profit and losses data – Using the data from ‘product expenses’, I would use formulas to work out 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).

Sheets, formulas and functions:

I will create 10 sheets they will all contain different data, they are:

Sheet 1 – Week 1 Product Information
Sheet 2 – Week 1 Product Expenses
Sheet 3 – Week 1 Profit & Losses
Sheet 4 – Week 2 Product Information
Sheet 5 – Week 2 Product Expenses
Sheet 6 – Week 2 Profit & Losses
Sheet 7 – (Bar Chart) Profit and Loss
Sheet 8 – (Pie Chart) Total of Products Sold
Sheet 9 – (Bar Chart) Profit and Loss
Sheet 10 – (Pie Chart) Total of Products Sold

Sheets1 and 4 (Product information) are the same. I have formatted data in some cells so that they are, ‘number’ instead of ‘general’, but the product names are still formatted as ‘general’. I have also formatted a cell to ‘date’, for the date to be displayed properly. There is a screen shot bellow showing the cell data that is formatted.

Screen shot showing formatted cells

Sheets 2 and 5 (Product Expenses) are also the same. I have again formatted data in some cells. The formats of data in these two sheets are, ‘General’, and ‘currency’. I have also formatted a cell to ‘date’, for the date to be displayed properly. There is a screen shot bellow showing the cell data which is formatted.

Screen shot showing formatted cells

Sheets 3 and 6 (Profit & Losses) are the same except for that week 2 has an extra formula. Cell formats include, ‘general’, ‘currency’, and ‘date’. The screen shot shows this below.

Sheets 7 and 9 (Bar Chart) Profit and Loss, and, sheets 8 and 10 (Pie Chart) Total of Products Sold, are graphs/charts so they do not have any cell formats. However the graph data does but the data is taken from the sheets above.

Data that will be put in the sheets are:

Sheets 1 and 4 (Product information) – the quantity bought, sold, stored, owned, and lost, and the product name.

Sheets 2 and 5 (Product Expenses) – name of product, price bought and sold each, total price spent on each type of product, total money received from each type of product, the total amount spent and received from all products.

Sheets 3 and 6 (Profit & Losses) – Whether or not a certain product was a profit or loss, the amount of profit or loss, the total money made for the week, whether or not an over all profit or loss was made from the week, the total money made or lost from all weeks, and for the 6th sheet (week 2) the average money made or lost per week.

Putting the tuck shop data and plan into a spreadsheet package (Excel):

First of all I ...