Juan's Taco Company has restaurants in five college towns. Juan wants to expand into Austin and College Station and needs a bank loan to do this. Mr. Bryan, the banker, will finance construction if Juan can present an acceptable three-month financial plan for January through March. Following are actual and forecasted sales figures:

Actual Forecast Additional Information
November
$120,000
January
$190,000
April forecast
$230,000

December
140,000
February
210,000

March
230,000

Of Juan's sales, 30 percent are for cash and the remaining 70 percent are on credit. Of credit sales, 40 percent are paid in the month after sale and 60 percent are paid in the second month after the sale. Materials cost 20 percent of sales and are paid for in cash. Labor expense is 50 percent of sales and is also paid in the month of sales. Selling and administrative expense is 5 percent of sales and is also paid in the month of sales. Overhead expense is $12,000 in cash per month; depreciation expense is $25,000 per month. Taxes of $20,000 and dividends of $16,000 will be paid in March. Cash at the beginning of January is $70,000, and the minimum desired cash balance is $65,000.

For January, February, and March, prepare a schedule of monthly cash receipts, monthly cash payments, and a complete monthly cash budget with borrowings and repayments.

Thank you for taking the time to copy your homework to AMHD.
Please refer to this announcement: https://www.askmehelpdesk.com/finance-accounting/announcement-font-color-ff0000-u-b-read-first-expectations-homework-help-board-b-u-font.html

This is not HOMEWORK.
This is ADVANCED PLANNING.
I used to do this type of work 15 years ago.
See whether I can help somewhat.
I wish my EXCEL spreadsheet could help better.


JUAN'S TACO COMPANY

NOV DEC JAN FEB MAR APR

TOTAL SALES 120,000 140,000 190,000 210,000 230,000 230,000

CASH @ 30% 36,000 42,000 57,000 63,000 69,000 69,000
CREDIT @ 70% 84,000 98,000 133,000 147,000 161,000 161,000

TOTAL 120,000 140,000 190,000 210,000 230,000 230,000

CASH FLOW

CASH @ 30% 36,000 42,000 57,000 63,000 69,000 69,000
1 MON AFTER 33,600 39,200 53,200 58,800 64400
2 MOS AFTER 50,400 58,800 79,800 88200

TOTALS 36,000 75,600 146,600 175,000 207,600 221,600

MATERIALS 24,000 28,000 38,000 42,000 46,000 46,000
LABOUR 60,000 70,000 95,000 105,000 115,000 115,000
S & ADMIN 6,000 7,000 9,500 10,500 11,500 11,500
OVERHEADS 12,000 12,000 12,000 12,000 12,000 12,000
TAXES 20,000
DIVIDENDS 16,000

TOTALS 102,000 117,000 154,500 169,500 220,500 184,500

CASH RECEIPTS 146,600 175,000 207,600
CASH PAYMENTS -154,500 -169,500 -220,500

NET -7,900 5,500 -12,900

CASH AT START 70,000 65,000 65,000
NET -7,900 5,500 -12,900
62,100 70,500 52,100
BORROWINGS / REPAYMENT 2,900 -5,500 12,900
65,000 65,000 65,000

CONCLUSION TOTAL BANK Accommodation. 10,300

I had this type o problem in my introductory finance class.