help me to make a required 4 i am done with required 1-3 but for the 4 i dont know please help me with the work solution
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. Also, all other operating costs are paid during the month incurred. During October, Natsu plans to pay $15,000 for a piece of equipment to replace a piece of old equipment. Natsu had $12,200 cash on hand on October 1 . The company has a policy to maintainamonthluminimumesh balance of Variable manufacturing overhead is incurred at a rate of $0.40 per searf 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. Also, all other operating costs are paid during the month incurred. During October, Natsu plans to pay $15,000 for a piece of equipment to replace a piece of old equipment. 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 borrowings 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 - 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 eash 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 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 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). 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. Also, all other operating costs are paid during the month incurred. During October, Natsu plans to pay $15,000 for a piece of equipment to replace a piece of old equipment. Natsu had $12,200 cash on hand on October 1 . The company has a policy to maintainamonthluminimumesh balance of Variable manufacturing overhead is incurred at a rate of $0.40 per searf 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. Also, all other operating costs are paid during the month incurred. During October, Natsu plans to pay $15,000 for a piece of equipment to replace a piece of old equipment. 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 borrowings 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 - 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 eash 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 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 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)
