Excel Project on budgetingPatriot Manufacting,Inc, produces blowÂout preventers used in drilling. Thepreventers are sold to various gas and oil drilling companies throughout the UnitedStates.Projected sales in units for the coming months are:January 25000February 35000March 20000April 20000The following data pertain to production polices and manufacturing specificationsfollowed patriot.a. Finished goods inventory on January 1 is 15000 units. The desired endinginventory for each month is 60 percent of the next monthâ€™s sales.b. The blowÂout preventers require two different types of materials. Data onmaterials used are:Direct Material PerÂunit Usage Unit CostPart 714 5 $4.00Part 502 3 $3.00Inventory policy dictates that sufficient materials be on hand at the beginning of themonth to produce 50% of that monthâ€™s estimated sales. This is exactly the amount ofmaterials in stock on January 1.c. The direct labor used per unit of output is two hours. The average direct labor costper hour is $15.d. Overhead each month is estimated using a flexible budget formula. Activity ismeasured in direct labor hours.Fixed Cost Component Variable per unit componentSupplies $ 0 $ 1.00Power $ 0 $ 0.20Maintenance $ 28,000 $ 1.10Supervision $ 14,000 n/aDepreciation $ 100,000 n/aTaxes $ 7,000 n/aOther $ 56,000 $ 1.60e. Monthly selling and administrative expenses are also estimated using a flexiblebudget formula. Activity is measured in units sold.Fixed Costs Variable CostsSalaries $ 30,000 n/aCommissions 0 $ 0.75Depreciation $5,000 n/aShipping 0 $ 2.60Other $10,000 $ 0.40f. The unit selling price of the blowÂout preventer is $90.g. During February, the company plans to purchase land for future expansion. Theland 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 theend of a month, they can borrow any needed cash from their bank under a line ofcredit. The bank agreement states that interest will be paid every month based onthe loan balance at the beginning of the month. The loan agreement specifies thatthe company will maintain a cash balance of at least $50,000. The company willpay off any loan balances as funds are available.Required1. Using Excel, prepare the following for the first three quarters(nine months) forPatriot Manufacturing, Inc.:Sales budgetProduction budgetDirect materials purchase budgets( you will need one of each part)Direct labor budgetOverhead budgetSelling and administrative expense budgetEnding finished goods inventory budgetCost of goods sold budgetBudgeted income statements( tax rate is 30%)Cash budget2. Do these budgets reveal any potential problems or opportunities for Patriot?Explain.
Writingexpert.net helps students cope with online college assignments and write papers on a wide range of topics. We deal with online discussion classes, academic writing, creative writing, and non-word assignments.
Phone: +1 (940) 905 5542