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

Standard cost, Standard Cost A predetermined cost is representing the i...

Standard Cost A predetermined cost is representing the ideal or norm achievable through an organization. Standard costs form the basis of a standard cost system used extensivel

Effects of increasing fixed cost, Jones Company operates within a monopolis...

Jones Company operates within a monopolistically competitive industry. The estimated demand for its products is given by the following inverse demand function P = 1760 - 12Q

Cost components, Cost Components Companies which manufacture a product ...

Cost Components Companies which manufacture a product face an elaborated set of accounting issues. Additionally the usual accounting matters related with selling and administra

Making variance analysis more meaningful, Making Variance Analysis More Mea...

Making Variance Analysis More Meaningful To compose variance analysis as useful aid to management is the main objective of variance calculations.  However this can only be don

Example of process cost report, Example of Process Cost Report Let sup...

Example of Process Cost Report Let suppose that the beginning work in progress in a Company in the month of November was 1,000 units that were 100 percent complete in terms of

Distinguish between the products of different producers, The text states th...

The text states that four conditions are necessary for the existence of a perfectly competitive market. Discuss in your own words each one. a)   Numerous participants: Roughly

What are the advantages and disadvantages of free float, Q. What are the ad...

Q. What are the advantages and disadvantages of free float? Advantages: It is one of the most suitable ER regimes for transitional countries that experience external shocks l

Compute the annual depreciation charges, On January 1, 2012, a machine was ...

On January 1, 2012, a machine was purchased for $197,100. The machine has an estimated salvage value of $13,140 and an estimated useful life of 5 years. The machine can operate for

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