Reference no: EM131805575

MANAGEMENT ACCOUNTING ASSIGNMENT

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	Bikini	Board short	Towel	Beach Bag
January 2018	1,600	1,200	200	400
February 2018	1,400	1,100	180	300
March 2018	1,300	1,160	160	260
April 2018	1,000	1,000	120	140
May 2018	400	600	60	80
June 2018	500	700	40	40
July 2018	400	500	40	40
August 2018	200	400	40	40
September 2018	1,300	1,160	160	260
October 2018	1,200	1,100	180	300
November 2018	1,200	1,100	180	300
December 2018	1,500	1,200	200	400
January 2019	1,600	1,400	220	440
February 2019	1,400	1,200	200	400

 

	Bikini		Board short	Towel		Beach Bag
Selling Price	$100		$75	$55		$45
Product	Bikini		Board short	Towel		Beach Bag
Direct Materials & Direct Labour per unit
Fabric	$18.75		$31.25	$26.25		$25
Elastic/Trim	$13.50		$3.00	$5.25		$1.5
Direct Labour (sewing time/machine hours)	1 hour		0.75 hours	0.4 hours		0.6 hours
Other Cost information:		Production Related Costs			Operating Costs
Direct Labour		$28/hour
Indirect Labour		$1/unit
Indirect Materials		$2/unit
Utilities		$550/month			$50/month
Insurance		$2,400/year			$6,000/year
Factory Supervisor's Salary		$52,000/year
Administration Staff Wages					$2,500/month
General Office Expenses					$1,500/month
Rent		$5,600/month			$1,400/month
Repairs and Maintenance		See below

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	32,250
Accounts Receivable	123,650
Finished Goods Inventory	99,150
Materials Inventory	114,375
Total Current Assets	$369,425
Non-current Assets
Equipment	85,000
Less: Accumulated Depreciation	( 8,000)
Total Non-Current Assets	$77,000
Total Assets	$446,425
Liabilities & Shareholder Equity
Current Liabilities
Accounts Payable	92,500
Total Current Liabilities	$92,500
Long-Term Liabilities
Bank Loan	175,000
Total Long-Term Liabilities	$175,000
Total Liabilities	$267,500
Shareholders' Equity
Share Capital	150,000
Retained Earnings	28,925
Total Shareholder Equity	$178,925
Total Liabilities & Shareholders' Equity	$446,425

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. Production Budget in Units

c. Direct Materials and Cash Purchases Budget

d. Direct Labour Budget

e. Manufacturing Overhead Budget (break into variable and fixed components)

f. Monthly Operating Cost Budget

g. Ending Inventory budget for Finished Goods

h. Cost of Sales Budget

i. Budgeted Income Statement for the year ended 31 December 2018

j. 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.

Please use the templates provided to prepare the:

(1)  Budget schedules (excel); and

(2)  Board Report (word).

Attachment:- Assignment Files.rar