Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Finance & Accounting (https://www.askmehelpdesk.com/forumdisplay.php?f=411)
-   -   Juan's Taco Company (https://www.askmehelpdesk.com/showthread.php?t=357051)

  • May 23, 2009, 10:56 AM
    majohn5
    Juan's Taco Company
    Juan's Taco Company has restaurants in five college towns. Juan wants to expand into Austin and College Station and needs a bank loan to do this. Mr. Bryan, the banker, will finance construction if Juan can present an acceptable three-month financial plan for January through March. Following are actual and forecasted sales figures:
    Actual Forecast Additional Information
    November $120,000 January $190,000 April forecast $230,000
    December 140,000 February 210,000
    March 230,000

    Of Juan's sales, 30 percent are for cash and the remaining 70 percent are on credit. Of credit sales, 40 percent are paid in the month after sale and 60 percent are paid in the second month after the sale. Materials cost 20 percent of sales and are paid for in cash. Labor expense is 50 percent of sales and is also paid in the month of sales. Selling and administrative expense is 5 percent of sales and is also paid in the month of sales. Overhead expense is $12,000 in cash per month; depreciation expense is $25,000 per month. Taxes of $20,000 and dividends of $16,000 will be paid in March. Cash at the beginning of January is $70,000, and the minimum desired cash balance is $65,000. For January, February, and March, prepares a schedule of monthly cash receipts, monthly cash payments, and a complete monthly cash budget with borrowings and repayments

    THIS IS MY ANSWER. CAN ANYONE DOUBLE CHECK THESE Answers

    November December January February March April

    Collections (40%) 33,600 39,200 53,200 58,800 64,000 64,000
    Collections (60%) 50,400 58,000 79,800 88,200 96,600 96,000





    November December January February March
    Payments for Material Purchases
    (20% of current month’s sales) 24,000 28,000 38,000 42,000 46,000 46,000
    Labor Expense (50% of sales) 60,000 70,000 95,000 105,000 115,000
    Selling and Admin. Exp. 6,000 7,000 9,500 10,500 11,500
    (5% of sales)

    Total Cash Payments 90,000 105,000 142,000 53,500 106,650

    Net profit
    30,000 35,000 67,500 156,450 123,500
  • May 24, 2009, 02:55 PM
    morgaine300
    Quote:

    November December January February March April

    Collections (40%) 33,600 39,200 53,200 58,800 64,000 64,000
    Collections (60%) 50,400 58,000 79,800 88,200 96,600 96,000
    You've got a start, but it's not there. I don't know where you got the 58,800. And the 64,000 doesn't really exist so I'm not sure where you've gotten that from. Across the bottom I have 50,400; 58,800 and 79,800. I'm assuming perhaps that 58,000 is a typo since you seem to have the idea about the math. I didn't even check the other numbers because it stops at March and I went no further.

    Let's just look at November. 84,000 is going to be on credit. 40% of that is 33,600 and 60% is 50,400. So that's where those numbers are coming from. But notice what it says: "Of credit sales, 40 percent are paid in the month after sale and 60 percent are paid in the second month after the sale." For November, the month "after the sale" is December, so the 33,600 is collected in December. Then the "second month" would be January, so the 50,400 is collected in January. Meaning you need to be shoving some numbers over. That wasn't collected in November - the sales are from November. You're doing the math, but ignoring the times.

    You've also left out the cash sales. Remember this is a CASH budget so we're concerned with what our cash is doing. If 30% of the sales were for cash, then you collected that 30% in that month. That has to be included also, not just what was collected off receivables.

    Try fixing that section first. That of course is going to have an effect on the bottom line numbers.


    Quote:

    November December January February March
    Payments for Material Purchases
    (20% of current month’s sales) 24,000 28,000 38,000 42,000 46,000 46,000
    Labor Expense (50% of sales) 60,000 70,000 95,000 105,000 115,000
    Selling and Admin. Exp. 6,000 7,000 9,500 10,500 11,500
    (5% of sales)

    Total Cash Payments 90,000 105,000 142,000 53,500 106,650
    To begin with, you don't need to list November and December on any of this. You need those numbers for the cash collections because it carries forward into January and February. But only January through March are going to be on your report. So for the cash payments part, Nov & Dec are unnecessary. (And I didn't do them.) I also don't understand why you have 6 numbers listed for materials when you only listed 5 months. Knock that last 46,000 off there and those are fine. Labor, and selling & admin are also fine. (Keeping in mind that I'm not checking Nov & Dec cause I didn't do them, and you don't need them.)

    However, you've missed a bunch of stuff. What about this section: "Overhead expense is $12,000 in cash per month; depreciation expense is $25,000 per month. Taxes of $20,000 and dividends of $16,000 will be paid in March." You haven't accounted for any of this at all. So you still need to do this. Do keep in mind you're doing CASH payments, so be thinking about what is cash and what is not. You need to finish this section up and fix the cash collections before you can move on, because you also need to deal with cash balances.

    Quote:

    Net profit
    30,000 35,000 67,500 156,450 123,500
    Net profit?? Net profit comes from an accrued basic income statement, not from a CASH budget. This is a listing of where cash is coming from and going to -- that does not make net profit. This subject is far enough into accounting that you should be knowing the difference by now.
  • May 24, 2009, 04:52 PM
    majohn5
    I have redone some answers but in regard to total expense and net cash flow I am don't so sure I have complete these correctly. Please can someone tell me if I am on the right track.


    November December January February March
    Sales 120,000 140,000 190,000 210,000 230,000
    Cash sales (30%) 36,000 42,000 57,000 63,000 69,000
    Collections (40%) 36,600 39,200 53,200 58,000
    Collections (60%) 50,400 58,800 79,800




    January February March
    Payments for Material Purchases
    (20% of current month’s sales) 38,000 42,000 46,000
    Labor Expense (50% of sales) 95,000 105,000 115,000
    Selling and Admin. Exp. 9,500 10,500 11,500
    (5% of sales)
    Overhead 12,000 12,000 12,000

    Taxes 20,000


    Dividends 16,000

    Depreciation expense 25,000 25,000 25,000
    Total expense 179,500 194,500 245,500




    January February March
    Total Cash Receipts
    Total Cash Payments
    Net Cash Flow 10,500 15,500 -15,500
  • May 24, 2009, 10:47 PM
    morgaine300
    Quote:

    November December January February March
    Sales 120,000 140,000 190,000 210,000 230,000
    Cash sales (30%) 36,000 42,000 57,000 63,000 69,000
    Collections (40%) 36,600 39,200 53,200 58,000
    Collections (60%) 50,400 58,800 79,800
    Numbers are correct, but I still can't tell what columns you have them in. It might look fine when you type it, but look at it after you post it. It just shoves everything to the left. You can use some... to separate the columns, but you'll have to do a Preview to see how it comes out looking. Or... do it in Excel and attach the file.

    But I do know something isn't right. If I take your January net cash flow, and add back in the 179,500 "expenses" - I get 190,000. That's the sales from December, but I don't know if that's the number you used. Two things wrong with that. One, it's not January. Two, it isn't about sales. It's about CASH. You've got to constantly be thinking about cash, cash, cash. It's a cash budget. It isn't a sales budget. The report isn't about when the sales are made -- that is just information you use to get to the cash. What you want is cash collections. And I can't check if those are correct because I can't decypher the columns.


    Quote:

    January February March
    Payments for Material Purchases
    (20% of current month’s sales) 38,000 42,000 46,000
    Labor Expense (50% of sales) 95,000 105,000 115,000
    Selling and Admin. Exp. 9,500 10,500 11,500
    (5% of sales)
    Overhead 12,000 12,000 12,000

    Taxes 20,000


    Dividends 16,000

    Depreciation expense 25,000 25,000 25,000
    Total expense 179,500 194,500 245,500
    These aren't "expenses." These are cash expenditures. While an expense can also be a cash expenditure and vice versa, they aren't actually the same thing. A cash expenditure is what you actually spent in cash. What do you have listed here that isn't spent in cash? I also still can't tell the columns, but you've listed the totals so I was able to figure it out. i.e. the taxes and dividends are in the correct place.

    Quote:

    January February March
    Total Cash Receipts
    Total Cash Payments
    Net Cash Flow 10,500 15,500 -15,500
    Any time anything in the top two sections are messed up, the net cash flow of course is going to be messed up. So this still needs fixed before you can get the rest.

    The actual concept of the rest isn't that difficult if you will keep your mind on CASH. Let's pretend for a moment that 10,500 is correct for January. (It's not, but let's pretend.) And they are telling you that you had a beginning balance in cash of 70,000. If you had a positive net cash flow of 10,500, then what is your ending balance in cash going to be?

    Then you need a minimum balance of 65,000. If you do not have that much, you have to borrow to get it up to that much to meet that minimum. And even I haven't done that part yet cause we don't have the other two sections completed yet. If you do have 65,000 or higher, then you don't have to borrow and you leave the balance as is. But you've got to get the other sections correct first, and get January completed cause you'll need your ending balance. (Remember that the balance from January will carry into February, etc.)
  • May 25, 2009, 11:22 AM
    majohn5
    Ok I am trying this again. This assignment is due tonight and I really feel that I just am not getting it. Please help





    Sales 120,000 (nov) 140,000(dec) 190,000(jan ) 210,000(march) 230,00 (apr)
    Collections-cash
    (30%) 36,000 42,000 57,000 63,000 69,000

    Collections (40%) 36,600 39,200 53,200 58,000

    Collections (60%) 50,400 58,800 79,800




    Payments for Material
    Purchases 38,000(jan) 42,000(feb) 48,000(march)
    (20%- paid in cash
    The month of sales)

    Labor Expense (50% of sales) 95,000 105,000 115,000
    Selling and Admin. Exp.
    (5% of sales) 9,500 10,500 11,500
    Overhead 12,000


    Taxes 20,000


    Dividends 16,000
    Total expense 142,500 157,500 220,500





    Net Cash Flow 47,500 (jan) 52,500 (feb) 9,500 (march)
    Beginning Cash Balance 70,000 22,500 29,500
    Cumulative Cash Balance 22,500 29,500 20,000
    Borrowing
    (65,000-Cumulative cash balance) 42,500 35,500 45,000
  • May 25, 2009, 11:28 AM
    majohn5
    Ok I reposted to help clear up so numbers. I was unable to attach document.


    Sales 120,000 (nov) 140,000(dec) 190,000(jan ) 210,000(march) 230,00 (apr)
    Collections-cash
    (30%) 36,000(nov) 42,000(dec) 57,000(jan) 63,000(feb) 69,000(march)

    Collections (40%) 36,600(dec) 39,200(jan) 53,200(feb) 58,000(march)

    Collections (60%) 50,400(jan) 58,800(feb) 79,800(march)




    Payments for Material
    Purchases 38,000(jan) 42,000(feb) 48,000(march)
    (20%- paid in cash
    The month of sales)

    Labor Expense (50% of sales) 95,000(jan) 105,000(feb) 115,000(march)
    Selling and Admin. Exp.
    (5% of sales) 9,500(jan) 10,500(feb) 11,500(march)
    Overhead 12,000(march)


    Taxes 20,000(march)


    Dividends 16,000(march)
    Total expense 142,500(jan) 157,500 (feb) 220,500 (march)





    Net Cash Flow 47,500 (jan) 52,500 (feb) 9,500 (march)
    Beginning Cash Balance 70,000(jan) 22,500(feb) 29,500 (march)
    Cumulative Cash Balance 22,500(jan) 29,500(feb) 20,000 (march)
    Borrowing
    (65,000-Cumulative cash balance) 42,500(jan) 35,500(feb) 45,000(march)
  • May 25, 2009, 08:44 PM
    morgaine300
    Quote:

    Sales 120,000 (nov) 140,000(dec) 190,000(jan ) 210,000(march) 230,00 (apr)
    Collections-cash
    (30%) 36,000(nov) 42,000(dec) 57,000(jan) 63,000(feb) 69,000(march)

    Collections (40%) 36,600(dec) 39,200(jan) 53,200(feb) 58,000(march)

    Collections (60%) 50,400(jan) 58,800(feb) 79,800(march)
    Yes, that helps a great deal figure out what's what. It's all correct, except that the 40% collected in March is 58,800. (210,000 Feb sales less 30% cash, leaves 147,000 on account, and 40% is 58,800.)

    The issue that remains is that you need to not put Nov & Dec on the final report. You can put that info as a footnote, like to show how you did the calculations, but it does not belong on the cash budget for the 1st quarter. Only include Jan through March for that. Also, the sales probably should not be on the final report either. Again, that can be included in a calculation section, but it's not part of cash collections. (You seem to not be getting the difference between the sales and the cash collections, but more on that below.)

    In the long run, if you have an example of one of these, follow it regardless of what I say, especially if you have a picky instructor.


    Quote:

    Payments for Material
    Purchases 38,000(jan) 42,000(feb) 48,000(march)
    (20%- paid in cash
    The month of sales)

    Labor Expense (50% of sales) 95,000(jan) 105,000(feb) 115,000(march)
    Selling and Admin. Exp.
    (5% of sales) 9,500(jan) 10,500(feb) 11,500(march)
    Overhead 12,000(march)


    Taxes 20,000(march)


    Dividends 16,000(march)
    Total expense 142,500(jan) 157,500 (feb) 220,500 (march)
    Real darn close. You dumped the non-cash depreciation. :D However, you also dumped overhead out of Jan & Feb, and that does belong there. Notice it says "per month," not "in March" like the taxes and divdends say. Get the overhead back into Jan & Feb and you'll be good to go on this section. For something like the depreciation, you have to be keeping your mind on cash and realize depreciation isn't a cash payment. (They throw that in to make sure you get that.) But something like the overhead is a matter of just reading the problem carefully. Take it literally.


    Quote:

    Net Cash Flow 47,500 (jan) 52,500 (feb) 9,500 (march)
    You're having the same issue here that I spoke of in the last post and haven't fixed yet. You're still using SALES from the top section to figure this out. SALES isn't CASH flow. You're labeling this "net cash flow" but sales isn't cash flow. I already explained this and can only repeat myself. CASH, CASH, CASH. You never totaled your cash collections in the top section, which is something you should do.

    Net CASH flow is the cash incoming less the cash outgoing. You're subtracting the cash outgoing, but you're subtracting it from sales. That's not the cash inflows. You're sticking 70% of the sales into receivables - how can that match cash inflows? The entire point of doing that first section is to get the CASH collections.

    Have I stressed the word CASH enough yet? ;)

    Quote:

    Beginning Cash Balance 70,000(jan) 22,500(feb) 29,500 (march)
    Cumulative Cash Balance 22,500(jan) 29,500(feb) 20,000 (march)
    Borrowing
    (65,000-Cumulative cash balance) 42,500(jan) 35,500(feb) 45,000(march)
    OK, this is all messed up. Stop thinking about accounting for a minute and think about your bank account. That's all this is. A bank account is an everyday sort of thing that most people have, and there's nothing special about it for accounting.

    If you have a positive net cash flow and a beginning balance of 70,000, how do you end up with less money than you started with? You should actually end up with a negative cash flow (once you get the idea that you need to be using cash collections and not sales), but as of now, you have a positive, so we need to make sure you get this concept. Plus it could be positive in the future. Positive cash flows don't reduce balances.

    Also, pretending 22,500 is correct, if you borrwed 42,500 to get the balance up to 65,000, then wouldn't you have 65,000 to carry over into February? You almost have the idea on that, but you do actually have the cash you borrowed. So you'd be carrying over the 65,000.

    The borrowing idea you've got right. Once you've corrected a couple of other things, that should work out OK.

    As an example, let's say your beginning balance is $100, and you have a negative cash flow of $130 for January, and your minimum balance is $50. I'm then going to carry that into an example February and March:

    January:
    Cash flow (130) (use parenthesis for any negatives)
    Beginning balance 100
    Ending balance (30)
    Borrowings 80
    Adjusted ending balance 50 (the minimum)

    February:
    Cash flow (10)
    Beginning balance 50
    Ending balance 40
    Borrowings 10
    Adjusted ending balance 50

    March:
    Cash flow 30
    Beginning balance 50
    Ending balance 80
    Repayment (30) (cause you have the extra cash and can still stick with the minimum)
    Adjusted ending balance 50

    I still haven't done that last part for this problem so I don't know how this comes out yet, but that's just an example of some of the stuff that can happen and how you would handle it.
  • May 27, 2009, 04:21 PM
    majohn5

    Thank you for all the help. The assignment was posted already. I changed a lot of answers from the last post but I still don't think I got the right answer
  • May 28, 2009, 06:29 PM
    morgaine300

    You're welcome. Good luck with it - if you would like to go over anything after you get it back (especially if you need it for a test or anything), let me know.
  • Jun 2, 2009, 06:26 AM
    majohn5

    Can you please double check this answer?


    You need $28,974 at the end of 10 years, and your only investment outlet is an 8 percent long-term certificate of deposit (compounded annually). With the certificate of deposit, you make an initial investment at the beginning of the first year.

    1.What single payment could be made at the beginning of the first year to achieve this objective?
    2. What amount could you pay at the end of each year annually for 10 years to achieve this same objective?



    Formula

    1) Amount =Principal(1+rate)n

    Amount= 28974
    Let Principal=x
    n=10
    r=0.08

    28974= x(1+0.08)10

    28974=X x 2.1589

    X = 28974/2.1589

    X = $13420.57

    2) The amount that has to be paid annually at the end of each year to achieve the same objective as above

    The formula for finding this Annuity is

    Future Value = Annuity[(1+rate)n -1]
    rate

    28974 = Annuity[(1+0.08)10 -1]
    0.08

    A = (28974x0.08)/(2.1589-1)

    A= (2317.92)/(1.1589)

    A= 2000.06

    Hence the amount to be paid annually at the end of each year to get a sum of $28974 in 10 years is $2000.06.

  • All times are GMT -7. The time now is 03:18 PM.