Question

I saw that this was solved already, but it's hard to learn it without an explanation on how the figures were calculated.

Using the following information you are to prepare a comprehensive budget for River City Micro Systems, Inc. The Company assembles a specialized device used in airports to detect certain types of explosives to prevent terrorist attacks. Arrangements have been made for the component parts (bundled in packets, one per unit) to be produced in Indonesia, shipped to Boise, then assembled and sold by River City Micro to the end users. You have developed the prototypes, established a market, and now you are putting together a budget for the first three months of 2018. The Company will actually start manufacturing and distribution on January 2, 2018. The purpose of this comprehensive budget is to formalize your expected income, cash flow and balance sheets. From the following information you are to prepare the following schedules/statements for the months ending January 31, 2018, February 28, 2018 and March 31, 2018:

1) Projected units of production

2) Projected raw material requirements

3) Projected raw material purchases in dollars

4) Projected cost of goods manufactured statement

5) Pro-forma income statement

6) Pro-forma cash flow statement

7) Pro-forma balance sheet

8) Capital lease amortization schedule

9) Depreciation schedule You should utilize the following assumptions in making your calculations:

a. Projected sales in units are as follows: January = 500, February = 600, March = 600, April and following months = 800. At the start of each month the management plans to have 30 days, (1 month) of direct materials on hand. Each packet of direct material costs $80.00. The company will have 800 units on hand on January 1, 2018 (all purchased during December 2017).

b. Ten hours of direct labor are required to assemble each device. The direct labor cost (including fringe benefits) is $35.00 per hour.

c. Manufacturing overhead is 50% of direct labor cost.

d. Devices are sold at 100% markup on cost.

e. The company wants to have at least 50% of next months projected sales in ending finished goods inventory each month.

f. Direct materials purchases are paid for on the 10th day of the month following month of purchases.

g. Manufacturing overhead is paid 25% in cash and with the balance paid in 30 days.

h. Wages earned by employees during the first half of each month are paid on the 22nd with the remainder paid on the 7th of the following month. Assume that workforce is stable each month (hence, wages and salaries are the same every day of the month).

i. On January 1, 2018 you acquire equipment and finance it 100% through a capital lease. Life of equipment is 60 months with no salvage value. Capital lease payments are $12,000 per month including an imputed interest component. Your cost of capital is 10%. Use this rate to calculate the present value of the cash payments and the present value of the lease principal as of January 1, 2017. The first payment is due on February 1, 2018.

j. Selling commissions are 10% of sales price. These are paid on the 15th day of the month following month of sale.

k. Administrative salaries and fringe benefits are $60,000 per month payable on schedule outlined in h.

l. Rent is $8,000 per month payable on the first day of each month.

m. On January 1, 2018 the Company will pay 6 months insurance premiums in advance for a total of $24,000.

n. Other general and administrative expenses are estimated to be 15% of sales. They are paid in the month after they are incurred.

o. The company has a $500,000 line of credit secured by inventory and accounts receivable. Borrowing against this line must be in increments of $50,000. Interest is 12% per annum and is payable on the 1st day of the month following the borrowing. Assume all borrowing occur on the 15th day of the month. Repayments must also occur in $50,000 increments on the 15th day of the month.

p. All sales are on account and are collected 15% in month of sale, 75% in next month and the balance in the following month.

q. Income tax rate is 35%. Taxes accrue on each month’s income and are paid in arrears on January 15, Apr 15, Jul 15 and Oct 15 for the preceding quarter. Note: any expected losses create tax benefits that can be used in reduce taxes paid in future quarters.

r. Beginning cash balance on January 1, 2018 is projected to be $100,000 that was raised through the sale of capital stock in December 2017.

Answer 1

:> Due to the more calculation involved in the question, I solved the problem in Excel Spreadsheet and pasted here.

(1) Projected Units of Production Calulation:

River City Micro Systems, Inc.
Projected units of production
For the quarter ended March 31, 2017
	Jan	Feb	Mar	Quarter
Estimated sales - Units	500	600	600	1,700
Add: Ending Inventory - 50% of next month sales (Given in Question)	300	300	400	400
Total Units required	800	900	1,000	2,100
Less: Beginning Inventory	250	300	300	250
Units to be manufactured	550	600	700	1,850

(2) Projected Raw Material Requirements Calculation :

River City Micro Systems, Inc.
Projected raw material requirements
For the quarter ended March 31, 2017
	Jan	Feb	Mar	Quarter
Units to be manufactured	550	600	700	1,850
Add: Desired Ending Inventory - next month's sales	600	700	800	800
Total Material needed for production	1,150	1,300	1,500	2,650
Less: Beginning Inventory	800	600	700	800
Material to be purchased	350	700	800	1,850

(3) Statement showing projected raw material purchases in dollars :

River City Micro Systems, Inc.
Projected raw material purchases in dollars
For the quarter ended March 31, 2017
	Jan	Feb	Mar	Quarter
Material to be purchased	350	700	800	1,850
Material cost - per unit	$80.00	$80.00	$80.00	$80.00
Material cost for the month	$28,000	$56,000	$64,000	$1,48,000

(4) Statement showing the Projected Cost of Goods Manufactured :

River City Micro Systems, Inc.
The projected cost of goods manufactured statement
For the quarter ended March 31, 2017
	Jan	Feb	Mar	Quarter
Units to be manufactured	550	600	700	1,850
Cost of Goods Manufactured
Direct Material - at $80 per unit	$44,000	$48,000	$56,000	$1,48,000
Direct Labor - at $350 per unit	$1,92,500	$2,10,000	$2,45,000	$6,47,500
Manufacturing Overhead - 50% of direct labor cost	$96,250	$1,05,000	$1,22,500	$3,23,750
Total cost of goods Manufactured	$3,32,750	$3,63,000	$4,23,500	$11,19,250

:) Hope You Liked the answer