This has to be answered in MS excell

Budget Project

Ch 21

Timber Construction constructs furniture. Theyve decided they need to layout out their budgets for the first Quarter of 2019 to see if they will make a profit and have cash for a future expansion that will cost $400,000. They always must keep $100,000 minimum in the checking account every month. (Assume the beginning of the Quarter has the minimum cash balance.) The CEO also wants to have a minimum of a 10% profit margin for the Quarter to ensure stability.

The CEO has said she wants to sell 5000 units in January, 6000 units in February, and 5500 in March. Looking forward into the second Quarter, she hopes to sell 7000 units in April. Each item sale price will be set at $150/unit. To build each unit, the purchasing agent says he can get the lumber for $50/unit, paint for $4/unit, and miscellaneous supplies for $5/unit. The production manager, based on past experience, says it costs about 2 hours/unit at $20/hour in labor costs. You are able as CFO to pull the other costs for the budgets: Utilities are about $6/unit, Factory salaries run $25,000/month, Factory property taxes average $5,000/month, and depreciation on Factory equipment is $22,000/month. Advertising costs average $4,000/month. Sales Commission is .5% of Gross Sales. CEO Salary is $150,000/year; CFO Salary is $120,000/year; Admin Assistant is $48,000/year. (Ignore payroll taxes.) Miscellaneous office expenses are about $1,000/month. Office Equipment is depreciated at $500/month. Cash payments are processed in the month of. The CEO would like 40% of next months production ready to sell so there is no shortages. Cash is collected 60% in the month of sale, and the remainder in the following month. Expected balances for certain accounts are listed below for your use.

Accounts Receivable on 1/1 is $240,000

Accounts Payable on 1/1 is $180,000

Accounts Payable on 3/31 is $200,000

Retained Earnings on 1/1 is $1,400,000

Income Tax Rate is 30%

Finished Goods, 1/1 is $160,000

Finished Goods, 3/31 is $280,000

WIP, 1/1 is $20,000

WIP, 3/31 is $25,000

Raw Materials desired beginning, 1/1 is $60,000 (Lumber $49,000; Paint $5,000; Misc. Supplies $6,000)

Raw Materials desired ending, 3/31 is $84,000 (Lumber $70,000; Paint $6,000; Misc. Supplies $8,000)

What was the 3/31 balance in Accounts Receivable?

Will they have enough money on March 31 to move forward with the expansion? Why or why not?

What is the profit margin? Does it meet the CEOs minimum requirement?

Prepare a Sales Budget, Production Budget, Direct Materials Budget, Direct Labor Budget, Factory Overhead Budget, Cost of Goods Sold, Selling & Admin Expense Budget, Proforma Income Statement, Cash Receipts Budget, Cash Payments Budget, Cash Budget.

Use formulas and cell references when using Excel.