The main question begins here screenshots may be out of order is this better?

Benjamin Dover, president of Bulldog Company, was just concluding a budgeting meeting with his senior staff including Kathryn Anderson, Bulldog's controller and her minion the sales manager, Sharona Jackson. It was November of 2021, and the group was discussing preparation of the firm's master budget for 2022. "I've decided to go ahead and purchase the new production machinery we've been talking about. My criminal case is set for 2023 and my lawyers have an escape plan should I be convicted so let's buy that thing on January 2 of next year to hopefully take advantage of the new tax code. I expect it should take us most of the year to train our lazy workers and to reorganize the entire production process so we can take full advantage of the new machinery." Kat asked "How much is that there thing gonna cost us and stuff? How can we afford it? I thought our credit was shot and that." Dover replied as follows: "The machine will cost $800,000. I told you that before. We're financing it with a one-year $800,000 loan from Enron Bank and Trust. I've negotiated a repayment schedule of four equal installments of $200,000 on the last day of each quarter. The interest rate will be 10%, and interest payments will be made quarterly as well." With that the meeting ended, and the budgeting process was on. Bulldog Company is a manufacturer of airplane window frames. The firm's two product lines are designated as AA window frames which are 57 inches and BB window frames which are 810 inches. The primary raw materials are flexible metal strips and 9-inch by 24-inch unbreakable plexi-glass sheets. Each AA window frame requires a 2-foot metal strip while the BB window frame requires a 3-foot strip. Allowing for normal breakage and scrap glass, Bulldog Company can get either four (4) AA frames or two (2) BB frames out of a glass sheet. Other raw materials, such as Styrofoam and vegetable oil, are insignificant in cost and treated as indirect materials. Kathryn Anderson is in charge of preparing the master budget for 2022. She has gathered the following information: 1. Sales in the fourth quarter of 2021 are expected to be 50,000 AA frames and 40,000 BB frames. Sharona Jackson predicts that over the next two years, sales in each product line will grow by 10% each quarter over the previous quarter. For example, AA frame sales in the first quarter of 2022 are expected to be 55,000 units. 2. Bulldog's sales history indicates that 75% of all sales are made on credit, with the remainder of the sales in cash. The company's collection experience shows that 70% of the credit sales are collected during the quarter in which the sale is made, while the remaining 30% is collected in the following quarter. Tony Soprano Inc., our collection agency, has ensured us that we will always collect 100% of our receivables. 3. The AA window frames sell for $12, and the BB frame sells for $20. These prices are expected to hold constant throughout 2022. 4. Bulldog's production manager, Madonna Ciccone, attempts to end each quarter with enough finished Bulldog's projected balance sheet as of December 31,2021 , follows: goods inventory in each product line to cover 20% of the following quarter's sales. Moreover, an attempt is made to end each quarter with 20% of the plexi-glass sheets needed for the following quarter's production. Since metal strips are purchased locally, Bulldog buys them on a just-in-time business from Tony Soprano, Inc; inventory is thus negligible. 5. All of Bulldog's direct materials purchases are made on account and 60% of each quarter's purchases are paid in cash during the same quarter as the purchase. The other 40% is paid in the next quarter. 6. Indirect materials are purchased as needed and paid for immediately in cash. Work-in-process inventory is negligible. 7. Projected manufacturing costs in 2022 are as follows: Total Liabilities and 5/1 58.591,630 Required: Prepare Bulldog Company's master budget for 2022 by completing the following schedules and statements using MS excel. I will send you a template which you are welcome to change/modify in any way. I want you to send me an electronic copy of you finished work and print one out for me. On the second spreadsheet, I also want you to change the selling price of the AA frames to $14 and the BB frames to $18 and the indirect materials and labor to 13% and send me that spreadsheet as well. Call it part B. 8. The predetermined overhead rate is $10 per direct-labor hour. The following manufacturing overhead costs are budgeted for 2022. Indirect materials and indirect labor are budgeted to be $10,200 and $40,800 respectively in the first quarter and increase by 12% each quarter over the previous quarter. Other overhead is budgeted to be $31,000 in the first quarter and is expected to increase by 14% each quarter over the previous quarter. Depreciation will amount to $80,000 over the year and depreciates evenly (straight-line) over the year. Therefore, total overhead should amount to $102,000 in the first quarter, but cash outlay equal to $82,000 (because depreciation isn't paid in cash). All of these costs will be paid in cash during the quarter incurred except for the depreciation charges. 9. Bulldog's quarterly selling and administrative expenses are budgeted to be $80,000 in the first quarter and increase by 8% each quarter over the previous quarter, always paid in cash. 10. Anderson anticipates that dividends of $50,000 will be declared and paid in cash each quarter. BULLDOG COMPANY Master Budget 1. Sales Budget: Cash sales* Sales on account 30% of total sales. 70% of total sales. 2 T 3. Production budget: \begin{tabular}{cc|cccc} \multicolumn{2021}{c}{} & \multicolumn{5}{c}{2022} & & \\ 4th & 1st & 2nd & 3rd & 4th & Entire \\ \cline { 2 - 6 } Quarter & Quarter & Quarter & Quarter & Quarter & Year \\ \hline \end{tabular} AA frames: Sales (in units) Add: Desired ending inventory Total units needed Less: Expected beginning inventory Units to be produced BB frames: Sales (in units) Add: Desired ending inventory Total units needed Less: Expected beginning inventory Units to be produced 4. Raw-material budget: 5. Cash disbursements budget: \begin{tabular}{ccc|ccccc} \hline & \multicolumn{1}{c}{} & \multicolumn{1}{c}{2022} \\ \hline & 1st & 2nd & 3rd & 4th & Entire \\ \cline { 2 - 7 } & Quarter & Quarter & Quarter & Quarter & Year \\ \hline \end{tabular} Raw-material purchases: Cash payments for purchases during the current quarter* Cash payments for purchases during the preceding quarter Total cash pmts. for raw-material purchases Direct labor: Frames produced (S and L) Direct-labor hours per frame Direct-labor hours to be used Rate per direct-labor hour Total cash payments for direct labor Manufacturing overhead: Indirect material Indirect labor Other Total cash payments for manufacturing OH Cash pmts. for selling and admin. expenses Total cash disbursements 70% of current quarter's purchases 30% of previous quarter's purchases 6. Summary cash budget: Benjamin Dover, president of Bulldog Company, was just concluding a budgeting meeting with his senior staff including Kathryn Anderson, Bulldog's controller and her minion the sales manager, Sharona Jackson. It was November of 2021, and the group was discussing preparation of the firm's master budget for 2022. "I've decided to go ahead and purchase the new production machinery we've been talking about. My criminal case is set for 2023 and my lawyers have an escape plan should I be convicted so let's buy that thing on January 2 of next year to hopefully take advantage of the new tax code. I expect it should take us most of the year to train our lazy workers and to reorganize the entire production process so we can take full advantage of the new machinery." Kat asked "How much is that there thing gonna cost us and stuff? How can we afford it? I thought our credit was shot and that." Dover replied as follows: "The machine will cost $800,000. I told you that before. We're financing it with a one-year $800,000 loan from Enron Bank and Trust. I've negotiated a repayment schedule of four equal installments of $200,000 on the last day of each quarter. The interest rate will be 10%, and interest payments will be made quarterly as well." With that the meeting ended, and the budgeting process was on. Bulldog Company is a manufacturer of airplane window frames. The firm's two product lines are designated as AA window frames which are 57 inches and BB window frames which are 810 inches. The primary raw materials are flexible metal strips and 9-inch by 24-inch unbreakable plexi-glass sheets. Each AA window frame requires a 2-foot metal strip while the BB window frame requires a 3-foot strip. Allowing for normal breakage and scrap glass, Bulldog Company can get either four (4) AA frames or two (2) BB frames out of a glass sheet. Other raw materials, such as Styrofoam and vegetable oil, are insignificant in cost and treated as indirect materials. Kathryn Anderson is in charge of preparing the master budget for 2022. She has gathered the following information: 1. Sales in the fourth quarter of 2021 are expected to be 50,000 AA frames and 40,000 BB frames. Sharona Jackson predicts that over the next two years, sales in each product line will grow by 10% each quarter over the previous quarter. For example, AA frame sales in the first quarter of 2022 are expected to be 55,000 units. 2. Bulldog's sales history indicates that 75% of all sales are made on credit, with the remainder of the sales in cash. The company's collection experience shows that 70% of the credit sales are collected during the quarter in which the sale is made, while the remaining 30% is collected in the following quarter. Tony Soprano Inc., our collection agency, has ensured us that we will always collect 100% of our receivables. 3. The AA window frames sell for $12, and the BB frame sells for $20. These prices are expected to hold constant throughout 2022. 4. Bulldog's production manager, Madonna Ciccone, attempts to end each quarter with enough finished Bulldog's projected balance sheet as of December 31,2021 , follows: goods inventory in each product line to cover 20% of the following quarter's sales. Moreover, an attempt is made to end each quarter with 20% of the plexi-glass sheets needed for the following quarter's production. Since metal strips are purchased locally, Bulldog buys them on a just-in-time business from Tony Soprano, Inc; inventory is thus negligible. 5. All of Bulldog's direct materials purchases are made on account and 60% of each quarter's purchases are paid in cash during the same quarter as the purchase. The other 40% is paid in the next quarter. 6. Indirect materials are purchased as needed and paid for immediately in cash. Work-in-process inventory is negligible. 7. Projected manufacturing costs in 2022 are as follows: Total Liabilities and 5/1 58.591,630 Required: Prepare Bulldog Company's master budget for 2022 by completing the following schedules and statements using MS excel. I will send you a template which you are welcome to change/modify in any way. I want you to send me an electronic copy of you finished work and print one out for me. On the second spreadsheet, I also want you to change the selling price of the AA frames to $14 and the BB frames to $18 and the indirect materials and labor to 13% and send me that spreadsheet as well. Call it part B. 8. The predetermined overhead rate is $10 per direct-labor hour. The following manufacturing overhead costs are budgeted for 2022. Indirect materials and indirect labor are budgeted to be $10,200 and $40,800 respectively in the first quarter and increase by 12% each quarter over the previous quarter. Other overhead is budgeted to be $31,000 in the first quarter and is expected to increase by 14% each quarter over the previous quarter. Depreciation will amount to $80,000 over the year and depreciates evenly (straight-line) over the year. Therefore, total overhead should amount to $102,000 in the first quarter, but cash outlay equal to $82,000 (because depreciation isn't paid in cash). All of these costs will be paid in cash during the quarter incurred except for the depreciation charges. 9. Bulldog's quarterly selling and administrative expenses are budgeted to be $80,000 in the first quarter and increase by 8% each quarter over the previous quarter, always paid in cash. 10. Anderson anticipates that dividends of $50,000 will be declared and paid in cash each quarter. BULLDOG COMPANY Master Budget 1. Sales Budget: Cash sales* Sales on account 30% of total sales. 70% of total sales. 2 T 3. Production budget: \begin{tabular}{cc|cccc} \multicolumn{2021}{c}{} & \multicolumn{5}{c}{2022} & & \\ 4th & 1st & 2nd & 3rd & 4th & Entire \\ \cline { 2 - 6 } Quarter & Quarter & Quarter & Quarter & Quarter & Year \\ \hline \end{tabular} AA frames: Sales (in units) Add: Desired ending inventory Total units needed Less: Expected beginning inventory Units to be produced BB frames: Sales (in units) Add: Desired ending inventory Total units needed Less: Expected beginning inventory Units to be produced 4. Raw-material budget: 5. Cash disbursements budget: \begin{tabular}{ccc|ccccc} \hline & \multicolumn{1}{c}{} & \multicolumn{1}{c}{2022} \\ \hline & 1st & 2nd & 3rd & 4th & Entire \\ \cline { 2 - 7 } & Quarter & Quarter & Quarter & Quarter & Year \\ \hline \end{tabular} Raw-material purchases: Cash payments for purchases during the current quarter* Cash payments for purchases during the preceding quarter Total cash pmts. for raw-material purchases Direct labor: Frames produced (S and L) Direct-labor hours per frame Direct-labor hours to be used Rate per direct-labor hour Total cash payments for direct labor Manufacturing overhead: Indirect material Indirect labor Other Total cash payments for manufacturing OH Cash pmts. for selling and admin. expenses Total cash disbursements 70% of current quarter's purchases 30% of previous quarter's purchases 6. Summary cash budget