Bilker Corp is a small, rapidly growing wholesaler of consumer electronic produc
ID: 2459806 • Letter: B
Question
Bilker Corp is a small, rapidly growing wholesaler of consumer electronic products. The company’s 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 company’s sales
during the first quarter of 2016 will increase by 7 percent each month over the previous month’s sales.
Then sales are expected to remain constant for several months. BilkerCorp’s 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 Corp’s 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 Corp’s cost of goods sold generally runs at 65% of sales. Inventory is purchased on
account and 25% of each month’s 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 month’s projected cost of goods sold.
3) The controller has estimated that Bilker Corp’s 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 company’s warehouse
just after the new year begins. This equipment purchase will be financed primarily from the
company’s 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 Corp’s 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 Corp’s 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 Corp’s budgeted statement of retained earnings for the quarter ended March 31,
2016.
(3) Prepare Bilker Corp’s 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.
Explanation / Answer
Collection Schedule of Sales December January February March Sales $850,000 909500 973165 1041287 Credit Sales $510,000 $545,700 $583,899 $624,772 Cash Sales $340,000 $363,800 $389,266 $416,515 Collection during the Month $102,000 $109,140 $116,780 $124,954 Collection in the following Month of Sale $408,000 $436,560 $467,119 Total Collection of Sales $880,940 $942,606 $1,008,588 Payment Schedule for Inventory Purchase December January February March Sales $850,000 $909,500 $973,165 $1,041,287 Cost of Goods Sold $552,500 $591,175 $632,557 $676,836 Closing Inventory $354,705 $379,534 $406,102 $406,102 Opening Inventory $354,705 $379,534 $406,102 Cost of Purchase $616,004 $659,125 $676,836 Payment for current Month's purchase $154,001 $164,781 $169,209 Payment for Previous Month's Purchase $431,779 $462,003 $494,343 Total Payment for Purchase $585,780 $626,784 $663,553 Cash Flow Budget of Biker Corporation for the First Quarter ended 31st March 2016 January February March Opening Cash Balance $25,000 $20,750 $153,191 Opening Marketable Securities $15,000 - Collection from Sales $880,940 $942,606 $1,008,588 Total Cash Available $920,940 $963,356 $1,161,779 Total Disbursements Cash Payment for Purchase $585,780 $626,784 $663,553 Sales Salaries $65,000 $65,000 $65,000 Advertising and Promotion $30,000 $30,000 $30,000 Administrative Salaries $40,000 $40,000 $40,000 Interest on Bonds $24,000 $0 $0 Property taxes $0 $12,000 $0 Sales Commisison $34,000 $36,380 $38,927 Automated Inventory control system $360,000 $0 $0 Dividend Paid $0 $0 125000 Total Payment $1,138,780 $810,164 $962,479 Finace Short Term Loan $241,000 0 ($176,000) Interest Payment ($2,410) ($2,410) ($2,410) Closing Balnace $20,750 $153,191 $20,890 0 Budgeted Statement of Retained Earnins of Biker Corporation Sales $ 2,923,952 Cost of Goods Sold $ 1,900,569 Gross Profit $ 1,023,383 Sales Salaries $195,000 Advertising and Promotion $90,000 Administrative Salaries $120,000 Interest on Bonds $12,000 Property taxes $6,000 Sales Commisison $116,958 Interest on Loan $7,230 Depreciation $246,000 Net Profit $ 230,195 Dividend $125,000 Retained Earnings $ 107,604 Budgeted Balance Sheet of Biker's Corporation as on 31st March 2016 Assets Amount Cash On hand $20,890 Accounts receivable $499,818 Inventory $406,102 Buidlings & Equipment Net of Depreciation $1,576,000 Total Assets $2,502,809 Accounts Payable $507,627 Sales Commission Payable $41,651 Bond Interest Payable $8,000 Property taxes payable $2,000 Bonds Payable $600,000 Short term Loan $65,000 Common Stock $750,000 Retained Earnings $ 528,530 Total Liablities and Sharholders Equity $2,502,809
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.