MBA 6100-The Watering Hole is a retail establishment that

The Watering Hole is a retail establishment that sells fishing and boating gear. The owner,
Theresa Washington, has enlisted you to prepare a budget for the next quarter, which is her
busiest time of year. Her primary concern is her cash position. She anticipates having to
finance inventory purchases in anticipation of an upswing in demand.
A Please prepare a cash budget for April-June 2017 with supporting schedules in Excel. (See
format outlined) Balance Sheet as of March 32 2017
Assets
Cash
Accounts Receivable
Inventory
Plant and quipment (Net of Depreciation)
Total Assets $
$
$
$
$ 9,000
48,000
12,600
200,000
269,600 Interest Payable $ - Note Payable $ - Accounts Payable
Total Liabilities $
$ 18,300
18,300 Capital Stock $ 180,000 Retained Earnings $ 71,300 Total Equity
Total Liabilities + Equity $
$ 251,300
269,600 Liabilities Equity Schedule A: Sales Budget
April May June April May June Credit Sales
Cash Sales
Total Sales
Schedule B: Expected Cash Collections Schedule C: Purchases Budget for Merchandise Inventory
April
May June Total Schedule D: Expected Cash Disbursements for Inventory Purchases
April May June Total For March Inventory
For April
For May
For June
Schedule E: Operating Expences And disbursments for expences
Cash Expences April May June Total Non Cash Expences April May June Total April May June Schedule F: Cash Budget
Beginning Cash Balance Ending Cash Balance Budgeted Monthly Expences
Saleries & Benefits
POIS System Fee & Equipment Maintenance
Utilities
Insurance
Advertising
Depreciation
Other Expences as % of Sales $
$
$
$
$
$ Policies/Budget Assumptions:
Minimum Inventory Policy as a % of next months COGS
Required Minimum Cash Balance
$
Sales Mix, Cash/Credit
20%
Credit Sales are collected the following month
Gross Profit Rate
Annual Loan Interest Rate
Interest is paid in cash monthly
Inventory purchases paid in cash at time of purchase
Inventory paid for in cash the month after purchase
New Equipment will be purchased in April for $19750 in cash
Dividends of $4000 will be paid in June
Assume that Accounts Payable reflects Inventory Purchases
Insurance is paid quarterly - March, June, Sept, and Dec 8,500
425
700
875
3,000
2,000
10% 30%
8,000
80%
40%
12%
50%
50% The Water Hole
Budgeted Sales:
March Actual
April
May
June
July $
$
$
$
$ 60,000
70,000
85,000
90,000
50,000 B Based on your schedules, write a short report to the owner that answers the following
questions she has posed. This should be in Microsoft Word memo format. 1 Does the cash budget based on the given parameters provide the firm with the
liquidity it needs in meeting operations for the quarter (and why)? 2 The owner wants to know what the impact would be of increasing the minimum
monthly cash balance from $8000 to $9,000. Provide a short but detailed
response to Ms. Washington on the impact of this change. (Of course, she will
expect to see the numerical values that support the response.) 3 Ms. Washington has been working with the same bank for 10 years. She is
accustomed to paying higher than market interest. You believe she could move
to another bank and reduce her interest rate to 8%. Explain what would be the
impact on a) the budget as proposed, and b) and her request for a higher
minimum cash balance?