Assignment:
Group Spreadsheet Assignment
Anonymous Charity
A large national charity that is best described as a learned society and professional body, has a need for an advanced spreadsheet to assist the organisation in budgeting. The charity is undertaking a costing review of the prices of its qualifications and requires a spreadsheet that is simple to use.
The requirements are:
1. A single file spreadsheet, with a "Calculator Sheet", bringing together all of the various costs,
2. The ability to quickly and easily choose which method of allocation the cost should be shared on, the two options are:
o By total student numbers (all students pay the same), and
o By qualification (the qualification is charged the same percentage, but this standardised cost is shared amongst the students on that qualification).
Data
The charity offers six qualifications, with different numbers of enrolled students:
|
Qualification
|
A
|
B
|
C
|
D
|
E
|
F
|
|
Enrolled Students
|
40
|
300
|
25
|
125
|
250
|
110
|
The charity has the following annual costs:
|
Staff Costs
|
£250,000 per year
|
|
Premises Costs
|
£45,000 per year
|
|
Development Costs
|
£25,000 per year
|
|
Promotion Costs
|
£25,000 per year
|
|
Regulatory Costs
|
£40,000 per year
|
|
Assessment Costs
|
As per below
|
|
Meeting Costs
|
As per below
|
Each qualification has a different number of assessments and meetings with varying number of delegates, thus unlike the above, each qualification (cost centre) has different costs (for the avoidance of doubt, both allocation options need to be available in the spreadsheet, even though each qualification carries a different cost)
|
Qualification
|
A
|
B
|
C
|
D
|
E
|
F
|
|
Total Assessments
|
12
|
9
|
15
|
28
|
8
|
12
|
|
Cost of assessment (each)
|
£200
|
£175
|
£240
|
£80
|
£130
|
£0
|
|
Yearly Meetings
|
4
|
4
|
3
|
3
|
4
|
2
|
|
External delegates
|
10
|
10
|
10
|
12
|
12
|
8
|
|
Staff Members
|
2
|
|
|
|
|
|