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

Traditional Costing System and Job Order Costing System, Hello, I''m curren...

Hello, I''m currently doing a research on a company and planning an Activity Based Costing system since the company is using Traditional Costing system to allocate the overhead to

Budget, budget preparation

budget preparation

What profit is earned by each firm, Three oligopolists, A, B and C, produce...

Three oligopolists, A, B and C, produce an identical product, Q. Q is produced under conditions of constant costs, that is, AC = MC = $100. The market demand schedule for Q is:

the meaning of the fixed production, Describe the meaning of the fixed pro...

Describe the meaning of the fixed production overhead variances calculated under the standard absorption costing system and talk about their usefulness to the management of X Ltd.

Prepare the capital accounts, Shirley and Ken are in partnership, trading i...

Shirley and Ken are in partnership, trading in the construction industry. The year end for the partnership business is 30 June. You are the Assistant Accountant and have been as

Compute depreciation for each year, A machine costing $210,400 with a four-...

A machine costing $210,400 with a four-year life and an estimated $20,000 salvage value is installed in Luther Company's factory on January 1. The factory manager estimates the mac

Approach in cost accounting, Approach in Cost Accounting Cost accounti...

Approach in Cost Accounting Cost accounting is based on the framework or concept of cost centers that is all the costs incurred throughout the production process contain to be

What is the net operating income, A manufacturing company that produces a s...

A manufacturing company that produces a single product has provided the following data concerning its most recent month of operations: selling price $140 units in begining in

Interest rates and adjust rates, The Federal Reserve adjusts short term int...

The Federal Reserve adjusts short term interest rates based upon their perceptions of the needs in the economy.  Please describe the ways the Federal Reserve can influence interest

Contract costing terminology, Contract Costing Terminology Principles ...

Contract Costing Terminology Principles of profit income recognition in contracts The Notional Profit This is a component of two items as: a) Profit taken   = Noti

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