Complete Cases 9-45 and 9-46 in the textbook
Cases 9-45 and 9-46 should be prepared in a Word document with embedded Excel spreadsheets for relevant calculations and supporting schedules. You still need to post your Excel worksheet to show support for your calculations. Note, you must show your work in Excel, which includes providing the formulas in the cells, not just the summary value. You may not earn full points if you do not show your work in detail. Suggestion: To complete all parts of the problems correctly you should start each assignment with preparing an Excel workbook. The response for both cases should be between 250-500 words each.
At least one external sources should be cited, not including a textbook.
Problem 9-45
“We really need to get this new material-handling equipment in operation just after the new year begins. I hope we can finance it largely with cash and marketable securities, but if necessary we can get a short-term loan down at MetroBank.” This statement by Beth Davies-Lowry, president of Intercoastal Electronics Company, concluded a meeting she had called with the firm’s top management. Intercoastal is a small, rapidly growing wholesaler of consumer electronic products. The firm’s main product lines are small kitchen appliances and power tools. Marcia Wilcox, Intercoastal’s General Manager of Marketing, has recently completed a sales forecast. She believes the company’s sales during the first quarter of 20×1 will increase by 10 percent each month over the previous month’s sales. Then Wilcox expects sales to remain constant for several months. Intercoastal’s projected balance sheet as of December 31, 20×0, is as follows:
Cash …………………………………………………………………………………………………………………………….. $ 35,000
Accounts receivable ……………………………………………………………………………………………………….. 270,000
Marketable securities ……………………………………………………………………………………………………… 15,000
Inventory ………………………………………………………………………………………………………………………. 154,000
Buildings and equipment (net of accumulated depreciation) ……………………………………………… 626,000
Total assets …………………………………………………………………………………………………………………$1,100,000
Accounts payable ………………………………………………………………………………………………………… $ 176,400
Bond interest payable ……………………………………………………………………………………………………… 12,500
Property taxes payable ………………………………………………………………………………………………………… 3,600
Bonds payable (10%; due in 20×6) …………………………………………………………………………………….. 300,000
Common stock ……………………………………………………………………………………………………………….. 500,000
Retained earnings …………………………………………………………………………………………………………… 107,500
Total liabilities and stockholders’ equity ………………………………………………………………………..$1,100,000
Jack Hanson, the assistant controller, is now preparing a monthly budget for the first quarter of 20×1. In the process, the following information has been accumulated:
1. Projected sales for December of 20×0 are $400,000. Credit sales typically are 75 percent of total sales. Intercoastal’s credit experience indicates that 10 percent of the credit sales are collected during the month of sale, and the remainder are collected during the following month.
2. Intercoastal’s cost of goods sold generally runs at 70 percent of sales. Inventory is purchased on account, and 40 percent 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 firm attempts to have inventory at the end of each month equal to half of the next month’s projected cost of goods sold.
3. Hanson has estimated that Intercoastal’s other monthly expenses will be as follows:
Sales salaries ………………………………………………………………………………………………………. $21,000
Advertising and promotion …………………………………………………………………………………… 16,000
Administrative salaries …………………………………………………………………………………………. 21,000
Depreciation ……………………………………………………………………………………………………….. 25,000
Interest on bonds …………………………………………………………………………………………………. 2,500
Property taxes ……………………………………………………………………………………………………….. 900
In addition, sales commissions run at the rate of 1 percent of sales.
4. Intercoastal’s president, Davies-Lowry, has indicated that the firm should invest $125,000 in an automated inventory-handling system to control the movement of inventory in the firm’s ware-house just after the new year begins. These equipment purchases will be financed primarily from the firm’s cash and marketable securities. However, Davies-Lowry believes that Intercoastal needs to keep a minimum cash balance of $25,000. If necessary, the remainder of the equipment purchases will be financed using short-term credit from a local bank. The minimum period for such a loan is three months. Hanson believes short-term interest rates will be 10 percent per year at the time of the equipment purchases. If a loan is necessary, Davies-Lowry has decided it should be paid off by the end of the first quarter if possible.
5. Intercoastal’s board of directors has indicated an intention to declare and pay dividends of $50,000 on the last day of each quarter.
6. The interest on any short-term borrowing will be paid when the loan is repaid. Interest on Inter-coastal’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.
Required: Prepare Intercoastal Electronics Company’s master budget for the first quarter of 20×1 by completing the following schedules and statements.
1. Sales budget:
20×0 20×1 December January, February, March 1st Quarter
Total sales …………………………..
Cash sales ………………………….
Sales on account ………………..
2. Cash receipts budget:
20×1
January, February, March, 1st Quarter
Cash sales ……………………………………………………….
Cash collections from credit sales made
during current month ……………………………..
Cash collections from credit sales made
during preceding month ………………………….
Total cash receipts …………………………………………….
3. Purchases budget:
20×0 20×1
December January, February, March 1st Quarter
Budgeted cost of goods sold …………………………
Add: Desired ending inventory ……………………….
Total goods needed ……………………….
Less: Expected beginning inventory ……………….
Purchases …………………………………..
4. Cash disbursements budget:
20×1
January, February, March, 1st Quarter
Inventory purchases:
Cash payments for purchases during the current month* ……………………………
Cash payments for purchases …………………………..
during the preceding month† ………………………..
Total cash payments for inventory purchases ……………………………………
Other expenses:
Sales salaries ……………………………………………….
Advertising and promotion ……………………………….
Administrative salaries ……………………………………
Interest on bonds‡ ………………………………………….
Property taxes‡ ……………………………………………..
Sales commissions ………………………………………..
Total cash payments for other expenses ………………………………………….
Total cash disbursements ……………………………………
*40% of the current month’s purchases (schedule 3).
†60% of the prior month’s purchases (schedule 3).
‡Bond interest is paid every six months, on January 31 and July 31. Property taxes also are paid every six months, on February 28 and August 31.
5. Complete the first three lines of the summary cash budget. Then do the analysis of short-term financing needs in requirement (6). Then finish requirement (5).
Summary cash budget:
20×1
January, February, March, 1st Quarter
Cash receipts (from schedule 2) ………………………………
Less: Cash disbursements (from schedule 4) ……………………………………………..
Change in cash balance during period due to operations …………………………………….
Sale of marketable securities (1/2/x1) ………………………
Proceeds from bank loan (1/2/x1) ……………………………
Purchase of equipment …………………………………………
Repayment of bank loan (3/31/x1) …………………………..
Interest on bank loan …………………………………………….
Payment of dividends ……………………………………………
Change in cash balance during first quarter …………………………………………………….
Cash balance, 1/1/x1 ……………………………………………
Cash balance, 3/31/x1 ………………………………………….
6. Analysis of short-term financing needs:
Projected cash balance as of December 31, 20×0 ………………………………………………………………………….. $
Less: Minimum cash balance …………………………………………………………………………………………………..
Cash available for equipment purchases ……………………………………………………………………………………… $
Projected proceeds from sale of marketable securities ………………………………………………………………..
Cash available …………………………………………………………………………………………………………………………… $
Less: Cost of investment in equipment ……………………………………………………………………………………….. Required short-term borrowing …………………………………………………………………………………………………..$
7. Prepare Intercoastal Electronics’ budgeted income statement for the first quarter of 20×1. (Ignore income taxes.)
8. Prepare Intercoastal Electronics’ budgeted statement of retained earnings for the first quarter of 20×1.
9. Prepare Intercoastal Electronics’ budgeted balance sheet as of March 31, 20×1. ( Hint: On March 31, 20×1, Bond Interest Payable is $5,000 and Property Taxes Payable is $900.)
Problem 9-46
City Racquetball Club (CRC) offers racquetball and other physical fitness facilities to its members. There are four of these clubs in the metropolitan area. Each club has between 1,800 and 2,500 members. Revenue is derived from annual membership fees and hourly court fees. The annual membership fees are as follows:
Individual …………………………………………………………………….. $ 40
Student ………………………………………………………………………. 25
Family ………………………………………………………………………… 95
The hourly court fees vary from $6 to $10 depending upon the season and the time of day (prime versus nonprime time).
The peak racquetball season is considered to run from September through April. During this period, court usage averages 90 to 100 percent of capacity during prime time (5:00–9:00 p.m.) and 50 to 60 percent of capacity during the remaining hours. Daily court usage during the off-season (i.e., sum-mer) averages only 20 to 40 percent of capacity.
Most of CRC’s memberships have September expirations. A substantial amount of the cash receipts are collected during the early part of the racquetball season due to the renewal of the annual membership fees and heavy court usage. However, cash receipts are not as large in the spring and drop significantly in the summer months.
CRC is considering changing its membership and fee structure in an attempt to change its cash receipts. Under the new membership plan, only an annual membership fee would be charged, rather than a membership fee plus hourly court fees. There would be two classes of membership as follows:
Individual ……………………………………………………………………. $250
Family ……………………………………………………………………….. 400
The annual fee would be collected in advance at the time the membership application is completed. Members would be allowed to use the racquetball courts as often as they wish during the year under the new plan.
All future memberships would be sold under these new terms. Current memberships would be honored on the old basis until they expire. However, a special promotional campaign would be insti-tuted to attract new members and to encourage current members to convert to the new membership plan immediately.
The annual fees for individual and family memberships would be reduced to $200 and $300, respectively, during the two-month promotional campaign. In addition, all memberships sold or renewed during this period would be for 15 months rather than the normal one-year period. Current members also would be given a credit toward the annual fee for the unexpired portion of their membership fee, and for all prepaid hourly court fees for league play that have not yet been used.
CRC’s management estimates that 60 to 70 percent of the present membership would continue with the club. The most active members (45 percent of the present membership) would convert immediately to the new plan, while the remaining members who continue would wait until their current memberships expire. Those members who would not continue are not considered active (i.e., they play five or less times during the year). Management estimates that the loss of members would be offset fully by new members within six months of instituting the new plan. Furthermore, many of the new members would be individuals who would play during nonprime time. Management estimates that adequate court time will be available for all members under the new plan.
If the new membership plan is adopted, it would be instituted on February 1, well before the sum-mer season. The special promotional campaign would be conducted during March and April. Once the plan is implemented, annual renewal of memberships and payment of fees would take place as each individual or family membership expires.
Required: Your consulting firm has been hired to help CRC evaluate its new fee structure. Write a letter to the club’s president answering the following questions.
1. Will City Racquetball Club’s new membership plan and fee structure improve its ability to plan its cash receipts? Explain your answer.
2. City Racquetball Club should evaluate the new membership plan and fee structure completely before it decides to adopt or reject it.
a . Identify the key factors that CRC should consider in its evaluation.
b . Explain what type of financial analyses CRC should prepare in order to make a complete evaluation.
3. Explain how City Racquetball Club’s cash management would differ from the present if the new membership plan and fee structure were adopted.