Calculate the allocations and totals, Cost Accounting

Assignment Help:

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


Related Discussions:- Calculate the allocations and totals

Evaluate the relevant cost, Question PART A A company manufactur...

Question PART A A company manufactures a single product and the data concerning the product is as follows: - Sales price of $10 - Marginal cost of $6. - Fixed

Calculate average cost and marginal cost, Farmer Dorr figures that her fixe...

Farmer Dorr figures that her fixed costs are $2,000, and the relevant portion of her total cost curve is:                                           Thousands of

Calculate the maximum capital cost allowance, At the beginning of 2010, Mir...

At the beginning of 2010, Mirror Corporation, had undepreciated capital cost (UCC) of $1,575,000 in asset Class 38 with a CCA rate of 30%. On April 15, 2010, Mirror sold an asset t

What will be their allocated overhead?, Tyler's Consulting Company has purc...

Tyler's Consulting Company has purchased a new $15,000 copier. This overhead cost will be shared by the purchasing, accounting, and information technology departments since those a

Overheadd anarlysis, Following figures are taken from annual budget of ABC ...

Following figures are taken from annual budget of ABC manufacturers for the year 2013: Fixed factory overhead Rs. 4,000,000 Factory overhead absorption rate Rs. 70 per direct labor

Bad debts, These are losses on account of uncollectable debts. While the am...

These are losses on account of uncollectable debts. While the amount due from debtors is irrecoverable, it is termed as bad debts. Bad debts, being loss are closed through transfer

Variance, It is important to have performance measures to evaluate managers...

It is important to have performance measures to evaluate managers as they control resources and invest in assets for the company. Describe how you could use different variances (ac

Material usage variance, Describe briefly the possible causes of: (i)   ...

Describe briefly the possible causes of: (i)   the material usage variance, (ii)  the labour rate variance, (iii)  the sales volume profit variance.

Cost of labor, The number of workdays varies from month to month due to the...

The number of workdays varies from month to month due to the number of weekdays, holidays, days of vacation, and sick leave taken in the month. The number of units produced in a mo

Manufacturing Costs, Is there a way to figure out labor cost and factory i...

Is there a way to figure out labor cost and factory inventory when no direct information regarding them is available.

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd