Precision Machines November,December,January,February,March,April,May and June Annual Cost of borrowing10.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.00Second Month (35%)0.3514,000.0017,500.0016,800.0019,250.0012,250.0017,500.0022,750.00Third Month (35%)0.3514,000.0017,500.0016,800.0019,250.0012,250.0017,500.00Total Collections29000.00$45,900.00$50,800.00$46,550.00$46,500.00$49,250.00$52,250.00Cash Disbursements Material Purchases0.5$20,000.00$25,000.00$24,000.00$27,500.00$17,500.00$25,000.00$32,500.00Salaries6,000.006,000.006,000.006,000.006,000.006,000.00Wages3,000.003,500.003,000.003,200.003,500.003,000.00Other Expenses Capital Expenditure45,000.00 Dividends1,000.001,000.00 Interest35.42Total Disbursements$34,000.00$33,500.00$82,500.00$26,735.42$34,500.00$42,500.00Cash flows Net cash flows$11,900.00$17,300.00($35,950.00)$19,764.58$14,750.00$9,750.00Cumulative cash flows$19,400.00$36,700.00$750.00$20,514.58$35,264.58$45,014.58Minimum Cash Balance$5,000.00$5,000.00$5,000.00$5,000.00$5,000.00$5,000.00Cash Surplus or (Deficit)$14,400.00$31,700.00($4,250.00)$15,514.58$30,264.58$40,014.58 Recommendations:
|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 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.
Review the “Precision Machines” document and spreadsheet.
Prepare a cash budget for Precision Machines in Microsoft® Excel®.
Create a 1000-word strategic analysis and include the following: