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 products 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 firms 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 products 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 years budgeted unit production and Finished Goods Inventory levels are the kept at the equivalent of one (1) week of the current years 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 companys 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 dont 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.
