Timber Construction constructs furniture. They’ve 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 $600,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 12% profit margin for the Quarter to ensure stability.

The CEO has said she wants to sell 5500 units in January, 6000 units in February, and 5000 in March. Looking forward into the second Quarter, she hopes to sell 6500 units in April.

Each item sale price will be set at $170/unit.

To build each unit, the purchasing agent says he can get the lumber for $50/unit, paint for $10/unit, and miscellaneous supplies for $5/unit. The production manager, based on past experience, says it costs about 2.6 hours/unit at $20/hour in labor costs.

You are able as CFO to pull the other costs for the budgets: Utilities are about $8/unit, Factory salaries run $25,000/month, Factory property taxes average $6,000/month, and depreciation on Factory equipment is $20,000/month. Advertising costs average $2,500/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,500/month. Office Equipment is depreciated at $500/month.

Additional info: Cash payments are paid in the month of. The CEO would like 40% of next month’s 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.

Accounts Receivable on 1/1 is $240,000

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

Income Tax Rate is 20%

Finished Goods, 1/1 is $160,000

Finished Goods, 3/31 is $280,000 (estimated)

WIP, 1/1 is $20,000

WIP, 3/31 is $25,000 (estimated)

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)

Prepare Quarterly Budgets for 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.)

Answer these Questions:

What was the 3/31 balance in Accounts Receivable?

Will they have enough money on March 31 to move forward with the expansion?

What is the profit margin?

Would you recommend the expansion? Why or why not?

Answer rating: 100% (QA)

solution X Book1 Microsoft Excel Product Activation Failed Load Test Team Data Review View SRL Wrap ... View the full answer