Bilker Corp is a small, rapidly growing wholesaler of consumer electronic products. The companys main product lines are small kitchen appliances and power tools. Bilker Corp is interested in purchasing somenew material handling equipment right after the beginning of 2016. They would like to finance the new equipment with cash and marketable securities, but if necessary they can get a short term loan from a local bank.

You have been engaged to review the work of a staff accountant who prepared a master budget for

Bilker Corp for the first quarter of 2016.After a preliminary review, the president is certain the

schedules contain errors. The president needs an accurate master budget in order to make financial

decisions for the upcoming quarter.

The marketing manager has recently completed a sales forecast. She believes the companys sales

during the first quarter of 2016 will increase by 7 percent each month over the previous months sales.

Then sales are expected to remain constant for several months. BilkerCorps projected balance sheet as of December 31, 2015 is as follows:

Cash $ 25000

Accounts receivable $408000

Marketable securities $15000

Inventory $354705

Buildings and equipment (net of accumulated depreciation) $1462000

Total assets $2264705

Accounts payable $ 431,779

Sales commission payable 34,000

Bond interest payable 20,000

Property taxes payable 8,000

Bonds payable (8%; due in 2025) 600,000

Common stock 750,000

Retained earnings 420,926

Total liabilities and stockholders' equity $ 2,264,705

The staff accountant used the following information to prepare a budget for the first quarter of 2016:

1) Projected sales for December 2015 are $850,000. Credit sales are typically 60% of totals sales.

Bilker Corps credit experience indicates that 20% of credit sales are collected during the month

of sale, and the remainder are collected during the following month.

2) Bilker Corps cost of goods sold generally runs at 65% of sales. Inventory is purchased on

account and 25% of each months purchases are paid during the month of purchase. The

remainder is paid during the following month. In order to have adequate stocks of inventory on

hand, the company attempts to have inventory on hand at the end of each month equal to 60%

of the next months projected cost of goods sold.

3) The controller has estimated that Bilker Corps other monthly expenses will be as follows:

Sales salaries $ 65,000

Advertising and promotion 30,000

Administrative salaries 40,000

Depreciation 82,000

Interest on bonds 4,000

Property taxes 2,000

In addition, sales commissions run at the rate of 4 percent of sales. Sales commissions are paid

in the month following the sale

4) The company president has indicated that the company should invest $360,000 in an automated

inventory-handling system to control the movement of inventory in the companys warehouse

just after the new year begins. This equipment purchase will be financed primarily from the

companys cash and marketable securities. However, the president believes the company needs

to keep a minimum cash balance of $20,000. If necessary, the remainder of the equipment

purchase will be financed using short-term credit from a local bank. The minimum period for

such a loan is three months. The current short-term interest rate is 12 percent per year and is

expected to remain at this rate through the time the equipment is purchased. If a loan is

necessary, the president has decided that it should be paid off by the end of the first quarter, if

possible. If the entire loan cannot be paid off, the maximum amount should be paid on the last

day of the quarter, while maintaining the minimum cash balance. Partial payments must be

made in $1,000 increments.

5) Bilker Corps board of directors has indicated an intention to declare and pay dividends of$125,000 on the last day of each quarter.

6) The interest on any short-term borrowing will be paid at the end of each month. Interest on

Bilker Corps bonds is paid semiannually on January 31 and July 31 for the preceding six-month

period.

7) Property taxes are paid semiannually on February 28 and August 31 for the preceding six-month

Period.

The master budget schedules prepared by the staff accountant are as follows:

Required:

(1) Prepare corrected budget schedules using the same schedule templates the staff accountant used.

Utilize excel functions as much as possible. Round all amounts to the nearest dollar.

(2) Prepare Bilker Corps budgeted statement of retained earnings for the quarter ended March 31,

2016.

(3) Prepare Bilker Corps budgeted balance sheet as of March 31, 2016. (Hint: On March 31, 2016,

Bond Interest Payable is $8,000 and Property Taxes Payable is $2,000.)

(4) Prepare a memo to the president of Bilker Corp. regarding the results of your engagement. Your

memo should include recommendations you may have for the company based on your work.

PS. I really ned help with the excel formulas. I will loose points if I do not include them.