Please HELP with requirement A!!! Thanks in advance!!!

A \begin{tabular}{|l|l} A & B \end{tabular} C D 2 Requirement A 3 Complete the Master Budget (ALL parts plus schedules) in Excel. 4 The specific budget information/details are provided in a separate word document. 5 To complete your Comprehensive Project 2 assignment, use the master budget example template. You may use the template provided in this workbook or the workbook used for the example completed in class. Using the example from class enables you to prepare the budget in excel ensuring that you have formulas set up correctly and correctly calculated values. Be sure to change all appropriate information if you use the workbook 6 from the class example. Your Comprehensive Project 2 Assignment will look exactly like the Royal Co. example with the exception of the budget specific standard numbers. You will simply need to change the appropriate numbers, time periods and company headings. These budget specific standard numbers and any other budget requirements will be provided in a 8 senarate document (FA)? Comnrehensive Prohlem v" x") There are some parts of the budget that will require you to make some decisions and/or understand how it relates to other/prior parts. The Cash Budget will require you to determine whether the company will need to borrow money in order to meet cash requirements. If you borrow money, you may need to pay some or all back with interest and you will have to calculate that interest. You will need to understand how to calculate or where to get the numbers for the income statement and balance sheet. Read/Review all the information provided as well as chapter 22. This is not an assignment that can be successfully completed in a couple of hours the night it is due. 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? Part II (Chapter 25, 50 pts) Requirement C This question is assignment version specific. Please refer to the assignment document for further details. 32 Requirement D This question is assignment version specific. Please refer to the assignment document 33 for further details. Augustus Co. Master Budget Input Section Sales Budget Expected unit sales in the first quarter \begin{tabular}{|l|r|} \hline Januray & 8,400 \\ \hline February & 6,400 \\ \hline March & 8,400 \\ \hline April & 5,400 \\ \hline May & 6,400 \\ \hline \end{tabular} Selling price \begin{tabular}{|ll|} \hline$54.00 \\ \hline \end{tabular} Cash Collections Beginning Balance Collected in month of sale Collected in the month following sale Uncollectible amount \begin{tabular}{|r|} \hline 0 \\ \hline 60%/ \\ \hline 35% \\ \hline 5%,6 \\ \hline \end{tabular} Manufacturing Overhead Budget \begin{tabular}{l|ll|l} Variable Manufacturing Overhead Rate & $3r.20 & per hour \\ \hline Fixed Manufacturing Overtead Rate & $83,000.00 & per month \\ \hline Depreciation & $15,000.00 & per month \\ \hline \end{tabular} Production Budget Selling and Adminlstrative Expense Budget Desired ending inventory Beginning Inventory \begin{tabular}{|ll|} \hline & 20% \\ \hline$ & \\ \hline & \\ \hline & 7 \\ \hline & 10%6 \\ \hline$ & 0.56 \\ \hline & 0 \\ \hline \end{tabular} Raw Materials Budget Raw materials per unit Cost per pound Desired ending inventory Beginning inventory Cash payments Beginning balance Paid in month of purchase Paid in the month following purchase \begin{tabular}{|r|} \hline 0 \\ \hline 60%6 \\ \hline 40% \\ \hline \end{tabular} Direct Labor Budget Direct labor hours per unit Direct labor cost per hour \begin{tabular}{|lr|} \hline & 0.74 \\ \hline$ & 14.00 \\ \hline \end{tabular} \begin{tabular}{l|r|r|} \hline Cash Budget & 18,000 & \\ \hline Beginning balance & 34,000 & \\ \hline Dividends & 114,000 & \\ \hline Purchase of equipment & 112,000 & \\ \hline Purchase of equipment & 18% & \\ \hline Interest Rate & & \end{tabular} Balances Exist as of Januray 1st Land Equipement CommonStock 300,000 162,000 480,000 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} A B C CASH BUDGET Cash Budget Cash balance, beginning Add receipts: \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline \end{tabular} Cash collections Total cash available \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline \end{tabular} Less disbursements: Direct materials Direct labor Manufacturing overhead Selling \& administrative Equipment purchases Dividends Total disbursements 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} Cash balance, ending Interest BUDGETED INCOME STATEMENT \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} 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} A \begin{tabular}{|l|l} A & B \end{tabular} C D 2 Requirement A 3 Complete the Master Budget (ALL parts plus schedules) in Excel. 4 The specific budget information/details are provided in a separate word document. 5 To complete your Comprehensive Project 2 assignment, use the master budget example template. You may use the template provided in this workbook or the workbook used for the example completed in class. Using the example from class enables you to prepare the budget in excel ensuring that you have formulas set up correctly and correctly calculated values. Be sure to change all appropriate information if you use the workbook 6 from the class example. Your Comprehensive Project 2 Assignment will look exactly like the Royal Co. example with the exception of the budget specific standard numbers. You will simply need to change the appropriate numbers, time periods and company headings. These budget specific standard numbers and any other budget requirements will be provided in a 8 senarate document (FA)? Comnrehensive Prohlem v" x") There are some parts of the budget that will require you to make some decisions and/or understand how it relates to other/prior parts. The Cash Budget will require you to determine whether the company will need to borrow money in order to meet cash requirements. If you borrow money, you may need to pay some or all back with interest and you will have to calculate that interest. You will need to understand how to calculate or where to get the numbers for the income statement and balance sheet. Read/Review all the information provided as well as chapter 22. This is not an assignment that can be successfully completed in a couple of hours the night it is due. 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? Part II (Chapter 25, 50 pts) Requirement C This question is assignment version specific. Please refer to the assignment document for further details. 32 Requirement D This question is assignment version specific. Please refer to the assignment document 33 for further details. Augustus Co. Master Budget Input Section Sales Budget Expected unit sales in the first quarter \begin{tabular}{|l|r|} \hline Januray & 8,400 \\ \hline February & 6,400 \\ \hline March & 8,400 \\ \hline April & 5,400 \\ \hline May & 6,400 \\ \hline \end{tabular} Selling price \begin{tabular}{|ll|} \hline$54.00 \\ \hline \end{tabular} Cash Collections Beginning Balance Collected in month of sale Collected in the month following sale Uncollectible amount \begin{tabular}{|r|} \hline 0 \\ \hline 60%/ \\ \hline 35% \\ \hline 5%,6 \\ \hline \end{tabular} Manufacturing Overhead Budget \begin{tabular}{l|ll|l} Variable Manufacturing Overhead Rate & $3r.20 & per hour \\ \hline Fixed Manufacturing Overtead Rate & $83,000.00 & per month \\ \hline Depreciation & $15,000.00 & per month \\ \hline \end{tabular} Production Budget Selling and Adminlstrative Expense Budget Desired ending inventory Beginning Inventory \begin{tabular}{|ll|} \hline & 20% \\ \hline$ & \\ \hline & \\ \hline & 7 \\ \hline & 10%6 \\ \hline$ & 0.56 \\ \hline & 0 \\ \hline \end{tabular} Raw Materials Budget Raw materials per unit Cost per pound Desired ending inventory Beginning inventory Cash payments Beginning balance Paid in month of purchase Paid in the month following purchase \begin{tabular}{|r|} \hline 0 \\ \hline 60%6 \\ \hline 40% \\ \hline \end{tabular} Direct Labor Budget Direct labor hours per unit Direct labor cost per hour \begin{tabular}{|lr|} \hline & 0.74 \\ \hline$ & 14.00 \\ \hline \end{tabular} \begin{tabular}{l|r|r|} \hline Cash Budget & 18,000 & \\ \hline Beginning balance & 34,000 & \\ \hline Dividends & 114,000 & \\ \hline Purchase of equipment & 112,000 & \\ \hline Purchase of equipment & 18% & \\ \hline Interest Rate & & \end{tabular} Balances Exist as of Januray 1st Land Equipement CommonStock 300,000 162,000 480,000 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} A B C CASH BUDGET Cash Budget Cash balance, beginning Add receipts: \begin{tabular}{|l|l|l|l|} \hline & & & Quarter \\ \hline & & & \\ \hline \end{tabular} Cash collections Total cash available \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline \end{tabular} Less disbursements: Direct materials Direct labor Manufacturing overhead Selling \& administrative Equipment purchases Dividends Total disbursements 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} Cash balance, ending Interest BUDGETED INCOME STATEMENT \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} 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}