-Done on excel

-Help need this done today or asap, must be done on excel

Googly Ltd. is a company that manufactures and sells a single product called Huggle. For planning and control purposes they utilize a monthly master budget, which is developed in advance of the budget year. Their fiscal year end is April 30.

The sales forecast consisted of these few lines:

• For the year ended April 30, 2023: 540,000 units at $22.00 each*
• For the year ended April 30, 2024: 560,000 units at $23.00 each
• For the year ended April 30, 2025: 580,000 units at $24.00 each

*Sales for the year ended April 30, 2023 are based on actual sales to date and budgeted sales for the duration of the year.

Your investigations of the companys records have revealed the following information:

1. Peak months for sales correspond with gift-giving holidays. History shows that January, March, May and June are the slowest months with only 6% of sales for each month. Sales pick up over the summer with July, August and September each contributing 8% to the total. Valentines Day in February and Easter in April account for 9% of total sales. As Christmas shopping picks up momentum, winter sales start at 10% in October, move to 11% in November and then peak at 13% in December. This pattern of sales is not expected to change in the next two years.

1. Sales are on a cash and credit basis, with 50% collected during the month of the sale, 30% the following month, and 18% the month thereafter, with 2% of total sales considered uncollectible (bad debt expense). Sales in March and April 2023 are expected to be $726,000 and 1,089,000 respectively. Based on the above collection pattern this will result in Accounts Receivable of $653,400 at April 30, 2023, which will be collected in May and June 2023.

1. From previous experience, management has determined that an ending inventory equal to 30% of the next months sales is required to fit the buyers demands. The beginning finished goods inventory for May 2023 is expected to be 10,350 units.

1. Because sales are seasonal, Googly must rent an additional warehouse from October to December to house the additional inventory on hand. The only related cost is a flat $20,000 per month, payable at the beginning of the month.

1. There is only one type of raw material used in the production of Huggle: Huggle Acrylic (HA) is a very compact material that is purchased in powder form. Each Huggle requires 0.5 kilogram of HA, at a cost of $15.00 per kilogram. The supplier of HA tends to be somewhat erratic so Googly finds it necessary to maintain an inventory balance equal to 40% of the following months production needs as a precaution against stock-outs. The beginning inventory for May 2023 is estimated to be 6,900 kg of HA.

1. Googly pays for 45% of a months purchases in the month of purchase, 30% in the following month and the remaining 25% two months after the month of purchase. There is no early payment discount. Beginning accounts payable will consist of $244,744 arising from the following estimated direct material purchases for March and April of 2023:

HA purchases in March, 2023: $305,775

HA purchases in April, 2023 $306,000

1. Googlys manufacturing process is highly automated, so their direct labour cost is low. Employees are paid at an average rate of $20.00 per hour. This rate already includes the employers portion of employee benefits. All payroll costs are paid in the period in which they are incurred. Each unit spends, on average, 9 minutes (i.e. 0.15 Hours) in production. However, due to the fluctuations in actual sales, the company maintains a workforce of 40 full-time employees who are guaranteed a minimum of 160 hours each. During months in which production is low, these workers perform other tasks such as equipment maintenance. In months when additional workers are needed, Googly hires temporary workers at $20 per hour. For simplicity, include all labour costs in the labour budget.

1. Variable manufacturing overhead is allocated based on units produced. The unit variable overhead manufacturing rate is $4.00.

1. The fixed manufacturing overhead costs for the entire year is estimated to be $960,000, including depreciation of manufacturing equipment, $192,000. Fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred. Googly uses the straight-line method of depreciation.

1. Selling and administrative expenses are largely fixed and are estimated to be $924,000 for the year. These costs are paid in the month in which they occur, except for the only non-cash item: a monthly depreciation of office equipment in the amount of $4,750. Starting in fiscal 2024, Googly intends to pay salespeople a commission of $1 per unit sold. This cost is not included in the estimate above, nor are bad debt expense (see point 2) or warehouse rental (see point 4).

1. Googlys average income tax rate is 35%. During the fiscal year ended April 30, 2024, Googly will be required to make monthly income tax installment payments of $50,000. Outstanding income taxes from the year ended April 30, 2023, must be paid in December 2023. Income taxes for the year ended April 30, 2024, in excess of installment payments, will be paid in December 2024.

1. Prior to the busy season, Googly is planning to purchase new manufacturing equipment for which they will need to pay cash. The bid that was accepted totaled $1,000,000, which will be paid in in 8 equal monthly instalments of $125,000 beginning in July 2023. Manufacturing overhead costs above already include the depreciation on this equipment.

1. An arrangement has been made with the local bank that if Googly maintains a minimum balance of $50,000 in their bank account, they will be given a line of credit at a preferred rate of 8.4% per annum (i.e., 0.7% per month). All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. All borrowings and repayments from the bank should be in multiples of $1,000 and interest must be paid at the end of each month. Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that month (i.e., any borrowing for a month must occur at the beginning of that month). Googly has a loan balance of $225,000 at April 30, 2023.

15. Googly has a policy of paying dividends at the end of each month. The President tells you that the board of directors plans to continue their policy of declaring dividends of $30,000 per month.

1. A listing of the estimated balances in the companys ledger accounts as of April 30, 2023 is given below:

Cash	$50,000		Accounts payable	$244,744
Accounts receivable	653,400		Bank Loan	225,000
Inventory-raw materials	103,500		Income tax payable	155,000
Inventory-finished goods	165,600		Capital stock	1,000,000
Capital assets (net)	2,427,500		Retained earnings	1,775,256
	$3,400,000			$3,400,000

Required:

Prepare a monthly master budget for Googly for the year ended April 30, 2024, including the following schedules:

1. Sales Budget & Schedule of Cash Receipts
2. Production Budget
3. Direct Materials Budget & Schedule of Cash Disbursements
4. Direct Labour Budget
5. Manufacturing Overhead Budget
6. Selling and Administrative Expense Budget
7. Cash Budget
8. Income Statement
9. Balance Sheet