Hi

I would like to know how to do these budget in the following:

Manufacturing Overhead Budget

Monthly Operating Cost Budget

Ending Inventory budget for Finished Goods

Cost of Sales Budget

Budgeted Income Statement

Monthly Cash Budget

And then how to write report for this test.

Please guide me outlines and ideas.

Thank you so much

ACC00146 MANAGEMENT ACCOUNTING MAJOR ASSIGNMENT (Session 2 2017) This assignment is worth 30 marks See MySCU for more details about the time and submission requirements Sun Worship Leisure Wear manufactures swimwear and accessories for men and women. They operate out of rented premises in Burleigh Heads where the factory is split into a manufacturing and storage area and a retail space. The business produces 4 products: Bikinis for women Board shorts for men Beach Towels Beach Bags You, as the management accountant for the firm, have been asked to prepare a range of budgets for the 2018 year. The following information has been gathered: Sales January 2018 February 2018 March 2018 April 2018 May 2018 June 2018 July 2018 August 2018 September 2018 October 2018 November 2018 December 2018 January 2019 February 2019 Bikini 1,500 1,200 1,300 1,000 400 500 400 200 1,300 1,200 1,200 1,500 1,600 1,400 Board short 1,200 1,100 1,160 1,000 600 700 500 400 1,160 1,100 1,100 1,200 1,400 1,200 Towel 400 300 260 140 80 40 40 40 260 300 300 400 440 400 Beach Bag 200 180 160 120 60 40 40 40 160 180 180 200 220 200 Selling Price Bikini $100 Board short $75 Towel $45 Beach Bag $65 Product Bikini Board short Direct Materials & Direct Labour per unit Fabric $18.75 $31.25 Elastic/Trim $13.50 $3.00 Direct Labour 1 hour 0.75 hours (sewing time/machine hours) Other Cost information: Direct Labour Indirect Labour Indirect Materials Utilities Insurance Factory Supervisor's Salary Administration Staff Wages General Office Expenses Rent Repairs and Maintenance Production Related Costs $28/hour $1/unit $2/unit $550/month $2,400/year $54,000/year $5,600/month See below Towel Beach Bag $26.25 $5.25 0.4 hours $25 $1.5 0.6 hours Operating Costs $50/month $6,000/year $2,500/month $1,500/month $1,400/month Inventory: At the end of each month the business plans to have 50% of the following month's sales units in stock as finished goods and 75% of the direct materials required for the next month's production. Cash Collections: 20% of sales are through the retail outlet at the factory. These customers pay for their purchases at the time of sale. The remainder of sales are to businesses that resell Sun Worship Leisure Wear products. Credit is extended to all business customers. It is estimated 60% of these sales are collected in the month of sale and the remaining 40% are collected in the following month. Cash Payments: All purchases, other than the purchase of direct materials, are paid at the time the expense is incurred. It is estimated 30% of accounts payable will be paid in the month the direct materials are purchased and the remaining 70% will be paid in the following month. The interest rate payable on the loan is 6% per annum. Interest is paid monthly. The loan is on an interest only basis and the principle can be repaid at any time. Depreciation of plant and equipment totals $9,000 for the year. Depreciation is recorded monthly. Repairs and maintenance related to manufacturing plant and equipment is estimated to be $2,500 each quarter payable in March, June, September, and December. Variable manufacturing overhead is allocated based on machine hours. Fixed manufacturing overhead is allocated based on units of production. Ignore GST and Income Tax. Round amounts other than unit costs to nearest dollar value. The opening Balance Sheet is provided below: Sun Worship Leisure Wear Balance Sheet As at 31 December 2017 Assets Current Assets Cash Accounts Receivable Finished Goods Inventory Materials Inventory Total Current Assets Non-current Assets Equipment Less: Accumulated Depreciation Total Non-Current Assets 48,250 123,650 99,150 114,375 $385,425 85,000 ( 8,000) $77,000 Total Assets Liabilities & Shareholder Equity Current Liabilities Accounts Payable Total Current Liabilities Long-Term Liabilities Bank Loan Total Long-Term Liabilities $462,425 Total Liabilities Shareholders' Equity Share Capital Retained Earnings Total Shareholder Equity $217,500 Total Liabilities & Shareholders' Equity $462,425 92,500 $92,500 125,000 $125,000 150,000 94,925 $244,925 Required: 1. Using Excel, prepare the following budgets for the twelve-month period from January 2018 to December 2018. The first worksheet should contain your raw data and assumptions and all future worksheets should be linked to this data. Use a different worksheet for each budget. Show all calculations and use Excel functions where possible. While you should use examples in your text as a guide, the key to using spreadsheets is that they are structured so that others can use them and follow the flow of information without difficulty. a. Monthly Sales Revenue and Cash Collection Budget b. c. d. e. f. g. h. i. j. Production Budget in Units Direct Materials and Cash Purchases Budget Direct Labour Budget Manufacturing Overhead Budget (break into variable and fixed components) Monthly Operating Cost Budget Ending Inventory budget for Finished Goods Cost of Sales Budget Budgeted Income Statement for the year ended 31 December 2018 Monthly Cash Budget Your budget spreadsheet must be submitted in Excel Format (not as an appendix in your WORD document). 2. Write a report (Maximum 1,500 words) for your manager to present to the upcoming Board meeting. The report should include: an overview of the expected results for the 2018 year; an analysis of the Contribution Margin for each product and recommendations regarding the products Sun Worship Leisure Wear manufacture and sell; recommendations regarding the cash position of the business; and an analysis of current market conditions and future predictions that you believe should be considered when the 2019 budget is prepared. You should use broader industry data and trends from sources such as IBISWorld and the Australian Bureau of Statistics to support your recommendations. Use visual aids such as tables and graphs to enhance your report presentation. The report should be submitted as a WORD (not PDF) document. Assessment Criteria Poor Spreadsheet layout and presentation (2.5 Marks) Spreadsheet Layout Insufficient data. including Raw Assumptions Spreadsheet has not been & Data, appropriate tabs separated into worksheets. correctly named for each Inappropriate layout. section of the budget and Poor use of Excel tools. use of Excel tools in spreadsheet. 0 Marks Preparation of Budgets (12.5 Marks) For each Budget: Budgets are mostly Appropriate layout inaccurate and incomplete. (monthly) broken into An understanding of the product categories; Clear application of the course Headings; Accurate content in a business calculations for monthly context has not been totals and annual totals. demonstrated. 0-2.5 Marks Report for the Board (15 Marks) The report must be Presentation is professionally presented unprofessional with and cover all issues raised numerous spelling and in the assignment grammatical errors and it instructions. A business does not cover the key report format must be requirements set out in the used along with visual aids assignment instructions. such as tables and graphs. Inadequate analysis. Not presented in the format of a business report. Very limited use of visual aids. 0-3.25 Marks Unsatisfactory Satisfactory Use of Worksheets but layout is difficult to follow. Worksheets have not been named. Limited use of Excel tools. Inconsistencies throughout. 1 Mark Use of appropriately named Worksheets. Reasonable use of Excel tools but application is inconsistent. Reasonable layout. Some Budgets are accurate but there are inconsistencies in the data or basic calculations are incorrect. Assignment instructions have not been followed and key items have been left out. 3-6 Marks Presentation is reasonable but the key issues have not been adequately addressed. Spelling and grammatical errors make the report unpresentable requiring the report to be rewritten before it could be presented. Opinions expressed lack insight, are unsupported (inadequate referencing) or referenced external sources lack relevance. Not all aspects of a business report format have been utilised. Limited use of visual aids. 3.5-7 Marks Good Excellent 1.5 Marks Use of appropriately named Worksheets. Consistent use of Excel tools. Layout appropriate but lacks consistency in some areas. 2 Marks Use of appropriately named Worksheets. Very good use of a range of Excel tools. All worksheets are linked resulting in a consistent flow of data between worksheets. 2.5 Marks Most budgets are accurate but there are inconsistencies in the data or basic calculations. Key items have been included and a reasonable understanding of the application of course content has been demonstrated. 6.5-9 Marks All budgets have been prepared and are mostly accurate - minor errors arising from careless mistakes and lack of attention to detail. A very good understanding of the application of course content has been demonstrated. 9.5-10.5 Marks All budgets have been accurately prepared. An excellent understanding of course content has been demonstrated and executed. Presentation is reasonable and the key issues have been addressed but more insight into the business could be demonstrated. Opinions expressed are unsupported (inadequate referencing) or referenced external sources lack relevance. Some minor spelling and grammatical errors. Most components of a business report format have been incorporated. Reasonable use of visual aids. Good, well written presentation and all issues have been covered. Demonstrates a solid insight into the business and recommendations are appropriate. Relevant external sources (at least 2) have been used to support recommendations and these have been appropriately referenced. All components of a business report format have been incorporated. Good use of visual aids. Excellent presentation with no spelling or grammatical errors. Demonstrates excellent insight into the business operations and external issues that may affect the business. Extensive use of relevant external resources to support recommendations. Appropriate referencing. All components of a business report format have been incorporated. Excellent use of visual aids. 7.5-11 Marks 11.25-12.75Marks 13-15 Marks 11-12.5 Marks