Instructions Please follow the materials under Project" and build a master budget on an Excel spreadsheet for requirement 1. Modify the master budget in another sheet for requirement 2. Prepare a report for requirement 3. 3. Total budgeted variable overhead costs for the 2021 year (at a level of sales estimated in Item 1 above) follow: Indirect materials Indirect labour Employee benefits Inspections Utilities Total $26.986 55,520 83.280 31,500 41.640 $238.926 2 Variable overhead is applied to components using a predetermined overhead rate based on annual direct labour hours. All variable overhead items are paid for in the quarter incurred. 4. The annual budget for fixed manufacturing overhead items follows: Supervisory salaries $186,200 Property taxes 26,000 Insurance 28.800 Maintenance 46,000 Utilities 33,400 Engineering Time 41.850 Depreciation 96,000 Total $458,250 All fixed overheads are paid evenly each quarter except for property taxes which are paid for in the third quarter of the year. Fixed overhead is applied to production using a predetermined overhead rate based on the estimated annual number of units produced. 5. Variable selling and administration expenses include commissions and other administrative expenses. Commissions are budgeted at 5% of sales dollars for the quarter. 80% of these commissions are paid in the quarter they incurred, while 20% are paid in the following quarter. Other variable administration costs are $2.00 per unit. These costs are paid for in the quarter they incurred. Annual fixed selling and administration expenses are as follows: Sales salaries Administration salaries Travel Insurance Utilities Depreciation Other Total $152,000 100,000 24,000 3,400 2.800 12,000 2800 $297,000 Fixed selling and administration expenses are paid evenly over the four quarters of the year. 6. CMRNA makes quarterly income tax installments based on the projected taxable income for the year. The company is subject to a 30% tax rate. For the master budget, CMRNA assumes tax expenses incurring for the year 2021 are paid in cash evenly over the four quarters of the year 2021. Required: 1. Prepare a master budget for CMRNA for each quarter of 2021 and for the year in total. The following component budgets must be included: a. Beginning balance sheet (classified as in Item 8, Project) b. Sales budget c. Schedule of receipts d. Production budget Direct materials purchases budget f. Schedule of disbursements for materials g. Direct labour budget h. Overhead budget (be sure to show disbursements for variable and fixed overheads, in addition to applied variable and fixed overhead expenses). i. Selling and administrative budget (be sure to show disbursements for selling and administrative expenses). j. Cash budget Prepare the following for the year, 2021, in total. k. Cost of goods manufactured budget 1. Cost of goods sold budget m. Pro forma income statement (using absorption costing) n. Pro forma classified balance sheet Cash budget and pro forma income statement are completed at the same time when you build a formula to account for tax expenses, and a set of formulas to account for cash outflow in the cash budget. Pro forma classified balance sheet are completed last. competitive advantages to firms maximizing profits. Describe the business environments that reward firms maximizing employee benefits and compensation. Discuss potential trade-offs (benefits and risks) in the strategic pricing and compensation in an industry or a business of your choice (e.g., services, media, luxury goods, retail, software engineering, manufacturing, real estate development and investment, bio-tech, fin-tech, advertisement, higher education, mining, oil and gas, agriculture, social work, professional services, etc.) Instructions for preparing Excel spreadsheets: 1. Develop your entire master budget in one worksheet; i.e., present your beginning balance sheet first, then present your sales budget below it and the next budget below, etc. All of the given information is entered in at the top of the workbook and the entire budget for each alternative is to be in a single workbook. 2. When you have finalized the master budget, start work on the sensitivity (what-if) analyses for part 2. First, copy the master budget onto a new worksheet (i.e., a new tab). Then make the necessary changes on the copied worksheet. If you have fully programmed the first worksheet, the results of your changes will be immediately calculated. Label the worksheet tabs and the title of the worksheet so that it is clear what you are calculating. (Use a header to label the worksheet so that the content of the worksheet is clear.) 3. Part of your mark will depend on how well you link the parts of your spreadsheets. 4. You must upload an electronic copy of your budget file (.xlsx) and report (.docx or .pdf) on Canvas