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

Apportionment of overheads, Apportionment of Overheads Apportionment...

Apportionment of Overheads Apportionment of overheads occurs whereas the net value of an overhead item is shared among more or two cost centers that employ the overheads. Th

Comparison between absorption and marginal costing, Comparison between Abso...

Comparison between Absorption and Marginal Costing Marginal Costing like a cost accounting system is considerably different from absorption costing. It is an optionally metho

Estimated net income, What will be the cost of Well Water after considering...

What will be the cost of Well Water after considering the financing surrounding the purchase (savings on the loan), Well Water's (net) working capital situation, and the additional

Prepare the journal entries to record depreciation, Moore Corporation follo...

Moore Corporation follows a policy of a 10% depreciation charge per year on all machinery and a 5% depreciation charge per year on buildings (the corporation uses the nearest full

Compute the cost per unit, Ass ume that during April, the job cost sheet f...

Ass ume that during April, the job cost sheet for Job 206 showed the following:                                                          Dept. A                   Dept. B M

Overhead anaylysis, the annual overhead costs for abc ltd which has 3 produ...

the annual overhead costs for abc ltd which has 3 production centeres and 2 service centers are as follows. indirect wages & supervision X 2million Y 2million 3 production departme

Student, i asking for start up

i asking for start up

Mr, Extracts from P Co''s records for last month are as follows: ...

Extracts from P Co''s records for last month are as follows: BUDGET ACTUAL PRODUCTION 7,000 UNITS 7,200 UNITS Direct Material $42,000 $42,912 Calculate

Determine the expected rate of growth of dividends, General Motors has to r...

General Motors has to raise new capital in one of the following three ways. Using the income tax rate of 32%, find the after-tax cost of new capital in each case. (A) Sell commo

Budget, budget preparation

budget preparation

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