The Student Room Group

GCSE spreadsheets: tuck shop

For GCSE short course IT, I'm designing a profit-and-loss spreadsheet for a tuck shop. Any tips to get the top grades? I've included IF forumlas but I haven't done anything else that is too impressive.

Also, I don't know how to lay it out exactly... should I have a sheet each for income, expenditure and profits? Should I do it month-by-month, or week-by-week? If you like, I could attach what I've done so far...

I'm really getting confused with what to do, so I would appreciate any kind of help!
Reply 1
Attach your spreadsheet thus far and i'll have a quick look over it... What exam board are you doing? To be totally honest, I did Edexcel full course a year early and its more based around your write up than your actual spreadsheet. But anyways, attach it and lemme know your exam board and i'll get back to you
Reply 2
could use data validations, so when the tuck shop is eaning money, the cell says "you are earning profit, well done" in green, when the books are balance, it can say "the books are balance, keep on going" in yellow, and when you are losing money, it could say "you are currently losing money, do something!!!" in red

very simple to do, and it is perfect form ICT
Reply 3
Thanks for the replies... carnage, I've attached what I've done so far (though not very much). The income sheet is OK, the expenditure sheet is rather messed up... I don't know whether I should do a month-by-month thing or not, and so am yet to decide whether to have a balance sheet, or just stick profits into one of the sheets.

EDIT: OK, I can't seem to upload it on here... not valid file type or something... I guess I could email it to you?
Reply 4
Nah dont worry, here is my suggestions for your spreadsheet:

Two separate sheets to record Sales and expenditures, and one that works effectively as a balance sheet, itemisng the profit and/or loss. For the first two i would just have a date field but allow the user to filter the results. In terms of functions, I would reccomend the following:

Conditional Formatting for the Profit/Loss Sheet
IF functions, these could be used in the sheet where on entry of the product into one cell (via drop down) it would automatically bring up the products price, thus doing the calculations for you.
Filtering certain fields
Good uses of Datavalidation
Graphs

I'll be totally honest, with GCSE, so much more emphasis on the Writeup, thats where the marks come from. Hope this helps, any more questions give me a shout.
:ditto:

Its not how well you do the project, but the writeup that will get you the marks. Just spend the time on the writeup, and cover everything.
Reply 6
carnage012
Nah dont worry, here is my suggestions for your spreadsheet:

Two separate sheets to record Sales and expenditures, and one that works effectively as a balance sheet, itemisng the profit and/or loss. For the first two i would just have a date field but allow the user to filter the results. In terms of functions, I would reccomend the following:

Conditional Formatting for the Profit/Loss Sheet
IF functions, these could be used in the sheet where on entry of the product into one cell (via drop down) it would automatically bring up the products price, thus doing the calculations for you.
Filtering certain fields
Good uses of Datavalidation

Graphs

I'll be totally honest, with GCSE, so much more emphasis on the Writeup, thats where the marks come from. Hope this helps, any more questions give me a shout.


Thank you for your help... but I'm just a measly GCSE student and it all sounds a bit too complicated... would you be able to perhaps explain the bits in bold again? :redface:

Also, would it be better to have a month-by-month balance on the profit sheet, and perhaps draw a line graph showing it? In that case, in the expenditure sheet I've got a table showing which items need to be/have been reordered, thus showing the expenditure of purchasing the goods. I don't know how I could include that as a month-by-month expenditure. It's very hard to explain without having the spreadsheet so I'm sorry if I don't make much sense.

But thanks for your help! And it's good to know that at GCSE the writeup counts for most, because that's what I'm better at lol.
Reply 7
Sorry, explaination not entirely clear...

I dont see month to month as a necessary thing, seems like alot more work and you wouldnt get anymore marks. But I like your reorder level idea.

Right... my suggestions...

Date field filtering, what this means is that you could have the date entered for each transaction. Then allow the user to filter the results for specific dates months etc. For more info on filtering, look it up in MS Excel Help.

Product Drop down - what you could have is a drop down list of products, then when you select one in a list it automatically enters the price onto the sheet. If that seems to complex for you, leave it, its an optional extra.

Filtering certain fields - the date thing is your example there, you could use that on other fields if you like. Your call.
Data Validation - an essential one this, it involves you making sure that the data entered is reasonable eg, no text in a numeric field, dates in certain ranges. For more info on what validation you can use, goto Data - Validation in Excel. Apply this where possible and evidence in writeup.

Hope thats cleared it all up :smile:
Reply 8
that one thing about ICT i hate, you spend ages desgin your system so it works, but its nothing unless you have paper work to back it up
Reply 9
carnage012
Sorry, explaination not entirely clear...

I dont see month to month as a necessary thing, seems like alot more work and you wouldnt get anymore marks. But I like your reorder level idea.

Right... my suggestions...

Date field filtering, what this means is that you could have the date entered for each transaction. Then allow the user to filter the results for specific dates months etc. For more info on filtering, look it up in MS Excel Help.

Product Drop down - what you could have is a drop down list of products, then when you select one in a list it automatically enters the price onto the sheet. If that seems to complex for you, leave it, its an optional extra.

Filtering certain fields - the date thing is your example there, you could use that on other fields if you like. Your call.
Data Validation - an essential one this, it involves you making sure that the data entered is reasonable eg, no text in a numeric field, dates in certain ranges. For more info on what validation you can use, goto Data - Validation in Excel. Apply this where possible and evidence in writeup.

Hope thats cleared it all up :smile:


Oh OK, that cleared that up... thanks! :biggrin: