In: Accounting
Can someone show me how this is supposed to look in a table format. I want to double check that I'm formatting and doing the numbers properly. Thank you!
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 $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 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. 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 CEO’s 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.
Hello
Let me clarify in the first place that your question is too lengthy and I have the option to answer any four sub parts of your question. I am answering only four sub parts.
(1) Sales Budget | |||
Partciulars | Jan | Feb | Mar |
Estimated Sales (in Units) (a) | 5,000 | 6,000 | 5,500 |
Selling Price per Unit (b) | 150 | 150 | 150 |
Total Sales [(a) * (b)] | 7,50,000 | 9,00,000 | 8,25,000 |
(2) Accounts Receivable as on 03/31/____ | |||
It is given that 60% sales is collected in the month of sale and balance 40% in the subsequent month | |||
Particulars | Amount | ||
Opening Balance on 1st Jan | 2,40,000 | ||
Add: Sales for Jan | 7,50,000 | (Refer Sales Budget) | |
Sub Total | 9,90,000 | ||
Less: Collection | |||
- Full Outstanding Bal on 1st jan | -2,40,000 | ||
- 60% of Sales during Jan | -4,50,000 | (750,000 * 60%) | |
Opening Balance on 1st Feb | 3,00,000 | ||
Add: Sales for Feb | 9,00,000 | (Refer Sales Budget) | |
Sub Total | 12,00,000 | ||
Less: Collection | |||
- Full Outstanding Bal on 1st Feb | -3,00,000 | ||
- 60% of Sales during Jan | -5,40,000 | (900,000 * 60%) | |
Opening Balance on 1st Mar | 3,60,000 | ||
Add: Sales for Mar | 8,25,000 | (Refer Sales Budget) | |
Sub Total | 11,85,000 | ||
Less: Collection | |||
- Full Outstanding Bal on 1st Feb | -3,60,000 | ||
- 60% of Sales during Jan | -4,95,000 | (825,000 * 60%) | |
Closing Balance as on 31st Mar | 3,30,000 | ||
(3) Cash Receipts Budget | |||
Realisation from Debtors is the only source through which cash would be received | |||
Particulars | Jan | Feb | Mar |
Realisation from Debtors | 6,90,000 | 8,40,000 | 8,55,000 |
Total | 6,90,000 | 8,40,000 | 8,55,000 |
(4) Selling & Admin Expense Budget | |||
Particulars | Jan | Feb | Mar |
Advertising Cost | 4000 | 4000 | 4000 |
Sales Commission (0.5% of Sales) | 3750 | 4500 | 4125 |
(750000*0.5%) | (900000*0.5%) | (825000*0.5%) | |
CEOs Salary (150000 annual) | 12500 | 12500 | 12500 |
CFOs Salary (125000 annual) | 10417 | 10417 | 10417 |
Admin Assisstant Salary 48000 ann | 4000 | 4000 | 4000 |
Miscellaneous Expense | 1000 | 1000 | 1000 |
Total | 35667 | 36417 | 36042 |
(Depreciation on office Equipment is ignored as it is a Non Cash Expenditure) |