Based on the information above, how would I fill out a Cash Budget statement in excel? The excel table format I am required to use is the first image.

UPDATE: This is the exact question: "complete the Cash Budget for the project for the period between January 2022 to December 2022 inclusive.

Let me know if you need more information

B F F H T N 0 CASH BUDGET Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 TOTAL Jan-22 415,140 OPENING CASH BALANCE CASH INFLOWS: See Cash collections from customers Other income Bank Loan $ 33.120$ 34,800$ 35,400 $ 40,896 42,336 $ 57,3123 56,449 $ S0,240 45,280S 44,480 S 40,480 61,440 Bike Repairs TOTAL CASH AVAILABLE CASH OUTFLOWS Cash payments for purchases 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 CLOSING CASH BALANCE FactSheet - Data on 2020 Financial Year (Historical Data) Oct-2020 Nov-2020 Dec-2020 Jan-2020 222 Feb-2020 233 Mar-2020 208 Apr-2020 218 May-2020 208 Jun-2020 257 Jul-2020 249 431 Aug-2020 221 Sep-2020 134 311 Sales Volume - Pedal Classic Sales Volume - Folding Urban Dream Service Volume - No. of Hours (Repairs) 179 192 164 306 304 319 325 312 323 438 345 416 422 276 290 295 284 294 398 392 314 283 278 378 384 Selling Price - Pedal Classic Cost Price - Pedal Classic $ $ 500.00 150.00 Selling Price - Folding Urban Dream Cost Price - Folding Urban Dream $ $ 800.00 260.00 Service Revenue Per Hour (Repairs) $ 80.00 Number of Sale Staff: Wage Rate per Employee per Month $ 3 6,000 Number of Office Staff: Wage Rate per Employee per Month 2 3,500 $ Number of Repair Technicians Wage Rate per Employee per Month 4 5,500 $ After much research, Tracie and Conor believe that sales volume, based on the corresponding month in 2020, will increase in 2022 for each eBike each month as follows January, February, March increases by 30%, April to September by 50%, October, November, December by 100%. Given the increase in eBikes, repair hours should also increase. Based on the corresponding months in 2020, Conor anticipates an increase of 50% in billable hours from January, February, March; an 80% increase in hours from April to July; and increase by 100% in billable hours from August to December. Tracie and Conor expect to use the rest of 2021 as a research and preparation period. The expansion itself won't begin until January 2022. The storefront along with all fixtures and furnishings will involve an initial setup costs (i.e. cost of project) that needs to be paid as a lump sum by 1st February 2022. Note that the initial setup cost is broken down as 75% for the storefront with the remaining 25% for general and administration purposes. This overall initial setup is expected to have useful life of 10-years with zero residual value. To finance the project, a bank loan will be acquired which will cover 60% of the initial cost of the project. The loan is with a local bank and it will start from 1st January 2022. The funds from this loan will be deposited into the business bank account on 1st January 2022. The loan is on an interest only arrangement with an interest rate of 12% per annum payable at the end of each quarter. On top of the initial setup costs, the business will need to hire 2 additional Sale Assistants, 2 additional Bike Repair Technician, and 1 additional office staff to help with the general administration of the business. The plan is to have these staff hired at the start of 2022. All new employees are expected to be paid the same rate as current existing staff. All other fixed costs are assumed to increase by 80% based on 2020 amounts. After the 1st year of the new project, in 2023, both the sales volume and service volume are expected to increase by 10% for each month in 2023 compared to the corresponding month in 2022. The expansion project would require the business to purchase one additional specialised equipment for repairs. The suppliers have agreed to provide the business with a 20% discount to the price that they've paid for their exisiting equipment. In addition, the initial cost of the shop fittings will need to increase by 100% for the new store front. The new specialised equipmnent and shop fittings will need to be purchased at the start of January 2021, with payments for these to be made in March 2022. All other non-current assets will remain the same as the 2020 amounts. Additional information: Both Tracie and Conor are owners within the business and each of them draws an amount of $2278 per month. Each owner plans on increasing the drawings to $6834 per month starting from July of 2022 As the manager, Tracie keeps a close eye on inventory management and would like to always maintain a monthly ending inventory requirement of 20% of next month's sales. You can assume the beginning inventory for Jan 2022 to be o units for both Pedal Classic and Folding Urban Dream ebikes. All purchases of inventory with the current supplier are on a credit basis, with 60% of the amount settled in the same month of purchase and the remaining 40% settled one month after the month of purchase. You can assume that all credit amounts for purchase of inventory prior to January 2022 have been settled. The business typically will have 70% of their sales of Pedal Classic and Folding Urban Dream ebikes as cash, with the remaining 30% on credit. Of the credit sales, 40% is collected in the month of sales, 30% is collected one month after the sales, and 30% is collected two months after the sales. You can assume that all credit amounts for sale of inventory prior to January 2022 have been settled. All customers coming in for ebikes repairs pay in cash all of the time. The business has the following non-current assets: In 2019, the business purchased two specialised equipments to provide repairs for the ebikes. The total cost of equipment is $62640 and it is expected to last 5 years. Other office equipment has a total cost of $40500 and depreciates at 25% per annum. Shop fittings were originally purchased at $35800 and depreciate at 30% per annum. All non-current assets have $0 residual value and are depreciated using the straight-line method of depreciation. Other Fixed Costs: Marketing expense is is paid at the start of each quarter and works out to be $12060 per annum. $6120 of business insurance is paid in advance every year on 1 January. This insurance covers the period ist Jan to 31st Dec every year. (70% store; 30% office) Bank fees works out to be $110 per month and are paid at the end of each quarter. On average, the office supplies are purchased and paid at the start of every 2 months (starting from the month of January) for the total amount of $525. The office supplies are expected to be consumed equally each month Council rates are $2652 per year and are usually paid in arrears at the end of December every year. The rates cover the period 1st Jan to 31st Dec every year. (60% store; 40% office) Rent is paid at the start of each quarter and works out to be $8070 per month. (70% store; 30% office) Water, gas and electricity are combined under utility expense which adds up to $621 per quarter and is usually paid at the end of each quarter. (70% store; 30% office) *Note: Quarter 1 runs from 1 Jan-31 Mar; Quarter 2 runs from 1 Apr - 30 Jun; Quarter 3 runs from 1 Jul - 30 Sep; Quarter 4 runs from 1 Oct - 31 Dec. B F F H T N 0 CASH BUDGET Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 TOTAL Jan-22 415,140 OPENING CASH BALANCE CASH INFLOWS: See Cash collections from customers Other income Bank Loan $ 33.120$ 34,800$ 35,400 $ 40,896 42,336 $ 57,3123 56,449 $ S0,240 45,280S 44,480 S 40,480 61,440 Bike Repairs TOTAL CASH AVAILABLE CASH OUTFLOWS Cash payments for purchases 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 CLOSING CASH BALANCE FactSheet - Data on 2020 Financial Year (Historical Data) Oct-2020 Nov-2020 Dec-2020 Jan-2020 222 Feb-2020 233 Mar-2020 208 Apr-2020 218 May-2020 208 Jun-2020 257 Jul-2020 249 431 Aug-2020 221 Sep-2020 134 311 Sales Volume - Pedal Classic Sales Volume - Folding Urban Dream Service Volume - No. of Hours (Repairs) 179 192 164 306 304 319 325 312 323 438 345 416 422 276 290 295 284 294 398 392 314 283 278 378 384 Selling Price - Pedal Classic Cost Price - Pedal Classic $ $ 500.00 150.00 Selling Price - Folding Urban Dream Cost Price - Folding Urban Dream $ $ 800.00 260.00 Service Revenue Per Hour (Repairs) $ 80.00 Number of Sale Staff: Wage Rate per Employee per Month $ 3 6,000 Number of Office Staff: Wage Rate per Employee per Month 2 3,500 $ Number of Repair Technicians Wage Rate per Employee per Month 4 5,500 $ After much research, Tracie and Conor believe that sales volume, based on the corresponding month in 2020, will increase in 2022 for each eBike each month as follows January, February, March increases by 30%, April to September by 50%, October, November, December by 100%. Given the increase in eBikes, repair hours should also increase. Based on the corresponding months in 2020, Conor anticipates an increase of 50% in billable hours from January, February, March; an 80% increase in hours from April to July; and increase by 100% in billable hours from August to December. Tracie and Conor expect to use the rest of 2021 as a research and preparation period. The expansion itself won't begin until January 2022. The storefront along with all fixtures and furnishings will involve an initial setup costs (i.e. cost of project) that needs to be paid as a lump sum by 1st February 2022. Note that the initial setup cost is broken down as 75% for the storefront with the remaining 25% for general and administration purposes. This overall initial setup is expected to have useful life of 10-years with zero residual value. To finance the project, a bank loan will be acquired which will cover 60% of the initial cost of the project. The loan is with a local bank and it will start from 1st January 2022. The funds from this loan will be deposited into the business bank account on 1st January 2022. The loan is on an interest only arrangement with an interest rate of 12% per annum payable at the end of each quarter. On top of the initial setup costs, the business will need to hire 2 additional Sale Assistants, 2 additional Bike Repair Technician, and 1 additional office staff to help with the general administration of the business. The plan is to have these staff hired at the start of 2022. All new employees are expected to be paid the same rate as current existing staff. All other fixed costs are assumed to increase by 80% based on 2020 amounts. After the 1st year of the new project, in 2023, both the sales volume and service volume are expected to increase by 10% for each month in 2023 compared to the corresponding month in 2022. The expansion project would require the business to purchase one additional specialised equipment for repairs. The suppliers have agreed to provide the business with a 20% discount to the price that they've paid for their exisiting equipment. In addition, the initial cost of the shop fittings will need to increase by 100% for the new store front. The new specialised equipmnent and shop fittings will need to be purchased at the start of January 2021, with payments for these to be made in March 2022. All other non-current assets will remain the same as the 2020 amounts. Additional information: Both Tracie and Conor are owners within the business and each of them draws an amount of $2278 per month. Each owner plans on increasing the drawings to $6834 per month starting from July of 2022 As the manager, Tracie keeps a close eye on inventory management and would like to always maintain a monthly ending inventory requirement of 20% of next month's sales. You can assume the beginning inventory for Jan 2022 to be o units for both Pedal Classic and Folding Urban Dream ebikes. All purchases of inventory with the current supplier are on a credit basis, with 60% of the amount settled in the same month of purchase and the remaining 40% settled one month after the month of purchase. You can assume that all credit amounts for purchase of inventory prior to January 2022 have been settled. The business typically will have 70% of their sales of Pedal Classic and Folding Urban Dream ebikes as cash, with the remaining 30% on credit. Of the credit sales, 40% is collected in the month of sales, 30% is collected one month after the sales, and 30% is collected two months after the sales. You can assume that all credit amounts for sale of inventory prior to January 2022 have been settled. All customers coming in for ebikes repairs pay in cash all of the time. The business has the following non-current assets: In 2019, the business purchased two specialised equipments to provide repairs for the ebikes. The total cost of equipment is $62640 and it is expected to last 5 years. Other office equipment has a total cost of $40500 and depreciates at 25% per annum. Shop fittings were originally purchased at $35800 and depreciate at 30% per annum. All non-current assets have $0 residual value and are depreciated using the straight-line method of depreciation. Other Fixed Costs: Marketing expense is is paid at the start of each quarter and works out to be $12060 per annum. $6120 of business insurance is paid in advance every year on 1 January. This insurance covers the period ist Jan to 31st Dec every year. (70% store; 30% office) Bank fees works out to be $110 per month and are paid at the end of each quarter. On average, the office supplies are purchased and paid at the start of every 2 months (starting from the month of January) for the total amount of $525. The office supplies are expected to be consumed equally each month Council rates are $2652 per year and are usually paid in arrears at the end of December every year. The rates cover the period 1st Jan to 31st Dec every year. (60% store; 40% office) Rent is paid at the start of each quarter and works out to be $8070 per month. (70% store; 30% office) Water, gas and electricity are combined under utility expense which adds up to $621 per quarter and is usually paid at the end of each quarter. (70% store; 30% office) *Note: Quarter 1 runs from 1 Jan-31 Mar; Quarter 2 runs from 1 Apr - 30 Jun; Quarter 3 runs from 1 Jul - 30 Sep; Quarter 4 runs from 1 Oct - 31 Dec