Completed project should
be submitted in an EXCEL workbook format with a separate page for each
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 2015. The Company will actually start
manufacturing and distribution on January 2, 2015. The purpose of this
comprehensive budget is to formalize your expected income, cash flow and
From the following
information you are to prepare the following schedules/statements for the
months ending January 31, 2015, February 28, 2015 and March 31, 2015:
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
utilize the following assumptions in making your calculations:
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, 2015 (all
purchased during December 2014).
b. Ten hours of direct labor are required to assemble each device.
The direct labor cost (including fringe benefits) is $35.00 per hour.
Manufacturing overhead is 50% of direct labor
d. Devices are sold at 100% markup on cost.
The company wants to have at least 50% of next
months projected sales in ending finished goods inventory each month.
Direct materials purchases are paid for on the
day of the month following month of purchases.
g. Manufacturing overhead is paid 25% in cash and with the balance
paid in 30 days.
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).
On January 1, 2015 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, 2014. The first payment is due on February 1,
Selling commissions are 10% of sales
price. These are paid on the 15th day of the month following month of sale.
Administrative salaries and fringe benefits are
$60,000 per month payable on schedule outlined in h.
Rent is $8,000 per month payable on the first
day of each month.
m. On January 1, 2015
the Company will pay 6 months insurance premiums in advance for a total of
Other general and administrative expenses are
estimated to be 15% of sales. They are paid in the month after they are
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.
Beginning cash balance on January 1, 2015 is
projected to be $100,000 that was raised through the sale of capital stock in
2. Complete the project as
a group assignment. Different tasks can be assigned to various group members
but everyone needs to actively participate.
3. Check figures will be
supplied at various times over the next ten days.
4.Submit one final copy
for a group. All group member names must appear on each schedule.
5. Submit all of your
answers as a single multi-tabbed Excel workbook from the assignment module. If
you have difficulty submitting the Excel file from the assignment module you
can attach the workbook to an email.
6. Please be patient as I take time to grade this written assignment.
You may want to check your gradebook from time to time over the next week to
read any feedback I provide and see your grade.