solutions for just the practical questions including

ACC210
Assignment

Session
2013 60

Due date: Sunday September
15th,
2013 (midnight)

Value: This Assignment is worth
30% of the overall assessment in this subject. The Assignment is marked out of
100.

IMPORTANT:PLEASE
ENSURE YOU READ AND

UNDERSTAND THE
ASSIGNMENT PREPARATION
AND SUBMISSION
REQUIREMENTS

Rationale

The
requirements of this assignment cover up to and including Topic 7 of the
Subject Outline. The assignment is designed to develop your problem solving, spreadsheet
(Excel) design, and written communication skills. The questions require you to
apply the knowledge and tools covered in the subject topics in order to
demonstrate your understanding of the subject content and also to illustrate
your capacity for strategic thinking. The assignment will also test your
ability to communicate and explain the impacts of your findings whether through
quantitative or written reports. The ability to communicate effectively has
been identified by the accounting professional bodies as being critical to your
future role as an accountant.

Note:
The
development and demonstration of a level of technical proficiency in using
spreadsheets to prepare management accounting reports is a key requirement and
expectation of this subject and, more particularly, of this assessment. In
prior offerings of this subject some students have resisted or not fully
engaged with this requirement to effectively use Excel or some compatible
spreadsheet application and have received significantly reduced marks.

Several
short video tutorials on Excel will also be provided through the subject
interact site. These include a tutorial specific to this subject and several
that were prepared for introductory accounting subjects. Indicative examples of
the types of formulae needed and model formats will also be provided, however
you are required to develop your own spreadsheets. If you need further help
with developing your Excel skills you should contact your lecturer or subject
coordinator who will be able to direct you to other resources to assist your
learning.

Assessment
Criteria

You will be
assessed on the following criteria:

1.
The ability to obtain
correct answers for each of the practical questions and sub-questions.

2. Submitted
workings showing how you have obtained your answers, including whether you have
applied appropriate techniques to analyse and solve problems.
3. The
ability to use Excel to solve management accounting problems. This includes the
ability to use appropriate Excel (or similar) analysis tools and functions,
construct appropriate spreadsheet formulae and to effectively and appropriately
print and present your material and results.
4.
The ability to
correctly interpret the results of your analyses and to clearly convey your
understanding of the results to the reader.
5. The
demonstrated, and appropriately communicated, level of understanding of the
theoretical issues associated with the topics covered and your capacity to
apply your understanding strategically to business situations.
6.
The ability to present
your answers, effectively, appropriately, and neatly, using computers.

Preparation
and submission requirements

This
assignment requires a Microsoft Word document as well as a Microsoft Excel
spreadsheet solution and both of these must be submitted online using EASTS.

1. You
must submit both a Word file AND an Excel file.Failure
to submit bothof the files by the due date constitutes non-submission
and late penalties will apply.

2. Your
spreadsheet solutions must be cut and pasted into the Word document.

This
Word document is what will be marked and returned to you. Remember that in the
business world the professional presentation of information is fundamental and
accordingly marks will be deducted for poor presentation. An electronic version
of your source spreadsheet is required to enable markers to open the file and
test your efficient use of spreadsheet formula by, for example, changing values
of input variables. Marks will be awarded on the basis of correctness of
answers, appropriate use of spreadsheet modelling, effective worksheet design,
and level of professional presentation.

3. A
reference list is mandatory for this assessment item.It
is important that youare aware of how to reference properly and a
reference list must be provided, properly formatted using hanging indent. Please
note that it is a submission requirement that you include a reference
list and assignments which do not include a properly formatted reference list
will incur up to a 5 mark penalty

Review
the rules regarding plagiarism and if you are not sure contact your lecturer or
student learning skills advisor for advice. There is no excuse for presenting the
work of others as your own; this includes cutting and pasting material from the
web with out properly referencing the source. In this subject there
has historically been large numbers of students caught either plagiarising or
failing to reference properly.

The
CSU Library site provides an on-line guide to APA style referencing which is
the referencing style adopted by the School of Accounting and Finance. The
guide can be downloaded as a PDF under the Research and Teaching tab on the
library home page

Any difficulties in submitting your
assignments online electronically using EASTS should be immediately reported to
the Subject Coordinator by email. Include your name and student number in the
header or footer of all documents submitted. Retain a copy of your assignment
for your records.

Spreadsheet
requirements

Your
spreadsheet must have a separate worksheet (tab) for each question answered
using Excel. For each question the worksheet should have a data entry section
where all (or most) of the question data is entered, followed by a model or
results section. The results section should be mainly formula driven.

NB
There should be as little as possible data entry in the model/results section
of the spreadsheet. Most, if not all,
data should be imported into the model from thedata entry section.

Question 1 Strategic Management Accounting(15
marks)

You have joined the cross-discipline
Strategic Management Committee of Venus Foods as the management accounting
representative. Venus Foods is a multinational food manufacturer which operates
in the Fast Moving Consumer Goods (FMCG) market. The key issue facing this top
level management group at the moment is how to improve profitability in several
key product categories.

The
product currently under discussion is the ‘Healthy and Delicious’ line of
pre-packaged refrigerated meals sold through the delicatessen sections of the
major supermarket chains. The product has been a great success story for Venus
Foods however lately it has come under increased price competition and sales
and market share have fallen dramatically. The major competition comes from a
brand manufactured by a multinational rival named ‘Quik & Eezy Meals’.

The
Venus Foods Marketing Department has provided the following information about
the pre-packaged refrigerated meals market:

As
the Accounting representative you have provided the Committee with the 2013
breakdown of revenues and costs for the ‘Healthy & Delicious’ product:

Healthy &
Delicious

Total
Assets ‘Healthy & Delicious’ Factory

$10m

Total
Sales (Volume)

3.5m

Regular
Retail Price (per unit)

$7.95

Gross
Sales Value (per unit)

$6.00

Supermarket
Rebates (per unit)

$1.00

Net
Sales Value

$5.00

Prime
Costs

$2.00

Manufacturing
Costs

$2.00

Logistic
Costs

$0.50

Margin
(Gross Profit)

$0.50

Total
Margin

$1.75m

%
Margin on Sales

10%

%
Return on Total Assets (ROTA)

17.5%

The
Marketing Department advises that market research conducted has found that the loss
in sales volume can be attributed to aggressive price discounting by the ‘Quik
& Eezy’ competitor. Marketing believe that if Venus can lower their price
by $0.50 that they will claw back much of the lost market share and could
expect to increase sales by 20% in the coming year. The Research and
Development and Purchasing Departments believe that by introducing new
products, which use less expensive ingredients, the product’s prime costs can
be reduced overall by 10%.

The
CEO of Venus Foods, who is the Chair of the Strategic Committee, advises that
even

allowing for the 10%
reduction in prime costs, discounting the product by $0.50 per unit will mean
that the product will no longer achieve the firm’s required return on assets of
15%. She argues that if this is the case, this previously successful product
line may have to be discontinued.

You
advise the Committee that you are aware that the ‘Healthy & Delicious’
manufacturing facility is currently running at 60% of capacity and that the warehouse
facility (logistics) is running at 50% capacity. You
are aware that the whilst the ‘Healthy & Delicious’ products Prime Costs are 100% Variable, the product’s Manufacturing
Costs and Logistic Costs are made up of 75% Fixed and 25% Variable cost. It can
be assumed that this cost break-down will hold consistently across the industry
including for any ‘Healthy & Delicious’ competitors.

You
ask if you can be given time to prepare a report on the cost and revenue implications
of the budgeted increase in sales and production estimated by the Marketing
Department.

(i)
Using excel prepare a
‘before and after’ comparative analysis of the revenues and costs of the
‘Healthy & Delicious’ product line incorporating the 20% predicted sales
increase and the 10% predicted savings in prime costs.

(10 marks)

(ii)
Prepare a brief report
for the Strategic Management Committee outlining the key points of your
findings. Include some discussion on the likely impact of the changes on the cost
structure on both Venus Foods and its main competitor and the competitive
implications that this may have on the market (assume 90% of the predicted
‘Healthy & Delicious’ sales increase is made at the expense of ‘Quik &
Eezy’ sales).

(5 marks)

Question
2 Income statement (Manufacturing Statement and Profit and loss(10
marks)

Jupiter
Manufacturing Co is a wholly owned subsidiary of Venus Foods. Jupiter
manufacture a range of breakfast cereals for the Australian and export markets.
The company utilises a traditional manufacturing cost flow inventory and
accounting system.

Trading data for
Jupiter Manufacturing Co for the 2013 financial year was as follows:

Account:

$

Purchases of Raw Materials
& Ingredients

10,654,000

Direct Labour

1,677,000

Indirect Labour (including
salaried supervisors)

1,680,500

Direct Manufacturing Overhead
(including depreciation)

2,625,500

Other Manufacturing Overhead

1,847,000

Factory heat, light and power

1,567,500

Administration Salaries and
Costs

1,175,500

Freight Inwards

870,500

Freight Outwards

969,500

Sales Revenue

29,623,500

Accounting & Audit costs

286,000

Interest & other charges

1,100,500

Sales & Marketing Expenses

1,187,500

On June 30th 2013
selected account balances of Jupiter Manufacturing Co were as follows:

Account:

June30 2013

June 30 2012

Cash & Receivables

8,624,500

6,795,000

Plant & Equipment (at cost)

5,865,000

5,865,000

Land & Buildings (at cost)

8,500,000

8,500,000

Accounts Payable

1,320,500

1,113,000

Raw Material Inventory

386,500

426,000

Finished Goods Inventory

452,000

235,000

Work in Process (WIP):

Raw Materials

132,000

202,500

Direct Labour

48,500

56,000

Manufacturing Overhead

427,500

505,500

Total WIP

608,000

764,000

Jupiter
Manufacturing Co is incorporated and operates in Australia and pays tax at the Australian
corporate rate of 30%. There are no adjustments for accruals or prepayments
required.

Using Excel prepare a schedule of cost
of goods manufactured schedule, schedule of cost of goods sold and an income
statement for the Jupiter Manufacturing Co from the information provided. Your
Excel model should include a data input section and appropriate formulae.

(10 marks)

Question 3 Manufacturing
Budget(30
marks)

Following the success of the report you
prepared for the Strategic Marketing Committee of Venus Foods you have been
asked to prepare a 5 year budget forecast for the ‘Ready to eat’ Food Division.

The
company utilises a traditional manufacturing cost flow inventory and accounting
system.

On
June 30th 2013
the following financial and trading data was provided:

2013 Financial Year data (all
costs are per unit)

Sales (Units)

26.8
million

Price (average)

$3.50

Prime Costs (per unit)

Ingredients & Packaging

$2.20

Labour

$0.10

Other Manufacturing Costs (per
unit)

$0.45

Inventory on Hand (at
valuation):

Ingredients & Packaging (1m
units)

$2,200,000

Finished Goods (515,500 units)

$1,475,000

Sales and Marketing Costs

8,675,000

Head Office and Administration
Costs

12,658,500

The
‘Ready to eat’ Food Division maintains target safety stock inventory of raw
materials and finished goods to safeguard against any potential supply chain problems.
Target Ingredients and Packaging Inventory is set at the equivalent of two
(2) weeks of the current year’s budgeted unit production and
Finished Goods Inventory levels are the kept at the equivalent of one (1) week
of the current year’s budgeted unit sales. At the end of the 2013
financial year there was enough Ingredient and Packaging Inventory on Hand to
manufacture 1 million units and there were 515,500 completed units of Finished
Goods in the warehouse. The firm does not utilise a Work in Process inventory
account.

In 2013 the ‘Ready to eat’ Food Division
conducted the launch of several new products and the Sales & Marketing team
believe that modern family pressures will see the ‘Ready to eat’ market
continue to expand. Marketing expect that unit sales will grow at approximately
10% pa over the budget period. They also expect that the product will be able
to achieve price increases of 2.0% pa above the annual projected inflation
rate, which is estimated to hold at 2.25% pa over the budget period.

All
other manufacturing costs including direct labour and ingredient costs are
expected to increase at the rate of inflation. All manufacturing costs
(including overhead) are assumed to vary directly with production (unless
otherwise stated). Venus Foods pays tax at the Australian Corporate tax rate
which is expected to hold at current levels.

The Venus Foods manufacturing facility
is currently operating at approximately 75% of its practical capacity of 35
million units per annum. Senior management have determined that if the
production facility is upgraded a 30% increase in practical capacity can be
achieved. The upgrade can be completed by the end of the 2014 financial year.
If the upgrade is undertaken it will cost the firm $500,000 per annum commencing
in the 2015 financial year.

(i)
Using Excel develop a Sales, Production
and Purchase budget as well as a budgeted Schedule of Cost of Goods
Manufactured, Schedule of Cost of Goods Sold, and an Income Statement for each of
the 5 years in the budget period (commencing 2014)
(advice
on the form of these budgets will be provided on the subject Interact site and
is also available in the Appendix to Chapter 9 of the text book). This budget
must also take into account the manufacturing facility practical capacity
production constraint. Your spreadsheet must include a data section which
enables inputs to be simply altered and ‘what if’ analysis to be undertaken.
(Excel resources are provided on your Interact site to guide students on the
use of the ‘IF’ formula which can be used for the budget production
constraint).
(15 marks)

Hint:
All 5 years of each budget should be shown side by side (1 column per year) for
ease of comparison by management. All of the budgets should be presented on one
worksheet together, working down the page commencing with the Sales and then
Production budgets.

You should be able to drag the
formula across for the whole of the budget if the first years are properly
constructed with a data input section and using absolute referencing. This
makes the process much quicker and easier. An Excel help file has been placed
on the Interact site to assist students.

(ii)
Using the model
developed in part (i) calculate the impact on sales and profit if the option of
upgrading the manufacturing facility is exercised and the practical production
capacity of the factory is increased by 30% (Submit results as a separate
worksheet).

(5 marks)

(iii)
Given your findings from
part (i) and (ii) write a report for the management of the Venus Foods ‘Ready
to eat’ Food Division recommending whether to take up the option to increase
production. In your report consider all of the strategic and financial
implications to the firm of reaching its production constraint and thealternative
of having extra productive capacity. Your grade will depend on the accuracy and
depth of your analysis, and your capacity to identify strategic issues which
management should consider when making their decision.

(10 marks)

Question
4 Process Costing(10
marks)

The
Mercury Pet Food Division of global FMCG company Venus Foods operates three
manufacturing plants at its production site in Tasmania. One of these
manufacturing plants produces ‘Chunky’ canned dog food. Mercury operates a 24 hour
a day, 7 days a week manufacturing facility producing ‘Chunky’ which prepares
product costing reports based on a process costing system.

All
of the ‘Chunky’ Raw Material ingredients are added at the commencement of the
process where the carefully formulated and measured portions the various meats,
vegetables, grains and other supplements are mixed together. The cans are
introduced when the manufacturing process is 20% complete. The dog food mixture
is added to the cans at this point and the cans are sealed and ingredients
cooked. For the purpose of accounting the conversion costs of manufacturing are
assumed to occur evenly across the whole of the production cycle.

The
following information relates to the production of ‘Chunk’ dog food during the
month of May 2013.

Work-in-Process:
May 1, 2013

187,652 Cans

Stage
of completion

Value

Ingredients

100%

$116,000

Cans

100%

$32,000

Conversion

55%

$28,500

Work-in-Process:
May 31, 2013

158,620 Cans

18% Complete

During
May 4,875,000 cans commenced production and the following costs were incurred.

Costs incurred
during May 2013:

Ingredients

$3,546,500

Cans

$1,052,000

Conversion

$1,537,500

Required:

(i)
Using the Weighted
Average Cost Method determine the cost value of closing WIP and the cost value
of goods transferred out during the period.

(5 marks)

(ii) Using
the First In First Out (FIFO) method determine the cost value of closing WIP
and the cost value of goods transferred out during the period

(5 marks)

Question
5 Job Costing (15
marks)

The
Mercury Pet Food firm has a Maintenance & Service Department which performs
all maintenance and repairs on the three pet food manufacturing facility at the
company’s manufacturing site in Tasmania.

Because
of the range and variety of tasks that the Maintenance & Service Department
perform for the different manufacturing facilities a job costing system is used
to record costs and jobs are billed at cost. Materials and Direct labour are
directly charged to each job and Overhead is allocated using Direct Labour as
the cost driver.

According
to its accounts on May 1st
2013 the Maintenance & Service Department Work-in-Process (WIP) account had
a balance of $45,267 which was made up of the following jobs:

Job

Description

Factory

Materials

Direct

Overhead

Total

Labour

675

Re-Build

Chunky

$12,500

$6,000

$2,000

$20,500

switchboard

676

Routine

Budgie

$500

$2,000

$667

$3,167

Maint.

678

Hydraulic

Budgie

$18,500

$0

$0

$18,500

Lift

680

Routine

Katekit

$1,500

$1200

$400

$3,100

Maint.

Totals

$33,000

$9,200

$3,067

$45,267

In
a normal month the Maintenance & Service Department will incur Direct
Labour costs of $120,000 and department overhead is expected to amount to
$40,000 per month. During Maythe following
costs were recorded to job cards in the Maintenance & ServiceDepartment and Jobs 675 through to 684 were
completed and invoiced:

Job No.

Materials

Direct Labour

675

$0

$2,000

676

$500

$4,800

678

$0

$2,700

680

$22,500

$6,000

681

$0

$15,000

682

$84,500

$48,600

683

$1,500

$12,000

684

$62,500

$6,000

685

$25,000

$24,000

$196,500 $121,100

Actual overhead
for the month of May was calculated to be $38,500.

Required:
(i)
Prepare a Job Cost
Summary sheet for the month of May to determine the cost of all
jobs completed
and the closing balance of the WIP account on May 31st. (10marks)

(ii)
Was Overhead over or under-allocated
during May, and if so by how much? (5 marks)

Question
6 Just-in-time(10
marks)

What
are the benefits of operating a JIT manufacturing system? What are the business
risks associated with such an approach?
(10 marks)
Question
7 Activity Based Costing(10
marks)

What
are the benefits of operating an ABC product costing system? Why don’t more
firms adopt the ABC approach?

(10 marks)

Please note that
answering Question 6 and Question 7 will require you to research, read and
reference Management Accounting literature beyond the materials provided in the
text, study guide and subject Interact site.