Prepare the following budgets for EcoSun Solutions by month and in total for the year ending December 31, 2024:

Prepare the Cash Budget in excel

EcoSun Solutions Inc. is a well-established business that has been producing a single product, high-quality solar pancls, for the past decade. The solar panel industry has experienced substantial growth due to increased demand for renewable energy sources. In response to market opportunities, EcoSun Solutions lne. is considering expanding its eperations to meet growing customer needs. You are provided with the following data to prepare the company's master budget for 2024 . All monetary values are stated in USS. 1. Sales The company conducts market research and assesses its sales pipeline to estimate revenue growth. Based on this research, the company expects sales in January 2024 to be 4,900 solar pancls. Sales in the subsequent months afe expected to increase by 100 units each month for the next 13 months. The selling price initially is set at $400 but a 10% increase in price is to be made in June 2024 . 4. Working Capital Management (a) Accounts Receivable Policy Allow for: 20% of sales to be cash sales and the balance credit sales 75% of credit sales to be received in the month of sale 25% of credit sales to be received one ( 1 ) month affer the sale (b) Accounts Payable Policy Allow for: 25% of purchases to be cash purchases 50% of purchases to be paid for in the month of purchase 25% of purchases to be paid for one (1) month after purchase (c) Closing Inventory Policy Raw Materials. Should be 10% of the following month's production needs Finished Goods - Should be 20% of the following month's sales The expected raw materials inventory on December 31,2024 , is 2,448 The expected finished goods inventory on December 31,2024 , is 1,220 units 5. Standard cost - Manufacture Overhead The production manager is unsure as to how to go about estimating manufacturing overheads but has compiled the following data relating to prior periods. Use regression analysis to derive a cost function for manufacturing overhead cost. Manufacturing overheads are paid in the month incurred. Included in the monthly manufacturing overhead costs is $15,000 depreciation for property, plant and equipment. 6. Administration cost (all fixed) Roughly 75% of these expenses are paid for in the month incurred and 25% in the following month. There is no outstanding payment for administrative costs expected for December 31, 2023. 7. Selling and Distribution Budget (semi-variable) All selling and distribution costs are paid in the month incurred. 8. Financing Activities The company plans to borrow $200,000 in January 2024 and repay this amount along with the incurred interest in July 2024. All bonrowings take place at the beginning of a month, and all repayments are made at the end of a month. The annual interest rate is 10%. EcoSun Solutions Inc. is a well-established business that has been producing a single product, high-quality solar pancls, for the past decade. The solar panel industry has experienced substantial growth due to increased demand for renewable energy sources. In response to market opportunities, EcoSun Solutions lne. is considering expanding its eperations to meet growing customer needs. You are provided with the following data to prepare the company's master budget for 2024 . All monetary values are stated in USS. 1. Sales The company conducts market research and assesses its sales pipeline to estimate revenue growth. Based on this research, the company expects sales in January 2024 to be 4,900 solar pancls. Sales in the subsequent months afe expected to increase by 100 units each month for the next 13 months. The selling price initially is set at $400 but a 10% increase in price is to be made in June 2024 . 4. Working Capital Management (a) Accounts Receivable Policy Allow for: 20% of sales to be cash sales and the balance credit sales 75% of credit sales to be received in the month of sale 25% of credit sales to be received one ( 1 ) month affer the sale (b) Accounts Payable Policy Allow for: 25% of purchases to be cash purchases 50% of purchases to be paid for in the month of purchase 25% of purchases to be paid for one (1) month after purchase (c) Closing Inventory Policy Raw Materials. Should be 10% of the following month's production needs Finished Goods - Should be 20% of the following month's sales The expected raw materials inventory on December 31,2024 , is 2,448 The expected finished goods inventory on December 31,2024 , is 1,220 units 5. Standard cost - Manufacture Overhead The production manager is unsure as to how to go about estimating manufacturing overheads but has compiled the following data relating to prior periods. Use regression analysis to derive a cost function for manufacturing overhead cost. Manufacturing overheads are paid in the month incurred. Included in the monthly manufacturing overhead costs is $15,000 depreciation for property, plant and equipment. 6. Administration cost (all fixed) Roughly 75% of these expenses are paid for in the month incurred and 25% in the following month. There is no outstanding payment for administrative costs expected for December 31, 2023. 7. Selling and Distribution Budget (semi-variable) All selling and distribution costs are paid in the month incurred. 8. Financing Activities The company plans to borrow $200,000 in January 2024 and repay this amount along with the incurred interest in July 2024. All bonrowings take place at the beginning of a month, and all repayments are made at the end of a month. The annual interest rate is 10%