Natsu Dragneel manufactures one kind of scarf. These scarves are white with black stripes. Each scarf sells for $17 and requires:

Raw Materials are $8 per linear foot of fabric and each scarf requires 0.5 linear foot of direct materials

30 minutes of labor hours to manufacture the scarf (Labor cost is $16.00 per hour)

Natsu Corp. has the following inventory policies:

Ending finished goods inventory should be 70% of next month's sales. Ending finished goods as of September 30^th are 16,800 scarves.

Ending inventory of raw materials should be 20% of next month's production need. Ending raw materials inventory as of September 30^th is 4,375 linear feet of material and the budgeted raw materials inventory is 1,980 linear feet of material.

As per the Marketing and Sales department of the Natsu Corp., sales are high in the 2 months before winter weather occurs. Therefore, months of October and November are considered high demand months. In an August budget meeting of the current year, the Sales Manager provided following estimates of unit sales for the upcoming months (October current year - January next year):

September 20,000 scarves

October 21,000 scarves

November 19,000 scarves

December 20,000 scarves

January 24,000 scarves

Variable manufacturing overhead is incurred at a rate of $2.70 per direct labor hour. Annual fixed manufacturing overhead is estimated to be $240,000 ($20,000 per month). 240,000 scarves are anticipated to be produced each year.

Fixed selling and administrative expenses are estimated at $12,500 per month and variable selling and administrative expenses are sales commissions and are estimated at 10% of sales. These commissions are paid in the same month of the sale.

Of its sales each month, 30% of sales are cash sales and are collected in the month of the sale and 70% credit sales which are collected in the month following the sale.

Of the purchase of raw materials, all are made on credit and paid in the month following the purchase.

Also, all other operating costs are paid during the month incurred. During December, Natsu plans to pay $100,000 for a piece of equipment to replace an old equipment.

Natsu had $40,000 cash on hand on September 1. The company has a policy to maintain a monthly minimum cash balance of $40,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. Borrowings or any part of the borrowings may be paid off in the month there is excess cash available (Ignore interest on borrowings).

Requirements:

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) total for each budget.

Sales budget

Production budget

Raw Materials budget - check figure for October = $50,760

Manufacturing Overheads budget

Budgeted Cost of Goods Sold (COGS)

Selling and administrative expenses budget

Prepare Natsu's budgeted income statement for quarter 4 (October - December).

Prepare the following for Natsu for quarter 4:

Budgeted cash receipts/collection each month (including quarter 4 total)

Budgeted cash payments each month (including quarter 4 total)

Cash budget of Natsu for quarter 4.

After completing requirements 1-3 above revise the budget spreadsheet in a new worksheet to include following changes (you can copy and paste the current spreadsheet in a new worksheet to incorporate following changes):

Natsu Corp. is contemplating to increase 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 net income from the original data and net income on the revised data? What is the difference in net income between the original data and the revised data? Based on the analysis, should Natsu Corp. increase the price of scarves temporarily during those high demand months? Include a short explanation in Excel file itself.

(You need to revise your Excel spreadsheet to determine the effect on income statement. If you used your formulas correctly for requirements 1-3, you will not need to make whole lot of changes to your spreadsheet).

Given Data: Sales price per unit Linear feet of raw material needed per scarf Raw material cost per foot Labor hours per scarf Labor rate per hour Ending Finished goods inventory policy Ending inventory policy for raw materials Variable mfg OH rate per scarf produced Fixed mfg OH per month Fixed selling and adm expenses per month Variable selling and admin expenses per scarf sold Annual fixed manufacturing overhead Annual production volume in units Sales Budget Quarter 4 Budgeted September October November December Quarter 4 January Sales (in units) x sales price per unitProduction Budget September October November December Quarter 4 January Budgeted Sales (in units) Add: Required Ending Inventory of Fin. Goods Less: Bud. Beginning Inventory of Fin. Goods Budgeted Production (in units) Raw Materials Purchase Budget September October November December Quarter 4 January Check figure October Bud. Production (in units) Total Purchase = $50,760 Raw Materials required per scarf (linear feet) Raw Materials needed for Production (linear feet) Add: Ending inventory of Raw Materials (linear feet) Less: Beg. Inventory of Raw Materials (linear feet) Budgeted Raw Materials Purchase (in linear feet) Raw Materials Cost per foot Total Budgeted Raw Materials Purchase ($) Direct Labor Budget October November December Quarter 4 Bud. Production (in units) DL hours per scarf Bud. DL Hours needed for Production DL rate per hour ($) Bud. DL Cost ($)MOH Budget Mfg. Cost per unit October November December Quarter 4 Raw Materials $0.00 DL $0.00 Bud. Production (in units) Var.MOH $0.00 Var. MOH per scarf Bud. Total Var. MOH ($) Fixed MOH #DIV/O! Bud. Total Fixed MOH ($) Total Mfg. Cost per unit #DIV/O! Bud. TOTAL MOH ($) COGS Budget October November December Quarter 4 Budgeted Sales * Mfg. cost per unit Bud. COGS Selling and Adm. Expense Budget October November December Quarter 4 Budgeted Sales (in units) Variable S&A Expense per unit Budgeted Variable S&A Expense Total Budgeted Fixed S&A Expense Total Budgeted Total S&A ExpenseBudgeted Income Statement October November December Quarter 4 Budgeted Sales Revenue Less: Budgeted COGS Gross Margin Less: Bud. S&A Expenses Net Operating Income (Loss) Budgeted Cash Receipts/Collection September October November December Quarter 4 Budgeted Sales ($) Current month's sales collected in cash Prior month's credit sales collected Budgeted Total Cash collection/receipt Budgeted Cash Payments September October November December Quarter 4 Budgeted Raw Materials Purchase ($) Bud. Cash payment toward prior month purchase Bud. Total Cash payment toward Oakwood purchase Bud. Cash payment towards DL cost Budgeted MOH costs Budgeted S&A Expenses Budgeted Capital Expenditure Budgeted Total Cash PaymentsCash Budget September October November December Quarter 4 Beginning Cash Balance Budgeted Cash Collection/ Receipts Less: Budgeted Cash Payments Preliminary Cash Balance Borrowings and Repayments: Borrowing Repayment of Principal Ending Cash BalanceA B C D E F G H Given Data: W Sales price per unit $17.00 4 Linear feet of raw material needed per scarf 0.5 5 Raw material cost per foot S 8.00 6 Labor hours per scarf 0.5 7 Labor rate per hour 16.00 8 9 Ending Finished goods inventory policy 70% 10 Ending inventory policy for raw materials 20% 11 12 Variable mfg OH rate per scarf produced 2.70 13 Fixed mfg OH per month $ 20,000.00 14 Fixed selling and adm expenses per month $ 12,500.00 15 Variable selling and admin expenses per scarf sold 10% was in $, is this a typo? 16 Annual fixed manufacturing overhead $ 240,000.00 17 Annual production volume in units 240,000.00 18 19 20 Sales Budget Quarter 4 21 Budgeted September October November December Quarter 4 January Sales (in units) 20,000.00 21,000.00 19,000.00 20,000.00 24,000.00 23 x sales price per unit $17.00 $17.00 $17.00 $17.00 $0.00 $17.00 24 Total Sales Revenue ($) $340,000.00 $357,000.00 $323,000.00 $340,000.00 $408,000.00 25A B C D E F G H 26 Production Budget 27 September October November December Quarter 4 January 28 Budgeted Sales (in units) 20,000.00 21,000.00 19,000.00 20,000.00 24,000.00 29 Add: Required Ending Inventory of Fin. Goods 16,800.00 13,300.00 14,000.00 16,800.00 30 Less: Bud. Beginning Inventory of Fin. Goods (14,000.00) (14,700.00) (13,300.00) (14,000.00) 31 Budgeted Production (in units) 22,800.00 19,600.00 19,700.00 22,800.00 32 33 Raw Materials Purchase Budget 34 September October November December Quarter 4 January Check figure October 35 3ud. Production (in units) 22,800.00 19,600.00 19,700.00 22,800.00 24,000.00 Total Purchase = $50,760 36 Raw Materials required per scarf (linear feet) 0.50 0.50 0.50 0.50 0.50 37 Raw Materials needed for Production (linear feet) 11,400.00 9,800.00 9,850.00 11,400.00 12,000.00 38 Add: Ending inventory of Raw Materials (linear feet) 4,375.00 3,940.00 4,560.00 4,800.00 39 Less: Beg. Inventory of Raw Materials (linear feet) (5,700.00) (4,900.00) (4,925.00) (5,700.00) 40 Budgeted Raw Materials Purchase (in linear feet) 10,075.00 8,840.00 9,485.00 10,500.00 41 Raw Materials Cost per foot $ 8.00 $ 8.00 $ 8.00 $ 8.00 42 Total Budgeted Raw Materials Purchase ($) 80600 70720 75880 84000 43 + 44