Assignment #$4$ Developing a Master Budget $[30\%]$

This assignment relates to the following Course Learning Activity:

$$

CLR $5$: Analyze financial result and plan for subsequent periods.

Using spreadsheet software create a multi$-$period master budget for manufacturing company.

$$

Activity $$ After reviewing Module $6-$ Instructor$$s Notes and completing an internet search, prepare a $12-$month Master Budget for a company of your choosing.$$ It can be a company that you are closely connect to or one randomly selected.$$

Remember: As you are preparing the Master Budget, some information is NOT provided. You are required to decide or predict or forecast this information.

Guidelines

You have $3$ weeks to complete this assignment. Assignment #$4$ is released to you in the $6$th week $($module $6)$ of this course and is due at the end of week $8($module $8).$ Your facilitator will advise you of these dates and any further information.

$$

Tasks:

$1.$ Please provide a brief introduction for the company.$$ The introduction should include:

$-$ Name of company

$-$ Location of company

$-$ Name of owner

$-$ Nature of the business.

$-$ Type of product

$-$ Fiscal period

$$

$2.$ For the company that you have selected $-$ Prepare a $12-$month Master Budget. $$Your submission must show each month$$s activities $$ example January, February, March etc.

$$

$3.$ Include a column and a line Total and complete calculations of them by months and by Item line.

$$

$$

$4.$ Here are some considerations to include:

$-$ You must forecast your sales, purchases, direct labour and manufacturing overhead for each month and any other information as required.

$-$ All sales are on accounts

$-$ Expected collections are to be $50\%$ in the month of the sale, $30\%$ in the first month following the sale, and $20\%$ in the second month following the sale.$$

$-$ Production the company will end each month with sufficient inventory to cover $20\%$ of the following month$$s sales. The newyear started with $600$ units in stock and plan to finish each year with $825$ units in stock.

$-$ It pays $60\%$ of direct materials purchase in cash in the month of purchase and the balance is due in the month following the purchase.$$ It pays all other items above in the month incurred, except for selling and administrative expenses that include $$1,000$ of amortization per month.

$-$ Cost of Goods Sold: $60\%$ of the sale and desired ending inventory $20\%$ of next month$$s sale

$-$ Payment Pattern: $60\%$ of purchases paid in the month of the purchase and $40\%$ paid in the next month.

$-$ Cash in Bank $$ January $2020$ is $$20,000.$

$-$ The company expected to purchase equipment in March $2020$ for $$10,000.$

$-$ Dividend paid per quarter $$2,000.$

$-$ Selling and administration expense per month $ $40,000.$

$-$ Equipment balance as at December $31,2029$ is $$60,000.$ Accumulated depreciation $$ straight line; $5$ years

$-$ Common stock is $ $6,000$

$$

$5.$ The company has access to a line of credit with the bank with the following terms $$ interest rate is $1\%$ per month on the first day of the month, interest payable is calculated at $1\%$ of the previous month$$s outstanding balance.$$ Borrowings in a given month are taken out at the beginning of the month.$$

$$

$6.$ The company wishes to maintain a minimum balance of $$10,000$ at all times.

$$

$7.$ Ensure that your total boxes correctly total up the rows in your columns.

$$

$8.$Submission $$ in an Excel Spreadsheet

a$.$ Brief introduction for the company $$ Spreadsheet $1(5$ Marks$)$

b$.$ Master Budget in an excel spreadsheet $$ Spreadsheet $2.(80$ marks$)$