The Student Room Group

Help with microsoft excel assignment

I have been set an assignment on excel which is:
"Build a spreadsheet which calculates the revenue generated from the sale of widgets, and the profit achieved from them after costs of sales. The inputs should be numbers of units sold, prices, and a cost per unit. The spreadsheet should run for two years on a monthly basis. Volumes will be based on an annual sales profile and there should be a growth factor from one year to the next. There should be 5 sales profiles (of your choice) which can be selected and switched between via a dropdown selection. Be as elaborate and imaginative as you want on presentation. Keep formulae consistent and clean, with no hard-coded numbers included. Inputs should be clearly separated from outputs"

There's a few things which I don't understand. I don't understand what is meant by 'there should be a growth factor from one year to the next'. What should have a growth factor? The number of units sold?

I also don't understand what is meant by "There should be 5 sales profiles (of your choice) which can be selected and switched between via a dropdown selection". What would be an example of a sales profile?

This is what I have done so far:
https://ibb.co/bWF3VFN
I have just made up numbers of units sold, cost and prices which I though seemed reasonable, and then made some simple formulas to calculate the sales and profits.
(edited 3 months ago)
Original post by RossM7
I have been set an assignment on excel which is:
"Build a spreadsheet which calculates the revenue generated from the sale of widgets, and the profit achieved from them after costs of sales. The inputs should be numbers of units sold, prices, and a cost per unit. The spreadsheet should run for two years on a monthly basis. Volumes will be based on an annual sales profile and there should be a growth factor from one year to the next. There should be 5 sales profiles (of your choice) which can be selected and switched between via a dropdown selection. Be as elaborate and imaginative as you want on presentation. Keep formulae consistent and clean, with no hard-coded numbers included. Inputs should be clearly separated from outputs"

There's a few things which I don't understand. I don't understand what is meant by 'there should be a growth factor from one year to the next'. What should have a growth factor? The number of units sold?

I also don't understand what is meant by "There should be 5 sales profiles (of your choice) which can be selected and switched between via a dropdown selection". What would be an example of a sales profile?

This is what I have done so far:
https://ibb.co/bWF3VFN
I have just made up numbers of units sold, cost and prices which I though seemed reasonable, and then made some simple formulas to calculate the sales and profits.

As someone who studied accounting, finance, management as well as having official qualifications in Excel, I could easily have a field day with this. As per TSR's policies and my personal ethics, I can't and won't give you direct answers to help you with your assignment.

What subject is this for? It doesn't seem like ICT. If it is, it's badly written. Is this for university? If it's college level, then it's badly composed.

I don't understand what is meant by 'there should be a growth factor from one year to the next'. What should have a growth factor? The number of units sold?
Growth factor is a multiple you multiply the current period's sales by to show a change in sales for forecasting purposes.
The following should give you a pretty good idea:
https://www.wallstreetprep.com/knowledge/income-statement-forecasting/

I also don't understand what is meant by "There should be 5 sales profiles (of your choice) which can be selected and switched between via a dropdown selection". What would be an example of a sales profile?
I haven't come across "sales profile" either. It's either a buzzword that universities have recently come up with that nobody understands other than the people writing the papers or it's something your teacher made up.
I think you would be able to find these answers if you look through your notes for your lesson. If not, look through your textbook assigned for the module/course. Failing both, interrogate your teacher/tutor about it and then question who made up a term nobody understands.

This is what I have done so far:https://ibb.co/bWF3VFNI have just made up numbers of units sold, cost and prices which I though seemed reasonable, and then made some simple formulas to calculate the sales and profits.
I'd be stingy with marks if there were any for presentation.
I wouldn't quite agree with what you have done, but then again it depends more on how your tutor/teacher/lecturer is going to mark it than what I (or a professional) am opining.
Reply 2
Original post by MindMax2000
As someone who studied accounting, finance, management as well as having official qualifications in Excel, I could easily have a field day with this. As per TSR's policies and my personal ethics, I can't and won't give you direct answers to help you with your assignment.

What subject is this for? It doesn't seem like ICT. If it is, it's badly written. Is this for university? If it's college level, then it's badly composed.

I don't understand what is meant by 'there should be a growth factor from one year to the next'. What should have a growth factor? The number of units sold?
Growth factor is a multiple you multiply the current period's sales by to show a change in sales for forecasting purposes.
The following should give you a pretty good idea:
https://www.wallstreetprep.com/knowledge/income-statement-forecasting/

I also don't understand what is meant by "There should be 5 sales profiles (of your choice) which can be selected and switched between via a dropdown selection". What would be an example of a sales profile?
I haven't come across "sales profile" either. It's either a buzzword that universities have recently come up with that nobody understands other than the people writing the papers or it's something your teacher made up.
I think you would be able to find these answers if you look through your notes for your lesson. If not, look through your textbook assigned for the module/course. Failing both, interrogate your teacher/tutor about it and then question who made up a term nobody understands.

This is what I have done so far:https://ibb.co/bWF3VFNI have just made up numbers of units sold, cost and prices which I though seemed reasonable, and then made some simple formulas to calculate the sales and profits.
I'd be stingy with marks if there were any for presentation.
I wouldn't quite agree with what you have done, but then again it depends more on how your tutor/teacher/lecturer is going to mark it than what I (or a professional) am opining.

Thanks for your answer. I think I understand what he means by growth rate and have at least some ideas of what to do for the 'sales profiles' part. I'm curious to know what you would suggest I could improve about the presentation. Is it because some parts of the table are solid white while others are blue and white because I'm not sure why that happened. Also for "I wouldn't quite agree with what you have done", which parts don't you agree with?
Original post by RossM7
Thanks for your answer. I think I understand what he means by growth rate and have at least some ideas of what to do for the 'sales profiles' part. I'm curious to know what you would suggest I could improve about the presentation. Is it because some parts of the table are solid white while others are blue and white because I'm not sure why that happened. Also for "I wouldn't quite agree with what you have done", which parts don't you agree with?

I think I understand what he means by growth rate and have at least some ideas of what to do for the 'sales profiles' part
I wish you well then. I on the other hand am drawing a blank.

I'm curious to know what you would suggest I could improve about the presentation. Is it because some parts of the table are solid white while others are blue and white because I'm not sure why that happened.
From the look of things, you seem to be trying to make it format like a Pivot Table, when you are just using Tables.
The task set doesn't seem to be asking you to create a pivot table out of this (even though you can). I would use standard accounting formatting like in the Wall Street Prep link to make things clearer. For one, it's customary to have time/periods across the horizontal, not the vertical.
Of course, if your teacher/tutor/etc. wants your work to be in a specific format and go against industry conventions, then do what your teacher/tutor is asking of you; we're not marking your work, your tutor is. I am not expecting all teachers to have an accounting background, especially if it's for something like a business or IT assignment.
The blue and white alternating format is standard in an Excel Table. If you click on the table, you should see a Design tab in your Ribbon, where you can format the table accordingly. See the following: https://support.microsoft.com/en-us/office/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664
https://support.microsoft.com/en-us/office/format-an-excel-table-6789619f-c889-495c-99c2-2f971c0e2370

Also for "I wouldn't quite agree with what you have done", which parts don't you agree with?
The growth rates and your sales figures (without giving too much away). Having said that, it might well be that your tutor doesn't know about forecasting in accounting or finance professions and might be using a different criteria of his or her own design to mark your work. I don't know, but whatever your tutor thinks is correct takes precedence over what I think; again, it's your tutor marking your work, not me.

Quick Reply

Latest