( 3 ) 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...

(
3
)
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 both of 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 co
ectness 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 you are aware of how to reference properly and a reference list must be provided, properly formatted using APA guidelines with a 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.
Spreadsheet requirements
Your spreadsheet must have a separate worksheet (tab) for each question answered. 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.
(
10
)
Assignment Background:
Whoo Hoo you have just landed yourself a graduate accounting job with Donas Sisters!! Donas Sisters is an extremely successful Australian musical girl band that has produced 5 top selling singles in Australia and in the United States in XXXXXXXXXXis looking to be an even bigger year for the band with several songs cu
ently being produced and an expanding merchandise range due to be released.
Your will be based in Albury, the heart of pop music, where your role will involve giving advice on the strategic and operational issues to management and production teams across a number of product ranges that will be manufactured. Your role includes working with other companies and verifying and understanding the information that they provide you with.
At the commencement of each question you are refe
ed to the Topic Modules which cover the material in the question.
Good luck with you new role...it will certainly be a challenging one!!
Question 1    Manufacturing Cost Schedule and Income Statement    (10 marks). This question relates to learning material and objectives from Online Topics 1, 2 and 3.
Donas Sisters merchandise range includes a variety of clothing and promotional food packaging labels. A 2 year contract between Donas Sisters and Uncle Tom’s was signed in January 2019. The agreement allowed Uncle Tom’s to use copyrighted photographs and logo graphics from Donas Sisters in return for a 12% share on net profit before tax plus a monthly royalty fee
The management accountant from Uncle Tom’s has unexpectedly fallen ill with food poisoning and you have been asked to finalise the reporting for Uncle Tom’s product line of ‘Donas Sisters’ Delicious Bars. The following information has been emailed through to you from the management accountant’s assistant for the 2019 calendar year:
    Inventory Account:
    Jan 1, 2019
    Dec 31, 2019
    Work in Process (WIP) Inventory:
    $ Opening
    $ Closing
    Raw Materials
    11,650
    13,800
    Direct Labou
    2,650
    3,000
    Manufacturing Overhead
    3,500
    4,500
    Raw Material Inventory
    12,400
    13,300
    Finished Goods Inventory
    65,000
    110,700
    Corporate Tax rate (30%)
    30%
    
    Profit Share (12%)
    12%
    
    Account:
    $
    Accounting & Audit costs
    13,780
    Depreciation of Factory Plant, Equipment & Machinery
    85,000
    Depreciation of Office Equipment & Furniture
    2,500
    Factory Direct Labour Cost
    95,260
    Factory Indirect Labour Cost
    18,400
    Factory Manufacturing Overhead
    123,450
    Freight Inwards
    4,550
    Freight Outwards
    32,150
    Heat Light & Power Costs (70% Factory/30% Admin)
    66,750
    Interest & other finance charges
    50,000
    Office Salaries and Costs
    16,400
    Purchases of Raw Materials & Packaging
    425,800
    Royalty Fee
    1,650
    Sales & Marketing Expenses
    82,000
    Sales Revenue
    1,305,665
Required:
Using Excel, from the information provided prepare the following financial reports:
Schedule of Cost of Goods Manufactured, Schedule of Cost of Goods Sold, and an after tax income statement for Uncle Tom’s for the 2019 calendar year.    (10 marks)
Note:    Your Excel model should include a data input section and appropriate formulae. An example of the Manufacturing reports required can be found in the text book.
Question 2    Process Costing    (15 marks)
This question relates to learning material and objectives from Online Module 4.
Uncle Tom factory in Tasmania is responsible to manufacturing the Direct Calorie Bar. The factory employs a process costing system. Due to the resignation of the management accountant who continues to suffer from food poisoning, you have been asked to prepare the end of period product costing.
The process for manufacturing the ‘Direct Calorie Bar’ involves mixing all the ingredients. The ingredients are then poured into moulds and injected with extra pure sugar and snap frozen. It is assumed for process costing purposes that all raw material ingredients are added at the commencement of the process. Packaging occurs when the manufacturing process is 70% complete. 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 Direct Calorie Bar during the month of November.
    Work-in-process: November 1, 2019
    8500
    
    Stage of completion
    Value
    Raw materials
    100%
    $    170,000
    Packaging
    100%
    $    63,750
    Conversion
    65%
    $    260,000
    Work-in-process: November 30, 2019
    15,300
    
    60% compete
    Cars commenced during Novembe
    36,000
    Cost incu
ed during production:
    
    Raw materials
    $    160,000
    Packaging
    $    82,500
    Conversion
    $    350,000
Required:
(i) Using the Weighted Average Cost Method determine the cost value of closing WIP and the cost value of goods transfe
ed out during the period.    (7 marks)
(ii) Using First in First Out (FIFO) method determine the cost value of closing WIP and the cost value of goods transfe
ed our during the period.    (8 marks)
Question 3    Comprehensive Manufacturing Budget    (30 marks)
This question builds on prior studies and relates to learning material and objectives from Online Modules 1, 2 and 3. Links to specific resources provided for this question relating to Manufacturing Budgets and Excel spreadsheets can be found in the Online Topic Modules.
You have been asked to prepare a 3 year budget forecast for the manufacturing of Donas Sisters dolls. Donas Sisters outsources the production of the dolls to the company ‘Dolled Up’.
‘Dolled Up’ utilises a traditional manufacturing cost flow inventory and accounting system and has provided you with the following information as at December 31st 2019 to create a budget from.
    2019 Year data
    
    Sales (Units)
    825,000
    Price (average 2019 price received)
    $24.95
    
    
    Prime Costs (per unit)
    
    Ingredients & Packing (including various fa
ic, wading and plastic head plus packaging costs)
    
$12.00
    Direct Labou
    $7.50
    Variable Manufacturing Costs (per unit)
    $1.10
    Factory Management Salaries (per annum)
    $650,000
    Factory Plant & Equipment Depreciation (per annum)
    $350,000
    Sales and Marketing Costs (per annum)
    $925,000
    Finance Costs (per annum)
    $862,000
    Non-Factory Administration Costs (per annum)
    $885,000
    Inventory on Hand (at valuation):
    
    Ingredients & Packaging (250,000 units)
    $3,750,000
    Finished Goods (175,000 units)
    $4,366,250
‘Dolled Up ‘maintains a target safety stock of raw materials inventory and finished goods inventory amounting to the equivalent of three (3) weeks of the cu
ent year’s budgeted unit sales. At the end of the 2019 calendar year there were 175,000 completed units of ‘Uno Dolls’ in the warehouse as Finished Goods. There are enough raw materials on hand to manufacture 250,000 units of Donas Sisters dolls.
The Research and Marketing Department at ‘Donas Sisters’ predict that unit sales of the Donas Sisters doll will continue to grow for the next 3 years at a rate of 5% above the 2.95% cu
ent long term rate of inflation (budgeted 7.95% increase per annum), however after the 3 years sales will drop off significantly. The company is budgeting to achieve a year on year price increase of 4.5% over the long term inflation rate (7.45% annual increase). All other costs including direct labour, material costs, and other overhead and administration costs are expected to increase annually at the rate of inflation. The company pays tax at the Australian Corporate tax rate which is expected to hold at 30%. The inflation rate of 2.95% is expected to hold over the 3 year budget period.
‘Dolled Up’ recently had to modify an old factory to be able to manufacture the Donas Sisters dolls. However due to the expected growth in sales, the factory is nearing its practical manufacturing capacity of 950,000 dolls.
Required:
(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 3 years in the budget period (commencing January 1, XXXXXXXXXXadvice on the
form of these budgets is linked through the online topic modules and in the Interact Resources folder 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 (such as the inflation rate, budgeted cost and sales increases, and the production limit) 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 3 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 and video which deals with the formula required has been placed in the Resources folder in the subject Interact site to assist students (linked through Online Module 3).
(ii) If sales continue to grow beyond the 3 years as the trend shows, the practical production capacity of 950,000 units will constrain ‘Dolled Up’ in a couple of years. The CEO of ‘Dolled Up’ has the option of investing in new plant equipment now and will be ready to start manufacturing by 2017. The new plant will have the capacity to manufacture 1.1 million dolls. A consulting engineering firm has advised that an investment of $20 million dollars will increase the life of