Question

In: Accounting

Can someone show me how this is supposed to look in a table format. I want...

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.

Solutions

Expert Solution

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)

Related Solutions

I see theres solution posted but its done in Mathmatica format. Can someone show me the...
I see theres solution posted but its done in Mathmatica format. Can someone show me the solution using Matlab, please? Consider a particle in a room. How likely is the particle to hit the east wall of the room? Assume the western wall repels the particles and there is no vertical motion. The position at a subsequent time is related to the current position by x^n+1 = z^n + f(x^n) + Noise where f(x) = k/x^2 so that the force...
Can someone show me how this answer is 8,080 ? I keep coming up with the...
Can someone show me how this answer is 8,080 ? I keep coming up with the wrong number. Stellar Company has the following sales, variable cost, and fixed cost. If sales increase by $10,000 then their profit increases/decreases by how much? Sales $50,000 Variable Costs $9,600 Fixed Costs $29,000 Answer is 8,080 Calculate the total cost of FC and VC for $50,000. 2) Calculate the increase in VC based on the % increase in Sales. VC/50000 * new sales 3)...
I have the answers listed I just want to know if someone could show me the...
I have the answers listed I just want to know if someone could show me the work step by step! Q1. A ball of mass 60 g is dropped from a height of 3.4 m. It lands on the top of a frictionless ramp at height 1.8 m. The ramp is tilted at an angle of 20 degrees. (a) What is the velocity of the ball at the top of the ramp? Answer: 5.6 m/s (b) At the bottom of...
Can someone answer it for me and may i have the reference in apa format? thank...
Can someone answer it for me and may i have the reference in apa format? thank you in advance! 1. What is cholesterol's mechanism of absorption, synthesis, esterification, and metabolism? 2.Does the low-cholesterol diet translate to a lower risk of CHD? 3. Why is there a need to esterify cholesterols? 4. How does fatty acid contribute to the generation of energy via beta-oxidation process. 5Why is there a presence of ketones in uncontrolled type 1 DM? Why is it absent...
Hello, I was wondering if someone could show me the process as to how to answer...
Hello, I was wondering if someone could show me the process as to how to answer this question. The answers were posted by the teacher assistant however I do not actually know how to solve the question: The production function of a firm is given as ? =K 1/2, L1/2 , capital is 200. The rental rate for both labor and capital is 5 and 20. There are total 20 firms that produce the same good. A. Write down the...
Can someone show me how to do a test for lack of fit for the following...
Can someone show me how to do a test for lack of fit for the following data? Please show all work for an up vote. Thanks. y x4 x7 x9 29.5 1.5 4 0 27.9 1.175 3 0 25.9 1.232 3 0 29.9 1.121 3 0 29.9 0.988 3 0 30.9 1.24 3 1 28.9 1.501 3 0 35.9 1.225 3 0 31.5 1.552 3 0 31 0.975 2 0 30.9 1.121 3 0 30 1.02 2 1 36.9 1.664...
Can someone show me the steps of how to solve this? A program needs to access...
Can someone show me the steps of how to solve this? A program needs to access the following pages: 1, 2, 3, 4, 2, 1, 3, 2, 1, 4, 2, 3 There are 3 initially empty frames, how many page faults will there be respectively, if we use First-in-First-out, and Farthest-in-Future page replacement algorithms? A) 7 and 7 B) 7 and 6 --  Correct Answer   C) 6 and 6 D) 6 and 5
Okay, can someone please tell me what I am doing wrong?? I will show the code...
Okay, can someone please tell me what I am doing wrong?? I will show the code I submitted for the assignment. However, according to my instructor I did it incorrectly but I am not understanding why. I will show the instructor's comment after providing my original code for the assignment. Thank you in advance. * * * * * HourlyTest Class * * * * * import java.util.Scanner; public class HourlyTest {    public static void main(String[] args)     {        ...
Can someone show me step by step how to determine the lattice constants of Graphite?
Can someone show me step by step how to determine the lattice constants of Graphite?
Can someone please show me how to do the work on this please. Question The transactions...
Can someone please show me how to do the work on this please. Question The transactions of the Fury Delivery Service are recorded in the general journal below. Instructions: 1. Post the journal entries to the attached general ledger “T” accounts. 2. Prepare a trial balance on the form provided after the “T” accounts. General Journal Date Account Titles and Explanation Debit Credit 2017 Sept. 1 Cash Common Stock (Stockholders invested cash in business) 25,000 25,000 4 Equipment Cash Notes...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT