Can someone show me how this is supposed to look in a tableformat. I want to double check that I'm formatting and doing thenumbers properly. Thank you!
Timber Construction constructs furniture. They’vedecided they need to layout out their budgets for the first Quarterof 2019 to see if they will make a profit and have cash for afuture expansion that will cost $400,000. They always must keep$100,000 minimum in the checking account everymonth. (Assume the beginning of the Quarter has theminimum cash balance.) The CEO also wants to have aminimum of a 10% profit margin for the Quarter to ensure stability.The CEO has said she wants to sell 5000 units in January, 6000units in February, and 5500 in March. Looking forwardinto the second Quarter, she hopes to sell 7000 units inApril. Each item sale price will be set at $150/unit. Tobuild each unit, the purchasing agent says he can get the lumberfor $50/unit, paint for $4/unit, and miscellaneous supplies for$5/unit. The production manager, based on pastexperience, says it costs about 2 hours/unit at $20/hour in laborcosts. You are able as CFO to pull the other costs forthe budgets: Utilities are about $6/unit, Factorysalaries 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 GrossSales. CEO Salary is $150,000/year; CFO Salary is$120,000/year; Admin Assistant is $48,000/year. (Ignore payrolltaxes.) Miscellaneous office expenses are about$1,000/month. Office Equipment is depreciated at$500/month. Cash payments are processed in the monthof. The CEO would like 40% of next month’s productionready to sell so there is no shortages. Cash is collected 60% inthe month of sale, and the remainder in the followingmonth. Expected balances for certain accounts are listedbelow 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 theexpansion? Why or why not?
What is the profit margin? Does it meet the CEO’s minimumrequirement?
Prepare a Sales Budget, Production Budget, Direct MaterialsBudget, Direct Labor Budget, Factory Overhead Budget, Cost of GoodsSold, Selling & Admin Expense Budget, Proforma IncomeStatement, Cash Receipts Budget, Cash Payments Budget, CashBudget.
Use formulas and cell references when using Excel.
