Calculate the allocations and totals, Cost Accounting

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).

Question 1, Part 1 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


No. of Category A Staff

No. Of Category B Staff






















Produce such a spreadsheet which will calculate the allocations and totals.          

Question 1, Part 2 The management also require the facility to provide a listing of the Departments and Allocation ordered by the final allocation figures by clicking on an appropriately labelled button.  Provide this facility.

Question 1, Part 3  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 another version of the spreadsheet on a separate sheet which enforces this minimum allocation.

Question 1 Requirement

Part 1 - Spreadsheet implementing weighting

Part 2 - Listing button generating ordered Department & Allocation list

Part 3 - Spreadsheet with minimum allocation of £32,000 enforced

Posted Date: 3/12/2013 3:33:25 AM | Location : United States

Related Discussions:- Calculate the allocations and totals, Assignment Help, Ask Question on Calculate the allocations and totals, Get Answer, Expert's Help, Calculate the allocations and totals Discussions

Write discussion on Calculate the allocations and totals
Your posts are moderated
Related Questions
I'm having a hard time with this, can you please help? I know the dates are imparative also in finding the solution. Stevens purchased an auto on Jan 1, 2001. On December 31, 2003

Denair Fine Wines, Inc., is planning to bring out a higher-quality wine product than any currently available in Stanislaus county. They've decided on selling the blended, fortified

On January 1, 2013, VKI Corporation awarded 12 million of its $1 par common shares to key personnel, subject to forfeiture if employment is terminated within three years. On the gr

One item a computer store sells is supplied by a vendor who handles only that item. Demand for that item recently changed, and the store manager must determine when to replenish it

advantages and disadvantages of just in time

This question tested the accounting of monetary instruments, especially an asset held at reasonable value through loss or profit. The preparation of the journal for subsequent and

Co-ownership incentive scheme or Profit Sharing Schemes The organization permits for ownership whereby the employees are permitted to own a percentage of the shares in the fir

The government of a small South Pacific island is considering whether to allow development of a small but valuable deposit of phosphate rock. Not having the resources to develop an

Principles of Marginal Costing The principles of marginal costing are as given: 1. Period fixed costs are similar, for any volume of sales and production provided suc

Harriet Ltd is a trading company set up a number of years ago with 5,000 £1ordinary shares issued at par. In order to expand the production facilities it needs to raise a further £