I need help with completing this Excel spreadsheet. I don\'t just need the numbe

Question

I need help with completing this Excel spreadsheet.

I don't just need the number answers, I need the calcualtions more and to understand how they were calculated. Thank you!

Here is the info needed to complete the spreadsheet.

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: Annual Cost of borrowing Minimum Cash Balance Beginning Cash Balance Revenues (Sales) November December January February March April May June 10.00% $5,000.00 $7,500.00 $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%) Second Month (35%) Third Month (35%) Total Collections Cash Disbursements Material Purchases Salaries Wages Other Expenses Capital Expenditure Dividends Interest Total Disbursements 6,000.00 6,000.00 6,000.00 6,000.00 6,00000 6,00000 3.000.003 500.00 3.000.003200.003 500.00 3.000.00 45,000.00 1,000.00 1,000.00 Cash flows Net cash flows Cumulative cash flows Minimum Cash Balance Cash surplus or (Deficit)

Explanation / Answer

Data November December January February March April May June Annual Cost of Borrowing 10% Minimum Cash Balance 5000 Beginning Cash Balance 7500 Revenue (Sales) 40000 50000 48000 55000 35000 50000 65000 40000 Cash Collections First Month (30%) 12000 15000 14400 16500 10500 15000 19500 12000 Second Month (35%) 14000 17500 16800 19250 12250 17500 22750 Third Month (35%) 14000 17500 16800 19250 12250 17500 Total Collections 12000 29000 45900 50800 46550 46500 49250 52250 Cash Disbursements Material Purchases 20000 25000 24000 27500 17500 25000 32500 Salaries 6000 6000 6000 6000 6000 6000 Wages 3000 3500 3000 3200 3500 3000 Other Expenses Capital Expenditure 45000 Dividends 1000 1000 Interest 375 Total Disbursements 0 20000 34000 33500 82500 27075 34500 42500 Cash Flows Net Cash Flows 12000 9000 11900 17300 -35950 19425 14750 9750 Cumulative Cash Flows 12000 21000 32900 50200 14250 33675 48425 58175 Minimum Cash Balance 5000 5000 5000 5000 5000 5000 5000 5000 Cash Surplus or (Deficit) 7000 16000 27900 45200 9250 28675 43425 53175

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.