Honolua Company is preparing a cash budget for November.

• The company has $150,000 cash at the beginning of November and anticipates total sales of $1,000,000, consisting of 25% cash sales and 75% bank credit-card sales.
• The bank charges 3% for credit-card deposits.
• The firm sets its selling price at 160% of the cost of purchases and pays the cost of each month's sales at the end of the month.
• Operating expenses are $45,000 per month, of which $25,000 is depreciation expense.
• Selling expenses (commissions) each month amount to 4% of total sales dollars.
• In addition, a $400,000 note will be due in November for equipment purchased last May. In addition to the principal amount, interest for one month (at 12% per year) will be paid in November.
• Honolua Company has line of credit (LOC) agreement with its bank to borrow up to $100,000 for operations. The LOC agreement requires Honolua Company to maintain a minimum cash balance of $100,000.

Required: Use the Excel worksheet provided with the exam to provide support for answering the following questions. Be sure to write your answers in the boxes below each question.

1. Prepare in good form a cash budget that shows the amount, if any, that the company must borrow during November. Separate your budget, at a minimum, into the following categories:

Beginning Cash Balance

Operating Cash Flows (Both Inflows and Outflows)

Cash Balance before Financing Effects

Financing Activity

Ending Cash Balance

2. What is Honolua Company's needed LOC borrowing in November under the above budget?

3. Your supervisor would like to present a Scenario Analysis of possible LOC requirements to the bank LOC officer. In addition to the Expected Sales level in your budget ($1,000,000), use the Excel Scenario Manager tool to prepare a Scenario Summary Analysis of the LOC requirements for a Best Case Scenario (Sales level at 10% above the Expected) and a Worst Case Scenario (Sales level at 10% below the Expected). Be sure to request the Summary in the Scenario Manager tool that creates a separate Scenario Summary worksheet. In the box below, present the Financing Section (Cash Balance before Financing, Financing Activity, Ending Cash Balance) for the three Scenarios. Be sure to include the cells with labels (e.g., "B36" is not an acceptable format).

A B PROBLEM 3. CASH BUDGET & SCENARIO ANALYSIS. HONOLUA COMPANY. DATA: Forecasted Sales S 1,000,000 Cash Sales 25% Bank Credit-Card Sales 75% Bank Fee on Credit-Card Sales 3% Sales Price Markup on Cost 160% Operating Expenses (including Depreciation) 45,000 Depreciation 25,000 Sales Commission Rate (bases on Sales $) 4% Note Due in November (principal) S 400,000 Interest Rate (annual) 12% Beginning Cash Balance 150,000 ur ur Minimum Cash Balance per LOC agreement 100,000 Cash Budget for November