Excel Project on budgetingPatriot Manufacting,Inc, produces blow­out preventers used in drilling. T

Do you require help with your paper? Use our custom writing service to achieve better grades and meet your deadlines. Trust our team of writing experts with your work today, and enjoy peace of mind.

Order a Similar Paper Order a Different Paper

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.


Our writing experts are ready and waiting to assist with any writing project you may have. From simple essays, research papers, lab reports, and dissertations, to online classes, you can be sure we have a service that perfectly matches your needs.

Order a Similar Paper Order a Different Paper