A large national charity that is best described as a learned society and professional body

Be Prepared For The Toughest Questions

Practice Problems

Question

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