Precision Machines

Read the following case study:

Precision Machines is preparing a financial plan for the next six months to determine the financial needs of the company. The historical analysis of the company’s sales shows that the company’s total sales are 30% cash sales and 70% credit sales. Further analysis of credit sales shows that the company receives 50% of the credit sales one month after the sale and the remaining 50% in the second month after the sale. This means the cash collections from sales are 30% in the first month of the sale, 35% in the second month, and 35% in the third month.

The materials purchased by the company amounts to 50% of the sales for the month. The company pays for the purchases one month after the initial purchase. The company likes to maintain a cash balance of $5,000. The cost of borrowing is 10%. The company plans to pay off the loan whenever there is a surplus and borrow when there is a deficit.

The attached spreadsheet shows revenues (sales), expenses, capital expenditures, and other expenses for Precision Machines’ next six months. Using the information given on the spreadsheet, prepare a cash budget for January through June and determine the cash surplus, deficit, and the financing needs of the company.
Precision Machines
Student Note: Fill in the light yellow cells
Data:
November December January February March April May June
Annual Cost of borrowing 10.00%
Minimum Cash Balance $5,000.00
Beginning Cash Balance $7,500.00
Revenues (Sales) $40,000.00 $50,000.00 $48,000.00 $55,000.00 $35,000.00 $50,000.00 $65,000.00 $40,000.00

Cash Collections November December January February March April May June
First Month (30%) 0.3 $12,000.00 $15,000.00 $14,400.00 $16,500.00 $10,500.00 $15,000.00 $19,500.00 $12,000.00
Second Month (35%) 0.35 14,000.00 17,500.00 16,800.00 19,250.00 12,250.00 17,500.00 22,750.00
Third Month (35%) 0.35 14,000.00 17,500.00 16,800.00 19,250.00 12,250.00 17,500.00
Total Collections 29000.00 $45,900.00 $50,800.00 $46,550.00 $46,500.00 $49,250.00 $52,250.00

Cash Disbursements
Material Purchases 0.5 $20,000.00 $25,000.00 $24,000.00 $27,500.00 $17,500.00 $25,000.00 $32,500.00
Salaries 6,000.00 6,000.00 6,000.00 6,000.00 6,000.00 6,000.00
Wages 3,000.00 3,500.00 3,000.00 3,200.00 3,500.00 3,000.00
Other Expenses
Capital Expenditure 45,000.00
Dividends 1,000.00 1,000.00
Interest 35.42
Total Disbursements $34,000.00 $33,500.00 $82,500.00 $26,735.42 $34,500.00 $42,500.00

Cash flows
Net cash flows $11,900.00 $17,300.00 ($35,950.00) $19,764.58 $14,750.00 $9,750.00
Cumulative cash flows $19,400.00 $36,700.00 $750.00 $20,514.58 $35,264.58 $45,014.58
Minimum Cash Balance $5,000.00 $5,000.00 $5,000.00 $5,000.00 $5,000.00 $5,000.00
Cash Surplus or (Deficit) $14,400.00 $31,700.00 ($4,250.00) $15,514.58 $30,264.58 $40,014.58

Recommendations: