Excel Applications for Accounting Principles 4th Edition by Gaylord Smith

Exercise 1

PROBLEM DATA
Ranger Industries has provided the following information at June 30:

Projected cash payments:
Inventory purchases are paid for in the month following acquisition.
Variable cash expenses, other than inventory, are equal to 25% of each month's sales and are paid in the month of sale.
Fixed cash expenses are $40,000 per month and are paid in the month incurred.
Depreciation on equipment is $2,000 per month.
REQUIREMENT
You have been asked to prepare a master budget for the upcoming quarter (July, August, and September). The components of this budget are a monthly sales budget, a monthly purchases budget, a monthly cash budget, a forecasted income statement for the quarter, and a forecasted September 30 balance sheet. The worksheet MASTER has been provided to assist you.
Ranger Industries desires to maintain a minimum cash balance of $8,000 at the end of each month. If this goal cannot be met, the company borrows the exact amount needed to reach its goal. If the company has a cash balance greater than $8,000 and also has loans payable outstanding, the amount in excess of $8,000 is paid to the bank. Annual interest of 18% is paid on a monthly basis on the outstanding balance.