The question is in the files. Solving it equals save my life.
Excel Project on budgeting Patriot Manufacting,Inc, produces blowout preventers used in drilling. The preventers are sold to various gas and oil drilling companies throughout the United States. Projected sales in units for the coming months are: January 25000 February 3500 March 20000 April 20000 The following data pertain to production polices and manufacturing specifications followed patriot. a. Finished goods inventory on January 1 is 15000 units. The desired ending inventory for each month is 60 percent of the next month's sales. b. The blowout preventers require two different types of materials. Data on materials used are: Direct Material Perunit Usage Unit Cost Part 714 5 $4.00 Part 502 3 $3.00 Inventory policy dictates that sufficient materials be on hand at the beginning of the month to produce 50% of that month's estimated sales. This is exactly the amount of materials in stock on January 1. c. The direct labor used per unit of output is two hours. The average direct labor cost per hour is $15. d. Overhead each month is estimated using a flexible budget formula. Activity is measured in direct labor hours. Fixed Cost Component Variable per unit component Supplies $ 0 $ 1.00 Power $ 0 $ 0.20 Maintenance $ 28,000 $ 1.10 Supervision $ 14,000 n/a Depreciation $ 100,000 n/a Taxes $ 7,000 n/a Other $ 56,000 $ 1.60 e. Monthly selling and administrative expenses are also estimated using a flexible budget formula. Activity is measured in units sold. Fixed Costs Variable Costs Salaries $ 30,000 n/a Commissions 0 $ 0.75 Depreciation $5,000 n/a Shipping 0 $ 2.60 Other $10,000 $ 0.40 f. The unit selling price of the blowout preventer is $90. g. During February, the company plans to purchase land for future expansion. The land costs $110,000 and Patriot management would like to play cash. h. Dividends are paid to shareholders in March. The company pays $2 per share. There are 20,000 total shares outstanding. i. j. All sales and purchases are for cash. Cash balance on January 1 equals $162,900.If the firm has a cash shortage at the end of a month, they can borrow any needed cash from their bank under a line of credit. The bank agreement states that interest will be paid every month based on the loan balance at the beginning of the month. The loan agreement specifies that the company will maintain a cash balance of at least $50,000. The company will pay off any loan balances as funds are available. Required 1. Using Excel, prepare the following for the first three quarters(nine months) for Patriot Manufacturing, Inc.: Sales budget Production budget Direct materials purchase budgets( you will need one of each part) Direct labor budget Overhead budget Selling and administrative expense budget Ending finished goods inventory budget Cost of goods sold budget Budgeted income statements( tax rate is 30%) Cash budget 2. Do these budgets reveal any potential problems or opportunities for Patriot? Explain
