Assignment:
Preparation of master budget, decision on whether to accept or reject a sales order based on certain circumstances, in house production or outsource part of production process.
Kofi Exports manufactures and distributes verities of computer parts, As at December 31, 200X. The cash balance for production activities stands at $2,500,000.00; actual exports for November and December and expected exports for January are as follows.
|
|
November
|
December
|
January
|
|
Cash Exports
|
2,600,000
|
2,350,000
|
6,050,000
|
|
Exports on Account
|
13,500,000
|
16,600,000
|
23,450,000
|
|
Total sales
|
16,100,000
|
18,950,000
|
29,500,000
|
Account receivable are collected over a three month period as follows; 25% collected in the month of Export, 50% collected when the goods are cleared at the port of discharge ( It takes one month to clear the goods from its destination); 25% is collected the following month.
January
Raw materials purchase will total $17,800,000
60% of a month’s inventory purchases are paid for in the month of purchase, the outstanding account payable due this month totals $2,600,000.00
Expected Depreciation and Amortization total $600,000.00 while selling and administrative will be $900,000.00
Kofi's export is expected to either purchase a new assembly line or have an in-house built assembly line. The expected purchase price will be $15,000,000.00, whiles the estimated in-house cost to build the assembly plant will be $15,850,000.00, The company has a total of $5,000,000.00 cash balance saved for this project, with credit arrange with its bank and other financial institution to access in times of need. The normal rate of borrowing for this company is 9.75% per annum. Monthly payment is estimated to be $150,000.00; this payment includes the interest and principal for the month. The loan payment is due at the inception of the loan. The expected extra borrowing for payments reserve should be equal to 6 months.
One of the major exports is desktop computer model SPC560; it requires four chipsets to manufacture one SPC560 unit. The production department is planning a major export base on an order they received from Daga Accounting Solutions an agent for Kwame & Kwame Company in Ghana West Africa. Kofi export’s needs your help to make a decision whether to accept this order.
If the order is accepted, the company will have the following inventory requirement finished goods inventory on hand at the beginning of each month must be equal to 18,000 units plus 40% of the next month sales. December’s production ending inventory is estimated at 6,800, and April ending balance will be 12,000. The raw materials inventory on hand at the end of each month must be equal one-third of the following month’s production needs for raw materials. December 31, 20XX (prior year) raw materials inventory is estimated at 18,600 chipsets. Experience reveals no work in process inventories.
Budget exports for January through March are as follows.
|
Month
|
Budgeted Export
|
|
January
|
420,000
|
|
February
|
510,000
|
|
March
|
680,000
|
|
April
|
720,800
|
Kofi Export’s have had in-house production of the basic raw material, the chipset used to produce model SPC560 desktop computers. The unit price per chipset based on 400,000 chipset per month is as follows.
|
Direct material
|
10
|
|
Direct labor
|
14
|
|
Variable manufacturing overhead
|
2
|
|
Fixed manufacturing overhead
|
9
|
|
Unit product Cost
|
22
|
Kojo & Co. manufacturing offered to supply the chipset to Kofi export for $28.00 per chipset. If Kofi export accepts this offer, they will be able to eliminate all variable cost and 1/8 of the fixed manufacturing cost, representing supervisory salaries. The other seventh-eight of the fixed manufacturing cost is directly traced to equipment. These equipments will have neither resale value nor alternative use.
At the current Activity level of 200,000 units, per month, Kofi’s export cost of producing and exporting model SPC560 desktop computers is as follows:
|
Direct material 4*22
|
88
|
|
Direct labor
|
16
|
|
Variable manufacturing overhead
|
1
|
|
Fixed manufacturing overhead
|
5
|
|
Variable Selling & Administrative
|
1.20
|
|
Fixed Selling & Administrative
|
.80
|
|
Unit product Cost
|
122
|
The normal selling price of model SPC560 without software is $150.00 per unit at 30,000 units of inventory level, without any manufacturing constraints. Kofi export’s currently has excess capacity to produce 30,000 more units without expansion of fixed assets.
Daga Accounting Solutions an agent for Kwame & Kwame Company in Ghana West Africa, order has the following details:
|
Quantity
|
28,000 SPC560 model
|
|
Price per unit
|
130
|
This order will not affect regular sales, since it is due at the end of March. March is the slowest sales season for Kofi export.
Based on the understanding of the above scenario, Please answer the following questions.
1) Prepare the following schedule;
a. Expected cash collections for January 200X
b. Expected cash disbursement for January 200X
2) Prepare a cash budget for January 200X; indicate in the financing sector any borrowing that will be needed during January 200X.
3) Prepare a production budget for January, February and March. Based on the production budget, should Kofi Export's produce more or less units in January and March?
4) In the months of January through March, prepare direct material budget indicating the quantity of chipsets requirements in these months.
5) Using Computations to support your decision advised Kofi exports, whether to outsource the product or to keep in-house production of the chipset.
6) Should Kofi Export's accept the order from Daga Accounting Solutions an agent for Kwame & Kwame Company in Ghana West Africa? If the order is accepted, what will be the range of change in profit?
Please respond using excel.