A company wishes to devise a fair means of allocating funds to its four main departments, namely Accounts, Production, Sales and Transport. The total allocation is to be £100,000. A suggested means of allocation is to award each department an initial base allocation of, say, £10,000 and to divide the remainder in proportion to the number of staff in each department. It has been noted that certain staff use up the available budget at a far greater rate than others and, as this is (usually) on company business, the allocation formula must make allowances for such staff. Initially it has been decided to divide the staff into two categories, A and B, and to weight the allocation in favour of those in category A (i.e. if the Weight is 3 then staff in category A are to be allocated an amount 3 times those in Category B).
(a) You are asked to devise a spreadsheet to allow the financial director to explore the possibilities of varying only the Base Allocation and the Weight. Note that the Weight has to be a positive integer between 1 and 7 inclusive.
A typical screen layout may be of the form:
Total Allocation £100,000 Base Allocation £10,000
Weight 3
Department No. of Category A Staff No. Of Category B Staff Allocation
Accounts 7 5 £19,570.55
Production 10 25 £30,245.40
Sales 15 5 £28,404.91
Transport 6 14 £21,779.14
Totals 38 49 £100,000
Produce such a spreadsheet which will calculate the allocations and totals.
(b) The management also require the facility to provide a listing of the Departments ordered by the final allocation figures by clicking on an appropriately labelled button. Provide this facility.
(c) In addition, the financial director wishes to explore the flexibility available if it is required to guarantee, for example, that the Production Department is allocated a minimum of £32,000 without changing the total amount available. Provide a version of the spreadsheet on Sheet 2 which enforces this minimum allocation.
________________
Okay so i've done the spreadsheet and i've sorted out the ordering button, but i'm a bit confused about part C.
The cell where the amount the production department allocation is has a formula in it.
Do i need to just apply data validation to it? Or is there another way i can do it?
(a) You are asked to devise a spreadsheet to allow the financial director to explore the possibilities of varying only the Base Allocation and the Weight. Note that the Weight has to be a positive integer between 1 and 7 inclusive.
A typical screen layout may be of the form:
Total Allocation £100,000 Base Allocation £10,000
Weight 3
Department No. of Category A Staff No. Of Category B Staff Allocation
Accounts 7 5 £19,570.55
Production 10 25 £30,245.40
Sales 15 5 £28,404.91
Transport 6 14 £21,779.14
Totals 38 49 £100,000
Produce such a spreadsheet which will calculate the allocations and totals.
(b) The management also require the facility to provide a listing of the Departments ordered by the final allocation figures by clicking on an appropriately labelled button. Provide this facility.
(c) In addition, the financial director wishes to explore the flexibility available if it is required to guarantee, for example, that the Production Department is allocated a minimum of £32,000 without changing the total amount available. Provide a version of the spreadsheet on Sheet 2 which enforces this minimum allocation.
________________
Okay so i've done the spreadsheet and i've sorted out the ordering button, but i'm a bit confused about part C.
The cell where the amount the production department allocation is has a formula in it.
Do i need to just apply data validation to it? Or is there another way i can do it?