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

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.          

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
Gustav Ltd commenced operations on 1 July 2011 and presents its first statement of comprehensive income for the year ending 30 June 2012 and first statement of financial position a

Fosson Furniture uses a process cost system to account for its chair factory. Beginning inventory consisted of 5,000 units (100% complete as to material, 55% complete as to labor)

Marginal Costing and Marginal Cost Marginal Costing is an optionally method of costing to absorption costing , In marginal costing, merely variable costs are charged like a

SALES REVENUE VARIANCE (SRV) The word 'Sales Variance' is indicated by the expression 'operating profit variance due to sales' by ICMA.  It is described as 'the difference betw

Smart Ltd ha sa unit selling price of $500 variable costs per unit of $325 and fixed costs of $140 000. Calculate the break even point in units using (a) a mathematical equations a

A process in the industry where a wholesaler needs an amount that is the difference among the manufacturer's price to the wholesaler and the contract price to the resale customer.

WORKED EXAMPLES OF EXPECTED CASH COLLECTIONS PATTERNS

Problem: A satellite is launched into Earth orbit by a Delta II launch vehicle (LV).  The Delta LV's engines do not perform as expected, and at upper-stage burnout the satellite

question and answer: XYZ trading purchased 6,850 killos of material at a total cost of 21,920.00. The material price variance was 1,370.00 favorable. The standard price per killo w

Cowboy Constructions employs a full-time driver and incurs costs for a vehicle to deliver paperwork between each of their building sites. Select Couriers has offered to carry out t