Cash Budget

Please complete Excel template provided

The goal of this assignment is to demonstrate the process of construction and utilization of a cash

budget for a company. The assignment is closely related to Course Objectives #02.

For this assignment we consider the monthly cash budget for six months (January – June) of the

upcoming year for Pear Inc, manufacturer of swim equipment and lines of clothing. The

company’s sales are highly seasonal with the peak in March through May.

The information used in cash budget development process:

Estimated Sales are given in the Excel Template in millions of US dollars. All sales are on

credit. Assuming that the percentage of bad debt is 0%, so all customers pay back the company.

Accounts Receivable Collections:

30% of sales are collected one month after the sale

70% of sales are collected two months after the sale

Total Collections (Line 7) is the sum of collections for the current month.

Collections for January (87 million dollars) is given and represents collection of accounts

receivable in January from sales in November (57 million dollars) and December (30 million

dollars) of the previous year.

(For simplicity, please disregard the actual dates when sales and collections were done.)

Purchases:

• Inventory purchases consist mostly of purchases materials for production, which is 60%

of the next month sales. (For example, for estimated February sales of 110 million dollars,

the company will buy materials in January. Thus, the size of inventory purchases in

January is 0.6*$110 = $66 (million dollars).

• Payments for inventory purchases are made in the next month following the purchase.

For example, payment for January purchases of $66 million dollars is made in February.

Other Operating Expenses consist of wages, rent, taxes, purchases of equipment, and other

expenses given in Excel template.Projected Cash Budget:

Cash Collections are the sum of accounts receivable collections for the month. We calculate it in

line 7. For example, for January in cell B22 we enter the formula =B7.

Cash Disbursements are equal to the sum of Payment for Inventory and Operating Expenses for

the month. For example, for January in cell B23 we enter the formula =B11+B19.

Net Monthly Change is equal to Cash Collections minus Cash Disbursements for the month.

For example, for January in cell B24 we enter the formula = B22-B23.

Plus: Beginning Cash Balance – beginning cash balance for January is given in the Excel

template.

Ending Cash Balance Before Short-Term Borrowing is equal to the sum of the Beginning

Cash Balance and Net Monthly Change for the month. For example, to calculate Ending Cash

Balance Before Short-Term Borrowing for January in cell B26 we enter the formula = B25+B24

Minimum Desired Cash Balance is given as $10 million dollars. This is the minimum cash

balance the company wants to have in its account. The company needs additional financing if the

cash balance falls below the $10 million minimum desired cash balance.

Cash surplus/Deficit

If the Ending Cash Balance Before Short-Term Borrowing falls below desired minimum cash

balance of $10 million, the company needs additional financing to raise cash balance to the $10

million. This cash deficit is entered as a negative number. For January the Ending Cash Balance

Before Short-Term Borrowing is $9 million dollars, which is below the $10 million minimum

desired cash balance. Thus, the company needs additional financing of $1 million dollars in

January. This amount appears in cell B28 as a negative number. The formula used in cell B28 is

=B26-B27

Excess cash received later will be used to retire the cumulating short-term borrowing from prior

months.

Repayment of Short-Term Borrowing - Excess cash received during the month is used to retire

the cumulating short-term borrowing from prior months.Ending Cash Balance should not be less than minimum desired cash balance $10 million

dollars. For January, Ending Cash Balance is $10 million because the company borrowed $1

million to raise the Ending Cash Balance Before Short-Term Borrowing to the minimum desired

cash balance of $10 million.

If the company has excess cash (positive balance in line 28), this excess cash is used to retire the

parts of cumulative short-term borrowing from prior months. In this case Ending Cash Balance

is equal Ending Cash Balance Before Short-Term Borrowing minus Repayment of Short-Term

Debt. Repayment of short-term borrowing should not drop off the ending cash balance below the

minimum desired cash balance of $10 million.

Cumulative Borrowing for week 1 is $1 million. For the next months the Cumulative

borrowing is a sum of previous and current month borrowing. Excess cash (positive balance in

line 28) will be used to retire the cumulating short-term borrowing from prior months.

IMPORTANT!

Ending Cash Balance for prior month is equal Beginning Cash Balance for the next month.

For example, Ending Cash Balance $10 million for January is equal Beginning Cash Balance for

February. These two cells are highlighted in purple.