Purpose

The overall purpose of this assignment is to enhance your skills with Excel relating to linking raw data into a schedule that will be useful to managers. It will help you integrate and understand the various components of an operating and cash budget.

Required:

In Excel, create a solution with all the following items. ALL RAW DATA SHOULD BE TYPED AT THE BEGINNING OF YOUR SCHEDULES. ALL cells that can be linked need to be$.$ If I change any number in your raw data, the schedules should update appropriately. Your solution needs to have the following items labeled in the following order:

a$.$ Revenues budget $(2$ marks$)$

b$.$ Production budget in units $(2$ marks$)$

c$.$ Direct materials usage budget and direct materials purchases budget $(10$ marks$)$

d$.$ Direct manufacturing labour cost budget $(2$ marks$)$

e$.$ Manufacturing overhead cost budget broken down by the $3$ activities. $(6$ marks$)$

f$.$ Budgeted unit costs of ending finished goods inventory $(6$ marks$)$

g$.$ Budgeted total costs of ending finished goods inventory and direct materials inventory $(4$ marks$)$

h$.$ Cost of goods sold budget $(5$ marks$)$

i$.$ Selling, general and administration and shipping costs budget $(2$ marks$)$

j$.$ Cash budget $(14$ marks$)$

k$.$ Budgeted income statement $(6$ marks$)$

BUDGET DATA:

Akin Manufacturing Ltd$.($AML$)$ produces and sells $2$ products: A$+33$ and AA$34.$ A$+33$ currently sells for $$170,$ and they believe they will be able to sell $2,700$ units in October. AA$34$ currently sells for $$205,$ and it is estimated there will be $3,000$ units sold in October.

On October $1,$ there were $300$ units of A$+33$ with a cost of $$20,400.$ They plan to have $500$ units of A$+33$ in stock on October $31.$ On October $1,$ there were $650$ units of AA$34$ in stock with a cost of $$65,260.$ The October $31$ estimate of finished goods required for AA$34$ is $750$ units.

AML uses FIFO cost$-$flow assumption for finished goods inventory.

Each product uses $2$ direct materials: Mercury and Plastic. The Mercury costs $$6\mathrm{.}50$ per litre and the plastic costs $$8\mathrm{.}60$ per metre. Additional information relating to the materials is below.

Input quantities per Unit of Output

A$+33$ AA$34$

Direct materials:

Mercury $($litres per unit$)0\mathrm{.}8$ litres $1\mathrm{.}4$ litres

Plastic $($metres per unit$)1$ metre $2$ metres

Inventory information: Direct Materials

Mercury Plastic

Beginning inventory $640$ litres $810$ metres

Beginning inventory cost $$3,936$ $$6,156$

Target ending inventory $370$ litres $285$ metres

AML uses FIFO cost$-$flow assumption for materials inventory.

The workers are paid $$16$ per direct manufacturing labour hour. It takes $3$ hours to manufacture a unit of A$+33$ and $4$ hours for each unit of AA$34.$

As well as the costs mentioned above, they also have manufacturing overhead costs, and selling, general and administrative costs. As AML has to change the setup in the facility to switch between A$+33$ and AA$34$ products, they create $50$ units in each batch. It takes $2$ hours to setup the factory for a batch of A$+33$ and it takes $3$ hours to setup the factory for a batch of AA$34.$

AML uses activity$-$based costing and has classified all manufacturing overhead costs for the month of October as follows:

Manufacturing Overhead Categories

Annual Costs Annual Denominator Activity

Setup $$68,1603,408$ Setup hours

Processing $$570,240237,600$ DMLH

Inspection $$47,04067,200$ Units

AML has selling, general and administrative costs that are $8\%$ of the sales revenue. Shipping costs are $$20$ per shipment. The products are sold in quantities of $40$ units per shipment.

Part $1.(39$ marks$)$

Prepare each of the following schedules for the operating budget for October:

a$.$ Revenues budget

b$.$ Production budget in units

c$.$ Direct materials usage budget and direct materials purchases budget in both units and dollars. Round to $2$ decimals.

d$.$ Direct manufacturing labour cost budget

e$.$ Manufacturing overhead cost budgets for setup, processing and inspection activities.

f$.$ Budgeted unit cost of ending finished goods inventory

g$.$ Budgeted total costs of ending finished goods inventory and ending direct materials inventories budget

h$.$ Cost of goods sold budget

i$.$ Selling, general and administration and shipping costs budget

Part $2.(14$ marks$)$

j$.$ Cash budget

From the operating budget schedule of Part $1,$ prepare a cash budget for AML using the additional information provided below for October:

$$ All sales are on account; $65\%$ are collected in the month of the sale, $32\%$ are collected the following month and $3\%$ are never collected and are written off as bad debts.

$$ All purchases of materials are on account. AML pays $60\%$ of purchases in the month of the purchase, and the balance is paid the following month. AML owed $$28,175$ to the suppliers at the end of the previous month.

$$ All other costs are paid in the month incurred.

$30\%$ of setup, processing and inspection costs are depreciated. $15\%$ of selling, general and administration and shipping costs are depreciated.

$$ Dividends worth $$25,00$