Please help I don't know what to do. Thanks in Advance!!!
Required: Part I (Chapter 22) (150 points) A. Complete the Master Budget (ALL parts plus schedules) in Excel. AUv3 B. Answer the following questions: 1. What are the budgeted sales for February? 2. What are the expected cash collections for February? 3. What is the accounts receivable balance at the end of March? 4. What is the estimated cost of raw materials purchases for February? 5. What is the estimated accounts payable balance at the end of March? 6. What is the estimated finished goods inventory balance at the end of March? 7 What is the estimated cost of goods sold and gross margin for March? 8. What is the estimated total selling and administrative expense for February? 9. What is the estimated net operating income for March? 10. What is the estimated retained earnings balance for March? Part II (Chapter 25) (50 points) I have provided templates based on my calculations and my answers. You may use them or create your own. Show all work/calculations to justify your decision. Label your work appropriately. If I can't understand it, I can't grade it. C. Assume that Augustus expects to produce and sell 90,000 units during the current year. One of Augustus's sales representatives has found a new customer that is willing to buy 8,000 additional units for a price of $43 per unit. If they accept the customer's offer, it will decrease unit sales to regular customers by 5,000 units. Should they accept this special order? Show all work/calculations to justify your decision. Label your work appropriately. If I can't understand it, I can't grade it. D. Assume that Augustus expects to produce and sell 23,000 units during the current quarter. A supplier has offered to manufacture and deliver 23,000 units for a price of $25 per unit. Should Augustus accept this offer? How much will profit's increase or decrease? Show all work/calculations to iustifv vour decision. Label your work appropriately. If I can't und SCHEDULE OF EXPECTED CASH COLLECTIONS Accounts receivable beginning balance April sales April May May sales May June June sales June Total cash collections PRODUCTION BUDGET Budgeted sales Add desired ending inventory Total needs Less beginning inventory Required production \begin{tabular}{|l|l|l|l|} \hline & & & \multicolumn{1}{|c|}{ Quarter } \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} DIRECT MATERIALS BUDGET Required production in units Raw materials per unit (pounds) Production needs (pounds) Add desired ending inventory (pounds) Total needs (pounds) Less beginning inventory (pounds) Raw materials to be purchased (pounds) Cost of raw materials to be purchased at $0.40 per pound \begin{tabular}{|l|l|l|l|l|l|} \hline & & & & Quarter & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|l|l|l|} \hline & & \multicolumn{2}{|c|}{ Quarter } & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & \\ \hline & & & & \\ \hline & & & & \\ \hline & & & & \\ \hline \end{tabular} Ready in Accessibility: Investigate \begin{tabular}{cc} A & B \\ \hline SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL \end{tabular} Accounts payable beginning balance April purchases: April May May purchases: May June June purchases: June Total cash disbursements for materials \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} DIRECT LABOR BUDGET Required production Direct labor-hours per unit Total direct labor-hours needed Direct labor cost per hour Total direct labor cost \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} MANUFACTURING OVERHEAD BUDGET Budgeted direct labor-hours Variable manufacturing overhead rate Variable manufacturing overhead Fixed manufacturing overhead Total manufacturing overhead Less depreciation Cash disbursements for manufacturing overhead \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} \begin{tabular}{c|c} \hline & A \\ 84 & ENDING FINISHED GOODS INVENTORY BUDGET \end{tabular} Computation of absorption unit product cost: 97 Budgeted ending finished goods inventory: 98 Ending finished goods inventory in units Unit product cost Ending finished goods inventory in dollars SELLING AND ADMINISTRATIVE EXPENSE BUDGET Budgeted sales in units Variable selling and administrative expense per unit Variable selling and administrative expense Fixed selling and administrative expense Total selling and administrative expense Less depreciation Cash disbursements for selling and administrative expenses \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & Quarter \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} Cash balance, beginning Cash Budget Add receipts: \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline \end{tabular} Cash collections Total cash available Less disbursements: \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline \end{tabular} Excess (deficiency) of cash available over disbursements Financing: \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} Borrowings Repayments Interest Total financing Cash balance, ending Interest \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} BUDGETED INCOME STATEMENT Budgeted Income Statement \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline \end{tabular} A B C D E BEGINNING BALANCE SHEET (REQUIRED) (Based on information given, beginning balances and calculations) Balance Sheet Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Plant and equipment: Land Buildings and equipment Accumulated depreciation Total assets Liabilities: Accounts payable Stockholders' equity: Common stock Retained earnings Total liabilities and stockholders' equity \begin{tabular}{|l|l|} \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Plant and equipment: Land Buildings and equipment Accumulated depreciation Total assets Liabilities: Accounts payable Stockholders' equity: Common stock Retained earnings Total liabilities and stockholders' equity \begin{tabular}{|l|l} \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} Requirement B 1. What are the budgeted sales for February? 2. What are the expected cash collections for February? 3. What is the accounts receivable balance at the end of March? 4. What is the estimated cost of raw materials purchases for February? 5. What is the estimated accounts payable balance at the end of March? 6. What is the estimated finished goods inventory balance at the end of March? 7 What is the estimated cost of goods sold and gross margin for March? 8. What is the estimated total selling and administrative expense for February? 9. What is the estimated net operating income for March? 10. What is the estimated retained earnings balance for March? Below is MY template for analyzing these business decisions. You may use it or create your own. If you choose to use my template but use the master budget workbook created in the class example exercise, simply copy this page into a worksheet in your class example workbook. Requirement C Assume that Augustus expects to produce and sell 90,000 units during the current year. One of Augustus's sales representatives has found a new customer that is willing to buy 8,000 additional units for a price of $43 per unit. If they accept the customer's offer, it will decrease unit sales to regular customers by 5,000 units. Should they accept this special order? Show all work/calculations to justify your decision. Reference the DATA from your master budget. The profit impact is computed as follows: \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \multicolumn{1}{c|}{ New } & Lost \\ \hline & & \\ \hline & \\ \hline & \\ \hline & & \\ \hline per unit \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & \\ \hline \end{tabular} \begin{tabular}{|l|} \hline Decision (Accept or Do Not Accept) \\ \hline \end{tabular} Assume that Augustus expects to produce and sell 23,000 units during the current quarter. A supplier has offered to manufacture and deliver 23,000 units for a price of $25 per unit. Should Augustus accept this offer? How much will profit's increase or decrease? Show all work/calculations to justify your decision. Required: Part I (Chapter 22) (150 points) A. Complete the Master Budget (ALL parts plus schedules) in Excel. AUv3 B. Answer the following questions: 1. What are the budgeted sales for February? 2. What are the expected cash collections for February? 3. What is the accounts receivable balance at the end of March? 4. What is the estimated cost of raw materials purchases for February? 5. What is the estimated accounts payable balance at the end of March? 6. What is the estimated finished goods inventory balance at the end of March? 7 What is the estimated cost of goods sold and gross margin for March? 8. What is the estimated total selling and administrative expense for February? 9. What is the estimated net operating income for March? 10. What is the estimated retained earnings balance for March? Part II (Chapter 25) (50 points) I have provided templates based on my calculations and my answers. You may use them or create your own. Show all work/calculations to justify your decision. Label your work appropriately. If I can't understand it, I can't grade it. C. Assume that Augustus expects to produce and sell 90,000 units during the current year. One of Augustus's sales representatives has found a new customer that is willing to buy 8,000 additional units for a price of $43 per unit. If they accept the customer's offer, it will decrease unit sales to regular customers by 5,000 units. Should they accept this special order? Show all work/calculations to justify your decision. Label your work appropriately. If I can't understand it, I can't grade it. D. Assume that Augustus expects to produce and sell 23,000 units during the current quarter. A supplier has offered to manufacture and deliver 23,000 units for a price of $25 per unit. Should Augustus accept this offer? How much will profit's increase or decrease? Show all work/calculations to iustifv vour decision. Label your work appropriately. If I can't und SCHEDULE OF EXPECTED CASH COLLECTIONS Accounts receivable beginning balance April sales April May May sales May June June sales June Total cash collections PRODUCTION BUDGET Budgeted sales Add desired ending inventory Total needs Less beginning inventory Required production \begin{tabular}{|l|l|l|l|} \hline & & & \multicolumn{1}{|c|}{ Quarter } \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} DIRECT MATERIALS BUDGET Required production in units Raw materials per unit (pounds) Production needs (pounds) Add desired ending inventory (pounds) Total needs (pounds) Less beginning inventory (pounds) Raw materials to be purchased (pounds) Cost of raw materials to be purchased at $0.40 per pound \begin{tabular}{|l|l|l|l|l|l|} \hline & & & & Quarter & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|l|l|l|} \hline & & \multicolumn{2}{|c|}{ Quarter } & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & & \\ \hline & & & & \\ \hline & & & & \\ \hline & & & & \\ \hline & & & & \\ \hline \end{tabular} Ready in Accessibility: Investigate \begin{tabular}{cc} A & B \\ \hline SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL \end{tabular} Accounts payable beginning balance April purchases: April May May purchases: May June June purchases: June Total cash disbursements for materials \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} DIRECT LABOR BUDGET Required production Direct labor-hours per unit Total direct labor-hours needed Direct labor cost per hour Total direct labor cost \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} MANUFACTURING OVERHEAD BUDGET Budgeted direct labor-hours Variable manufacturing overhead rate Variable manufacturing overhead Fixed manufacturing overhead Total manufacturing overhead Less depreciation Cash disbursements for manufacturing overhead \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} \begin{tabular}{c|c} \hline & A \\ 84 & ENDING FINISHED GOODS INVENTORY BUDGET \end{tabular} Computation of absorption unit product cost: 97 Budgeted ending finished goods inventory: 98 Ending finished goods inventory in units Unit product cost Ending finished goods inventory in dollars SELLING AND ADMINISTRATIVE EXPENSE BUDGET Budgeted sales in units Variable selling and administrative expense per unit Variable selling and administrative expense Fixed selling and administrative expense Total selling and administrative expense Less depreciation Cash disbursements for selling and administrative expenses \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & Quarter \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} Cash balance, beginning Cash Budget Add receipts: \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline \end{tabular} Cash collections Total cash available Less disbursements: \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline \end{tabular} Excess (deficiency) of cash available over disbursements Financing: \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} Borrowings Repayments Interest Total financing Cash balance, ending Interest \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} BUDGETED INCOME STATEMENT Budgeted Income Statement \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline \end{tabular} A B C D E BEGINNING BALANCE SHEET (REQUIRED) (Based on information given, beginning balances and calculations) Balance Sheet Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Plant and equipment: Land Buildings and equipment Accumulated depreciation Total assets Liabilities: Accounts payable Stockholders' equity: Common stock Retained earnings Total liabilities and stockholders' equity \begin{tabular}{|l|l|} \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Plant and equipment: Land Buildings and equipment Accumulated depreciation Total assets Liabilities: Accounts payable Stockholders' equity: Common stock Retained earnings Total liabilities and stockholders' equity \begin{tabular}{|l|l} \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} Requirement B 1. What are the budgeted sales for February? 2. What are the expected cash collections for February? 3. What is the accounts receivable balance at the end of March? 4. What is the estimated cost of raw materials purchases for February? 5. What is the estimated accounts payable balance at the end of March? 6. What is the estimated finished goods inventory balance at the end of March? 7 What is the estimated cost of goods sold and gross margin for March? 8. What is the estimated total selling and administrative expense for February? 9. What is the estimated net operating income for March? 10. What is the estimated retained earnings balance for March? Below is MY template for analyzing these business decisions. You may use it or create your own. If you choose to use my template but use the master budget workbook created in the class example exercise, simply copy this page into a worksheet in your class example workbook. Requirement C Assume that Augustus expects to produce and sell 90,000 units during the current year. One of Augustus's sales representatives has found a new customer that is willing to buy 8,000 additional units for a price of $43 per unit. If they accept the customer's offer, it will decrease unit sales to regular customers by 5,000 units. Should they accept this special order? Show all work/calculations to justify your decision. Reference the DATA from your master budget. The profit impact is computed as follows: \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \multicolumn{1}{c|}{ New } & Lost \\ \hline & & \\ \hline & \\ \hline & \\ \hline & & \\ \hline per unit \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & \\ \hline \end{tabular} \begin{tabular}{|l|} \hline Decision (Accept or Do Not Accept) \\ \hline \end{tabular} Assume that Augustus expects to produce and sell 23,000 units during the current quarter. A supplier has offered to manufacture and deliver 23,000 units for a price of $25 per unit. Should Augustus accept this offer? How much will profit's increase or decrease? Show all work/calculations to justify your decision
