Tasks

Part A

You have recently been appointed as a budget analyst for Turnbull and Co a manufacturer of golf bags.

The Balance Sheet as at 1 July 2014 is as follows:

Management wants you to prepare a budget for the first quarter of the coming financial year and provides you with the following information. 

Sales (in units):

Actual

May     6,000

Jun     8,000

Budgeted

Jul     7,000

Aug   9,000

Sep   10,000

Oct    7,000

Nov   10,000

Each golf bag is expected to sell for $39.00 (ex GST). All sales are on credit.

Discount is not offered for early payment. The sales collection is expected to follow previous months as follows:

Current month of sale         25%

1st month following sale     30%

2nd month following sale     45%

The accounts receivable balance at 1 July 2014 consists of:

$115,830 owing from May Sales ($105,300 + GST $10,530)

$257,400 owing from June Sales ($234,000 + GST $23,400)

Finished Goods inventory

It is the company‘s policy that each month‘s closing stock of finished golf bags is to cover 40% of the next month‘s sales units.

Direct materials

Turnbull and Co uses ½ metre of material to make each golf bag. The material costs $18.00 per metre. Management wants to hold sufficient material at the end of each month to meet 15% of the next month‘s production requirements.

Raw materials are paid for in the month after the month of purchase.

June‘s purchases are $68,670 (ex GST)

Direct labour

It takes ¼ hour of direct labour to produce each golf bag. Production staff are paid an average of $21.00 per hour. Direct Labour is paid for in the month incurred.

Factory overhead

Factory overhead is to be applied on the basis of golf bags produced. The application rate per unit is as follows: 

Factory overhead     $7.00

Included in each month‘s factory overhead is depreciation of factory plant of $2,600. Factory overhead is paid for in the month incurred.

Selling, administrative and finance expenses

Fixed expenses per month are expected to be as follows:

Wages                                         $11,700

Administrative expenses          $3,900

Selling expenses                        $8,500

Finance expenses                        $650

Depreciation office furniture  $1,300

Variable selling expenses are $1.00 for each golf bag sold.

The values above exclude GST – GST needs to be added where appropriate into Budgets.

Other information

$117,000 of the loan principle is due to be repaid in September.

New plant costing $156,000 (ex GST) will be purchased on credit, with payments to be made equally in July and August. Depreciation for this plant has already been considered in the allocation of fixed overhead.

GST is to be applied to the Cash payments Budget for Turnbull and Co. 

Required

Using the Excel Template for Assignment 2 from the OLS, complete the following tasks noting:

Your spreadsheets must include a data sheet into which all data is entered.

Worksheets need to contain formulae and/or links to the data sheet or a subsequent worksheet. There are to be no absolute numbers in budgets or reports.

Use the number format and use the 1000 separator (,) for amounts over 1,000.

Do not round unit costs in your calculations. Show unit costs in dollars and cents.

Ensure each budget or report has the name of the business, the type of report or budget and specifies the period to which it applies.

Prepare the following budgets for July, August and September.

Sales budget (showing units and $)

Production budget (in units)

Direct material purchases budget

Direct labour budget (showing hours and $)

Factory overhead budget

Schedule of collection from accounts receivable

Cash Payments budget

In addition, prepare:

A schedule showing the cost of each golf bag

A budgeted Income Statement for the quarter ended 30 Sept 2014 (month by month details are not required). Round all figures to the nearest dollar.