Business Spreadsheet

emily2309

Board Regular
Joined
Nov 17, 2009
Messages
51
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?
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
(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?

Hello emily2309,

It sounds like the request is to have a second version of the model in which the budget for the Production department is the greater of £32,000 or whatever your first model allocated...and....
the rest of the department budgets would have formulas that automatically adjust to keep the total budget at £100,000.
 

emily2309

Board Regular
Joined
Nov 17, 2009
Messages
51
Yea i know thats what i have to do i just don't know how i'm supposed to do it?
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
If you post an example showing the formula you have developed without the £32,000 minimum requirement, it will make it easier to help you revise it.
 

emily2309

Board Regular
Joined
Nov 17, 2009
Messages
51
=$F$2+(C9*$B$20*$C$3)+(D9*$B$20)

which basically transfers to:

= base allocation + (number of category A staff * Amount of money per Staff * weight) + (number of category B staff * amount of money per staff)

The spreadsheet basically calculates how much money four departments get out of £100,000 depending on how many of each staff they have. The category A staff get a certain times more money than category B staff, and this is defined by the weight. For example, if the weight is 3 the category A staff get 3 times more money than the category B staff.
The amount of money per staff was worked out by taking the base allocation times four (number of departments) and taking that away from the total allocation (£100,000). So for example if the base allocation is four you would be left with £60,000. Then by adding the number of category B staff to the number of category A staff * weight to give the total number of staff. So if there was 2 category B staff and 3 category A staff and the weight was 3 the total number of staff would be 2 + (3*3), therefore 11. You would then divide £60,000 by 11 giving the amount of money per staff.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Emily,

Your sending me a copy of your current workbook helped - thanks.

Below is one approach to model the adjustments needed if one department is allocated a minimum budget.
Excel Workbook
ABCDEFGHIJK
1
2Total Allocation100,000.00Base Allocation10,000
3Weight3
4
5DepartmentNo. of Category A StaffNo. of Category B StaffNo. of Shares of Left AmtUnadjusted AllocationMinimumOverrideNo. of Shares of Left AmtAdjusted Allocation
6Accounts752619,570.55002619,148.15
7Transport6143221,779.14003221,259.26
8Sales1555028,404.91005027,592.59
9Production10255530,245.4032,00032,00032,000.00
10Totals3849163100,000.0032,00032,000108100,000.00
11
12Unadjusted:Adjusted:
1360,000.00Amount left to share38,000.00Amount left to share
14368.10Amount per share351.85Amount per share
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top