solve it in excel

CASE SCENARIO As part of the continued advancement of technology, a drone camera market has emerged in recent years. The drone camera market has been growing as more photography enthusiasts have begun adopted this high-tech approach to capturing still images and video using remotely controlled devices. Eager to capture a share of this growing market, Bahrain Tech. Inc. entered the market in early 2018. Bahrain Tech. Inc. manufactures camera drones, selling primarily to retailers. Bahrain Tech. Inc. is pleased with its financial performance over its first few years of operations, optimistic to achieve continued financial success. For planning and control purposes the company utilizes a quarterly master budget, which is usually developed at least three months in advance of the budget year. The company has a fiscal year ending December 31. Based on your discussions with the various departments throughout the company, you have collected the following relevant information for preparing the budget: 1. Annual sales are as follows: For the year ended December 31, 2021: 10,000 units at $950 each* For the year ending December 31, 2022: 11,000 units at $950 each For the year ending December 31, 2023: 12,000 units at $950 each *Sales for the year ended December 31, 2021, were based on actual sales. 2. Peak months for sales generally correspond with summer weather and gift-giving holidays. History shows that January contributes 10% of annual sales, followed closely by Feb-April with 12% of annual sales for each month. Sales spike during summer months with May, June, July, and August contributing 14%, 15%, 12%, and 7% of annual sales respectively. With the back-to-school focus in September, there is a significant dip in camera drone sales to 4% of annual sales. As winter shopping picks up momentum, sales increase to 8% in October, 8% in November, and then 10% in December. This pattern of sales is not expected to change in the next two years. 3. Sales are on a credit basis, with 60% collected during the month of the sale, 30% the following month, and 10% the month thereafter. There are no early payment discounts for customers. 4. Based on the collection pattern described above, accounts receivable as at end of business day on December 31, 2021, is projected to be $456,000 arising from the following estimates: Sales (November 2021) $760,000 Sales (December 2021) $950,000 1 5. Each camera drone requires a total of 4 direct manufacturing labor hours. Due to the highly technical nature of Bahrain Tech, Inc.'s manufacturing process, direct labor rate has averaged $40.00 per hour for 2021, which is expected to increase by 2.5% effective January 1, 2022. This rate already includes the employer's portion of employee benefits. All payroll costs are paid in the period in which they are incurred. 6. Each Bahrain Tech. Inc. requires 2kg of direct materials. It is expected that in 2022, the average cost of direct materials will be $55/kg. The supplier of the direct materials tends to be somewhat erratic, so Bahrain Tech. Inc. finds it necessary to maintain a direct materials inventory balance equal to 40% of the following month's production needs as a precaution against stock-outs. 7. Bahrain Tech. Inc. pays for 20% of a month's purchases of direct materials in the month of purchase, 50% in the following month and the remaining 30% two months after the month of purchase. There are no early payment discounts offered by suppliers. 8. Based on the payment pattern described above, accounts payable as at end of business day on December 31, 2021, is projected to be $125,136 arising from the following estimates: Direct Material Purchases (November 2021) Direct Material Purchases (December 2022) $101,200 $118,470 9. From previous experience, management has determined that an ending finished goods inventory equal to 25% of the next month's sales is required to efficiently meet customer demands. 10. Due to the similarity of the equipment in each of the production stages and the company's concentration on a single product, manufacturing overhead is allocated based on volume (i.e. the units produced). The variable manufacturing overhead rate for 2022 will be $140/unit, consisting of: Plant & Equipment Maintenance $50 Utilities 40 Indirect Materials Other Total 30 20 $ 140 11. The fixed manufacturing overhead costs for 2022 are expected as follows: Annual Supervisor's salary $240,000 Annual Depreciation of Plant & Equipment 132,000 Annual Insurance 240,000 Annual Property and Business Taxes 150,000 84,000 Other annual manufacturing overhead costs Total S 846,000 12. Annual insurance is paid and divided equally in January and February each year and apply to the following 12-month period. 13. The property and business taxes are paid in January of each year and apply to the following 12-month period. 14. Supervisor's salary and other fixed manufacturing overhead costs are incurred evenly over the year. 15. Selling and administrative expenses are known to be a mixed cost Selling and administrative expenses are paid in the month in which they occur. Monthly expenses for 2022 are expected to be as follows: Variable: Sales commissions 3% of sales Fixed: Advertising $ 50,000 Rent $ 35,000 Salaries $ 136,000 Utilities $ 30,000 $ 50,000 Insurance Depreciation $ 60,000 16. To secure and grow its market share and as part of market research cost, Bahrain Tech. Inc. is planning to operate "temporary" kiosks in various local retail centers during peak sales months (May-Aug, Nov-Dec) commencing Nov 2021. Bahrain Tech. Inc. is budgeting a total amount of $70,000 for the market research costs (including all related fees and taxes), payable in January 2022, to set-up and operate these kiosks. The cost to set-up and operate "temporary" kiosks will be in addition to the normal selling and administrative expenses described above. 17. Income tax expense is estimated to be 25% of net income. Bahrain Tech. Inc. makes an income tax installment payment of $46,201 in the second as well as in the last month of each quarter. m 18. Bahrain Tech. Inc. has a policy of paying dividends at the end of each quarter. The President tells you that the Board of Directors is planning on declaring dividends as follows: $61,500 March 2022 June 2022 $70,000 $100,000 September 2022 December 2022 $125,000 Dividends are paid in the month they are declared. 19. The company maintains a minimum cash balance of $50,000. All borrowing is considered to happen on the first day of the month and repayments are on the last day of the month. The company has an agreement with a bank that allows the company to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1.25% per month and for simplicity we will assume that interest is not compounded. At the end of each quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $50,000 in cash. 20. The company's balance sheet as at the end of business day on December 31, 2021is as follows: Assets Cash $100,000 Accounts Receivable 456,000 Inventory: Direct Materials 4,536 Inventory: Finished Goods 12,800 Prepaid Property and Business Taxes 24,000 Prepaid Insurance 21,000 Capital Assets (net) 626,800 Total Assets 1,245,136 Accounts Payable $125,136 Income Taxes Payable 20,000 Capital Stock 500.000 Retained Earnings 600,000 Total Liabilities and Shareholders' Equity 1,245,136 Liabilities and Shareholders' Equity Required: Prepare a master budget for Bahrain Tech. Inc. in excel file for the three-month period ending March 31, 2022 (show the budgets by month and in total for the quarter). Include the following details: - Include your name & ID in the header Budget launch page (content page) Beginning balance budget . Budgeting assumptions 1. Sales Budget 2. Schedule of Expected Cash Collections 3. Production Budget 4. Direct Materials Budget 5. Cash Disbursement for Materials Budget 6. Direct Labor Budget 7. Manufacturing Overhead Budget 8. Selling and Administration Budget 9. Cash Budget Note: 1. Your work in Excel MUST BE BY USING FORMULAS and not just entering amounts. 2. Each of the above requirements should be in a separate worksheet in the excel file. 3. There will also be extra marks for making your workbook interactive for example (links that take you to the different worksheets) as well as proper and neat presentation