QUESTION

Assessment task 2 — Assignment
Due date:
Thursday of  Week 8 [4pm  [AEST] 26 April 2012]
ASSESSMENT
Weighting:

Objectives
30%

This assessment item relates to the course learning outcomes 2, 3, 4 and 7.
Details
The assignment is designed to help you to gain and/or improve your generic, analytical and
information technology skills. There are two parts to this assignment. Part A is designed to enhance
your skills in the planning, designing and using of spreadsheets and relates to budgets. Part B is
designed to enhance your written communication and analytical skills and relates to various costing
issues to consider in making decisions.
Students must use spreadsheets such as excel in Microsoft Office to answer Part A. You will lose all
10 marks allocated to the use of spreadsheets if you choose not to use spreadsheets.
As this is an advanced course, it is assumed that students enrolled in this course are familiar with the
use of spreadsheets. If you are not familiar with using spreadsheets or some functions of a
spreadsheet, we advise you to use the help facilities which are provided within most spreadsheet
software. You will also find help on how to use spreadsheets on the internet and/or in textbooks in
most libraries. In addition to these sources, students can also contact the Communications Learning
Centre and the lecturer or tutor at the local campus. Distance Education students may contact the
Course Coordinator for assistance.
Students must design the spreadsheet so that you have an input spreadsheet with input cells for
entering the data, which are linked to output spreadsheets (i.e. the 9 budgets). We recommend you
design your output sheets as shown in the examples on page 8 of this document. In essence, ensure
your spreadsheet is designed in such a way that when you change a number in an input cell, the
calculations are done and the relevant output sheet(s) is (are) updated automatically. Design the
spreadsheet so that the only numeric values that need to be altered when scenarios change are those
in the input cells. Your spreadsheets must contain formulae for doing calculations. However, you are
strongly encouraged to show your calculations on your spreadsheets. If your figures are incorrect and
you do not provide your calculations, you may not receive any marks. The markers do not have the
time to follow your links and/or to guess how you derived at your figures. One way of showing your
calculations in your output sheet is in brackets for example as shown in bold below:
May
Sales (7 000 x $100)
$700 000

The Course Coordinator reserves the right to require a student to supply a soft copy of their
spreadsheets and to explain to your lecturer how the assignment was prepared using that spreadsheet.

Submission requirements
There is no electronic submission of the assignment for this course.
1

2
Submit hard copies of the following three documents:
• your assignment
• the marking criteria sheet (provided on page 15 of this course profile)
• a signed assessment cover sheet that is available on the university website.
Ensure your name and student ID number appear at the top of each page you submit and on
the marking criteria sheet.

ON-CAMPUS students
Submit hard copies of the three documents stated above in the provided assignment boxes located on
your particular campus. If no boxes are provided, then submit your assignment to the administration
office.
OFF-CAMPUS students
You have to post the hard copies of the three documents stated above by the deadline 4 pm [AEST]
Thursday 26 April 2012 to the Student Contact Centre, Building 5, CQUniversity, Rockhampton,
QLD 4702. We highly recommend that you include evidence with your assignment that you have met
this deadline. For example, include a copy of the post office mail document that shows the date it was
posted.
Assessment criteria
The marking criteria sheet at the end of the assignment indicates the allocation of marks for the
assignment. Please attach a copy of this marking criteria sheet including your name and student
ID to your assignment.
There is a 5% penalty per day for late submissions.
2

Part A – Budgeting       75 marks
Sunny Shades Ltd manufactures two types of gazebos for outdoor use for the Australian market: Steel
(standard) and Aluminium (luxury) gazebos. Sunny Shades Ltd buys two types of direct materials in
metres to manufacture the frames and the sails for the gazebos. Steel is used to manufacture the steel
frames and aluminium for manufacturing the aluminium frames. The fabric used to manufacture the
sails is polyester. The sail finishing for the steel gazebo is PA coated and the aluminium gazebo has
UV protection. The aluminium gazebo is also fitted with four LED lights. The following data are
available for the 2012 budget:
The direct-cost inputs for each gazebo are:
Direct-cost inputs Steel gazebo Aluminium gazebo
For frames 12 metres of steel 14 metres of aluminium
Fabric (polyester) for sails 4 metres 5 metres
LED lights 0 4 lights
Direct manufacturing labour 6 hours 8 hours

The Marketing manager of Sunny Shades Ltd projects the following monthly sales and estimated
selling prices of the two gazebos:
Months Steel Gazebos Aluminium Gazebos
Projected sales Selling price
3

(S/P)
Projected sales Selling price
(S/P)
January to April 2 500 per month $250 each 3 000 per month $370 each
May to August 2 000 per month $230 each 2 400 per month $350 each
September to
December
2 400 per month $260 each 2 600 per month $380 each

The actual inventory of finished goods as at 31 December 2011 is as follows:
Inventory at 31/12/2011 Steel Gazebos Aluminium Gazebos
Units 2 300 2 700
Total cost $276,000 $621 000

The actual inventory of raw material as at 31 December 2011 is as follows:
Inventory at 31/12/2011 Steel (for frame) Aluminium
(for frame)
Fabric LED lights
Units 24 000 metres 33 600 metres 20 000 metres 9 600
Total cost $38 400 $67 200 $160 000 $38 400

The firm has a policy of carrying an end-of-month finished goods inventory of 100% of the following
month’s sales plus 20% of the second following month’s sales. It is also company policy to keep raw
material inventory at the end of a period at levels sufficient for manufacturing the number of gazebos
required for production of the following month. For example, raw material inventory at the end of
January will be the raw material required to manufacture the required number of gazebos for
February.
Unit costs of direct materials purchased and unit costs of finished goods sold remain unchanged
throughout each budget year but can change from year to year. The actual and estimated unit costs for
the two types of frames, for fabric and for LED lights are as follows:
Actual for 2011 Estimated for 2012
Steel frame $1.60 per metre $1.80 per metre
Aluminium frame $2 per metre $2.40 per metre
Fabric $8 per metre $9 per metre
LED lights $4 each $3 each

Sunny Shades Ltd has a labour contract that calls for a wage increase of 4% per hour on 1 May every
year. The current wage rate is $16.50 per hour. In addition to wages, direct manufacturing labourrelated
costs
include
superannuation

contributions of $0.80 per hour and worker’s compensation
insurance of $0.20 per hour. These costs also increase by 4% per hour on 1 May every year. The cost
of employee benefits paid by Sunny Shades Ltd for its employees is treated as a direct manufacturing
labour cost.
Manufacturing overhead (both variable and fixed) is allocated to each gazebo on the basis of
budgeted direct manufacturing labour hours (DMLH) per gazebo. The budgeted variable
manufacturing overhead rate for the 12 months ending 30 April 2012 is $5 per DMLH hour and is
estimated to be $5.50 per DMLH for the next 12 months. The fixed manufacturing overhead for the
12 months ending 30 April 2012 is $15 000 per month and is budgeted to be $16 000 per month for
the next 12 months. Both variable and fixed manufacturing overhead costs are allocated to each unit
of finished goods.
Assume the following in your answer:
• Direct materials inventory and finished goods inventory are costed using the FIFO method.
• There is no work-in-progress inventory at any given point in time.
Use formulae to do your calculations and use 3 (three) decimal places in your calculations. To
receive maximum marks for your figures, show all your calculations in your output sheets.
Refer to the marking criteria sheet for the allocation of marks.
Required:
(i) Prepare and print the following six (6) budgets by month for April, May and June of 2012 and
the totals for these three months:
1. Revenue budget
2. Production budget in units
3. Direct material usage budget (both units to be used and the cost budget in dollars)
4

4. Direct material purchases budget in units and in dollars
5. Direct manufacturing labour hours and cost budget
6. Manufacturing overhead budget (you are not required to calculate the totals for the three
months, only the individual months)
(ii) Prepare the following three (3) budgets for the three months ending 30 June 2012 only.
(Show these budgets in total for the quarter only, not per month. Round your numbers for
these budgets up to zero decimal places):
7. Opening and ending inventories budget for direct materials for the three months ending
30 June 2012.
8. Opening and ending inventories budget for finished goods for the three months ending
30 June 2012.
9. Cost of good sold budget for the three months ending 30 June 2012.
(iii) Print and submit the following for your answer to Part A (worth 10 marks – see marking
criteria sheet):

• The input sheet
• The formulae spreadsheet(s) of all nine budgets in (i) above (i.e. showing the rows and
columns together with any formula that has been used in each cell of the spreadsheet).
Please note: the 12 marks allocated for the use of spreadsheets are for the design and layout
of your spreadsheet. The formulae sheets are not considered to be calculations and are not
marked or looked at to ascertain how you derived at your figures. Your formulae sheets will
be marked merely to ascertain the linkages in your spreadsheets and the logical flow of the
design of your spreadsheets. Please show your calculations in your output spreadsheets of the
nine budgets required in (i) and (ii) above to enable the markers to award you partial marks if
your figures are incorrect.
5

Part B – Decision making and relevant information             15 marks
Part B is a continuation from Part A.
Sunny Shades Ltd’s plant has a capacity to manufacture 7 200 gazebos per month (3 200 Steel
gazebos and 4 000 Aluminium gazebos). The constraint is the direct manufacturing labour hours. The
company received an order from an overseas customer to manufacture 10 000 Aluminium gazebos, to
be delivered 1 July 2011. The gazebos can be manufactured any month from January to June 2011.
To meet the requirements of the overseas customer, the Aluminium gazebos will have to be
customised to make the fabric for the sail waterproof. Sunny Shades Ltd does not have the equipment
to make the fabric waterproof. They could buy equipment for $30 000 to make the fabric waterproof
and do it themselves or they can outsource the waterproofing to be done by another company, which
will cost $25 per gazebo. The customer is prepared to pay $350 per gazebo.
Required:
Advise the CEO of Sunny Shades Ltd whether to accept or reject the order. Discuss the following
issues that Sunny Shades Ltd will have to consider in deciding whether to accept or reject the order.
1. Relevant costs.         (5 marks)
2. Other financial issues and costs.       (5 marks)
3. Other non-financial issues.        (5 marks)

Word limit for Part B: between 1 000 – 1 500 words.
6

Student name

Student ID number
Marking Criteria Sheet
7

Marks available Marks awarded
Part A  – Budgeting 63 marks
Revenue budget 8
Production budget in units 10
Direct material usage budget 10
Direct material purchase budget 8
Direct manufacturing labour hours budget 10
Manufacturing overhead cost budget  6
Opening and ending raw material inventory costs 5
Opening and ending finished goods inventory costs 5
Cost of goods sold budget 3
Use of Spreadsheets 12 marks
Design of input sheet 4

Design and use of formulas 4

Layout of output sheets 4

Part B – Decision making and relevant information  15 marks

Discussion of relevant costs 5
Discussion of financial costs and issues 5
Discussion of other non-financial issues 5
Assignment Total 90 marks
Assignment Total out of 30 30 marks

Suggested Layout for Output spreadsheets

For example: Revenue budget
Revenue budget for Steel
gazebos
8

April May June Total
Units
S/P per unit
Total sales for Steel gazebos
Revenue budget for Aluminium
gazebos
April May June Total
Units
S/P per unit
Total sales for Aluminium
gazebos

For example: Production budget in units

Steel gazebos April May June Total
Budgeted unit sales
Add budgeted ending fin. gds
inventory

Total requirements
Deduct beginning fin. Gds inventory
Budgeted production

Aluminium gazebos

Budgeted unit sales
Add budgeted ending fin. gds
inventory

Total requirements
Deduct beginning fin. Gds inventory
Budgeted production

For example: Direct materials usage budget
April May June Total
Frames for Steel gazebos
Frames for Aluminium gazebos

LED lights

SOLUTION

Relevant Cost

Relevant costs are the costs appropriate to a specific management decision. These are estimated future costs that are different under alternative courses of action. It includes both fixed and variable are included in the relevant cost (Cowan, 1971). In this case the relevant costs for Aluminum are same. Thus the additional costs that has to be considered for making water proof gazebo. Thus it has to be considered whether machine should be purchased or it should be outsourced. If the machine is bought, then 10000 units will be served. Thus the per unit cost will be $3. Whereas the per unit cost for outsourcing this task is only $25. Thus there will be advantage of $22 in case this task is not outsourced and is manufactured by purchasing the machine. This is to say that according to relevant costing concept it has been easy to make such decisions.

In this case the relevant costing includes the sunk costs which will be incurred if the machine is bought. Thus this has to be considered while making the decision (Bragg, 2012).

Financial Issue and Costs

There are different financial issues that a company might face from arrangement of working capital to the maintenance of debt and equity ratio and thus maintaining the shareholders interest. These all are very important in the context that this reflect the stability and position of the company in the market. In this case these all concerns might come up. The company might face the financial issues for two things. Firstly the finance has to be arranged for purchasing the machine. This can be done either by raising money from the market on interest or utilize the fund from the profits made by the company. In first case the interest will add to the cost for the company and will increase the cost of production. This will reduce the profits for the company. If the funds of the company are used it will first of all result in reduced profits and reserves and will also impact the return on equity. Thus in order to maintain the interest of shareholders the company would have to ensure that such costs that add to the overhead costs for the company.

The budget that has been prepared does not account the additional cost that has come up for making the Gazebos waterproof. Thus this cost will result in reduced profits for the company impacting the budget that has been prepared. Provisions for such additional costs could have been taken up in the costing so that the adjustments need not be made later. Inability of the company to achieve these results as a result of inability to achieve the desired level may spoil the position of the company in the market. These are important issue that needs to be addressed and must involve the higher management and the decision makers in the company.

Non Financial Issues

There are a few non financial issues. These are related to management forecasting. This is to say that the company has forecasted the demand to be in the range of 2000 Aluminum Gazebos (approx). Thus the total for six months will be 12000. Since there is demand from single company itself for 10000 Gazebos, the company will have to make additional arrangements for the production of Aluminum Gazebos and at the same time they will have to ensure that the inventory level is maintained. The inventory level has to be maintained for both raw materials and finished goods. Thus the company will have to take steps so that the issues in operations do not arise.

Another issue the company might face is that the inventory of Steel Gazebo might be impacted for both finished good and the inventory level. Thus the company will have to maintain the balance between the requirement of Steel Gazebos and the Aluminum Gazebo so that the optimum level of production and inventory is maintained without affecting the current orders as well as the order for Aluminum Gazebo.

Thus the management of the operations will be a major task associated with the taking up of the order thus this will result in the management decision and the effectiveness and efficiency of the efforts for achieving the tasks and at the same time to maintain the production of the Steel Gazebos and the Aluminum Gazebos for other orders that may be pending.

Conclusion

Thus the order may be accepted as the selling price is as per the budgeted price but the provisions will have to be taken up for the additional costs that will arise. Also the important decision on the purchase of machine has to be taken and the source of financing for purchasing the machine will be important to decide. The company if is comfortable with the additional cost of the machine and is ready to make suitable adjustments in the operations can go in for accepting the order. The cost associated with the purchase of machine also has the drawback of additional costs that may not been utilized.

The application of relevant cost and the application of other financial and non financial issues have to be carefully studied and applied in this case. This should not be a big issue as such decisions have to be made at certain times and requires critical evaluation and analysis than the complexity of the situation.

References:

Tom Keith Cowan (1971). Management accounting; objectives, systems, analysis of relevant costs. English: Sweet & Maxwell . 554 pages

Steven M. Bragg (2012). Cost Accounting Fundamentals: Essential Concepts and Examples. 3rd ed. English: Kindle Book

KD98

But you can order it from our service and receive complete high-quality custom paper.  Our service offers Accounting  essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”