See attached
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.
