Reference no: EM133920976

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. Get expert-level assignment help in any subject.

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 tothe 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 Borrowingfalls 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.