Natsu Dragneel manufactures one kind of scarf. These scarves are white with black stripes. Each scarf sells for $20 and requires: - Raw Materials are $0.60 per yard of fabric and each scarf requires 2 yards of fabric - 30 minutes of labor hours to manufacture the scarf (Labor cost is $8.00 per hour) Natsu Corp. has the following inventory policies: - Ending finished goods inventory should be 30% of next month's sales. - Ending inventory of direct raw materials should be 20% of next month's production need. As per the Marketing and Sales department of the Natsu Corp., sales are high in the 2 months before winter weather occurs. Therefore, the months of October and November are considered high-demand months. In an August budget meeting of the current year, the Sales Manager provided the following estimates of unit sales for the upcoming months (September current year - February next year): Variable manufacturing overhead is incurred at a rate of $0.40 per scarf manufactured. Annual fixed manufacturing overhead is estimated at $9,000 ( $750 per month). 9,000 scarves are anticipated to be produced each year. Fixed selling and administrative expenses are estimated at $820 per month and variable selling and administrative expenses are $0.75 per scarf sold. Of its sales each month, 60% of sales are cash sales and are collected in the month of the sale. Of the credit sales, 50% are collected in the month of the sale and 50% are collected in the month following the sale. The purchase of raw materials 60% is paid for in the month of the purchase and 40% are paid for in the month following the purchase. Material purchases in September were $800. Natsu had $12,200 cash on hand on October 1 . The company has a policy to maintain a monthly minimum cash balance of $10,000. The company may borrow any amount using the credit line provided by their-bank to pay for deficits and maintain the minimum required balance of cash. The company must borrow in increments of $1,000. Borrowings or any part of the borrowing may be paid off in the month there is excess cash available (Ignore interest on borrowings). Included in the fixed manufacturing overhead is deprecation. This amount is $280. Requirements: 1. Using the information provided above prepare the following budgets for the fourth quarter of the year (October - December) for Natsu Corporation. Include each month and quarter 4 (October - December) totals for each budget. a. Sales budget b. Production budget c. Raw Materials budget - ctieck figure for October =$818.64 d. Manufacturing overhead budget e. Budgeted Cost of Goods Sold (COGS) f. Selling and administrative expenses budget 2. Prepare Natsu's budgeted income statement for quarter 4 (October - December). 3. Prepare the following for Natsu for quarter 4 : a. Budgeted cash receipts/collection each month (including quarter 4 total) b. Budgeted cash payments each month (including quarter 4 total) c. Cash budget of Natsu for quarter 4 . 4. After completing requirements 1-3 above revise the budget spreadsheet in a new worksheet to include the following changes (you can copy and paste the current spreadsheet in a new worksheet to incorporate the following changes): Natsu Corp. is contemplating increasing the selling price of the Scarves by 10% during the high sales months of October and November. The management of Natsu believes that it would affect unit sales marginally only, thereby, reducing unit sales by 5% in those two months. How would these two changes affect the net income of Quarter 4 ? What is the net income from the original data and the net income on the revised data? What is the net income difference between the original and revised data? Based on the analysis, should Natsu Corp. temporarily increase the price of scarves during those high-demand months? Include a short explanation in the Excel file itself. (You must revise your Excel spreadsheet to determine the effect on the income statement. If you used your formulas correctly for requirements 1-3, you will not need to make a whole lot of changes to your spreadsheet). quarter 4 (October - December) totals for each budget. a. Sales budget b. Production budget c. Raw Materials budget - check figure for October =$818.64 d. Manufacturing overhead budget e. Budgeted Cost of Goods Sold (COGS) f. Selling and administrative expenses budget 2. Prepare Natsu's budgeted income statement for quarter 4 (October - December). 3. Prepare the following for Natsu for quarter 4 : a. Budgeted cash receipts/collection each month (including quarter 4 total) b. Budgeted cash payments each month (including quarter 4 total) c. Cash budget of Natsu for quarter 4 . 4. After completing requirements 1-3 above revise the budget spreadsheet in a new worksheet to include the following changes (you can copy and paste the current spreadsheet in a new worksheet to incorporate the following changes): Natsu Corp. is contemplafing increasing the selling price of the Scarves by 10% during the high sales months of October and November. The management of Natsu believes that it would affect unit sales marginally only, thereby, reducing unit sales by 5% in those two months. How would these two changes affect the net income of Quarter 4? What is the net income from the original data and the net income on the revised data? What is the net income difference between the original and revised data? Based on the analysis, should Natsu Corp. temporarily increase the price of scarves during those high-demand months? Include a short explanation in the Excel file itself. (You must revise your Excel spreadsheet to determine the effect on the income statement. If you used your formulas correctly for requirements 1-3, you will not need to make a whole lot of changes to your spreadsheet). 5. Save a copy of the Excel solution file after completing requirement 4 on your computer or flash drive. 6. Submit your Excel file you saved in requirement 5 above (with original and revised estimates) on the eLearning Dropbox (You should not revise your excel file to the original data, you should have two complete tabs within excel, the original data and the revised data). Note: Be sure to start this project early to allow time to ask questions