Part I (Chapter 22, 150 pts) Requirement A Complete the Master Budget (ALL parts plus schedules) in Excel. The specific budget information/details are provided in a separate word document. 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 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 separate document. (FA22 Comprehensive Problem 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. Requirement D This question is assignment version specific. Please refer to the assignment document for further details.Master Budget Input Section Sales Budget Expected unit sales in the first quarter Selling price Cash Collections Beginning Balance Collected in month of sale Collected in the month following sale Uncollectible amount Production Budget % of next month's Desired ending inventory sales Beginning Inventory units Raw Materials Budget Raw materials per unit pounds Cost per pound Desired ending inventory Beginning inventory pounds Cash payments Beginning balance Paid in month of purchase Paid in the month following purchaseCash payments Beginning balance Paid in month of purchase Paid in the month following purchase Direct Labor Budget Direct labor hours per unit Direct labor cost per hour Manufacturing Overhead Budget Variable Manufacturing Overhead Rate per hour Fixed Manufacturing Overhead Rate per month Depreciation per month Selling and Administrative Expense Budget Variable Selling & Admin Rate per unit Fixed Selling & Admin Rate per month Depreciation per month Cash Budget Beginning balance Dividends Purchase of equipment Purchase of equipment Interest RateI Master Budget I SALES BUDGET Budgeted sales (units) Selling price per unit Total sales 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 units units units units units Quarter Quarter Quarter DIRECT MATERIALS BUDGET Quarter 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 SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIAL Quarter Accounts payable beginning balance April purchases: April May May purchases: May June June purchases: June Total cash disbursements for materials DIRECT LABOR BUDGET Required production Quarter Direct labor-hours per unit Total direct laborhours needed Direct labor cost per hour Total direct labor cost MANUFACTURING OVERHEAD BUDGET Budgeted direct laborhours Variable manufacturing overhead rate Variable manufacturing overhead Fixed manufacturing overhead Total manufacturing overhead Less depreciation Cash disbursements for manufacturing overhead ENDING FINISHED GOODS INVENTORY BUDGET Computation of absorption unit product cost: Direct materials Direct labor Manufacturing overhead Unit product cost Predetermined Overhead Rate = Budgeted ending finished goods inventory: Ending finished goods inventory in units Unit product cost Ending finished goods inventory in dollars Quantity pounds per pound hours per hour hours per hour* Total manufacturing overhead = Total direct labor hours E Cost Total SELLING AND ADMINISTRATIVE EXPENSE BUDGET : Budgeted sales in units i Variable selling and administrative expense per unit ' Variable selling and administrative expense ; Fixed selling and administrative expense i Total selling and administrative expense i Less depreciation Cash disbursements for selling and administrative expenses : CASH BUDGET i Cash balance, beginning i Add receipts: Cash collections Total cash available Less disbursements: Direct materials Direct labor Manufacturing overhead Selling & administrative Equipment purchases i Dividends Total disbursements Excess (deficiency) of cash available over : disbursements : Financing: Borrowings Repayments lnterest* Cash Budget Quarter Financing: Borrowings Repayments Interest* Total financing Cash balance, ending I nterest BUDGETED INCOME STATEMENT Budgeted Income Statement Net sales Computation of net sales Cost of goods sold Sales Gross margin Less uncollectible amounts Selling & administrative expenses Net sales Net operating income Interest expense Net income Computation of cost of goods sold Budgeted sales (units) Unit product cost Cost of goods sold BEGINNING BALANCE SHEET (REQUIRED) (Based on information given, beginning balances and calculations) Balance Sheet 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 Budgeted 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 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: New Lost per unit Decision (Accept or Do Not Accept)Requirement 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 justify your decision. Reference the DATA from your master budget. Make Buy Units Decision (Accept or Do Not Accept)