Results 1 to 4 of 4

Math Help - please help with excel exercises

  1. #1
    Junior Member
    Joined
    Oct 2006
    From
    canada
    Posts
    68

    please help with excel exercises

    Hello, how are you.

    could you please help me to solve these excel exercises, they are so difficult for me, I don't know what formulas to use.

    A standard auto loan. For each of the 48 monthly payments compute the items in the table: Beginning Balance, Payment,
    Interest portion of the Payment, etc ... Use Excel's Payment function (PMT), and be careful about relative and absolute
    cell references. The payment, of course, will be the same for all months.

    A detail: interest rates are usually quoted on an annual basis. But here the payments are monthly. How will you account for
    this when you reference the interest rate?

    If you do this cleverly, you can specify the first two rows, then make one big copy for the other 46 rows.


    Amount $25,000.00

    Interest Rate 6%

    Months 48


    Beginning Balance Payment Amount Interest Principal Reduction Ending Balance
    1 25,000.00
    2
    3

    etc

    Total Interest Total Principal
    ================================================== ==========






    Prepare a Purchases Budget for Grunk that reflects a Cost of Merchandise Sold (Cost of Goods Sold, Cost of Sales)
    of 60% and a required ending inventory of 65% of the next months sales.

    Beginning inventory is $50,000.
    Sales for January of next year are estimated to be $365,000


    Jan Feb

    Sales $300,000

    Cost of
    Mrch Sold

    Ending
    Inventory

    Beginning
    Inventory 50,000

    Required
    Purchases
    ---------------------------------------------------------------------



    Prepare a Cash Receipts Budget for Grunk based on its historical cash collection patterns:

    50% in the month of sale.
    40% in the month following the sale.
    10% two months following.

    January collections will include $80,000 from the previous December and $20,000 from November.
    February collections will include $20,000 from the previous December.

    Jan Feb

    Sales $300,000

    Cash Collections:

    Month of
    Sale

    Month $80,000
    Following

    2 Months $20,000 20,000
    Following

    Total
    ================================================== ==========



    Create a Cash Disbursements Budget for Grunk based on historical patterns:

    40% of payments are made in the month of purchase.
    60% of payments are made in the month following.

    January payments are expected to include $60,000 from December purchasing activity.


    Jan Feb

    Required
    Purchases

    Cash Payments:

    Month of
    Purch

    Month 60,000
    Following

    Total
    Paymnts
    ==========================================

    thank you.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor
    Joined
    Aug 2007
    From
    USA
    Posts
    3,110
    Thanks
    2
    First, please notice that Excel has documentation all along the way. Just type "=pmt(". As soon as you type the left parenthesis, you should get a picture of available parameters. The same with many others. Try "=fv(". It's great stuff and should help you on your way.

    The deterimination of what to do with mismatched interest crediting and payments MUST be specified in the problem statement or you should have a very good argument if you get it wrong on an exam. Generally, unless you are an insurance company, interest rates are reported as the rather useless NOMINAL Annual rate. That's all fine, but you must know about compounding in order to get the right answer. It also helps to know a little about common practices. For example, auto loans often report nominal annual and take payments monthly. For auto loans, simply dividing the nominal annual rate by 12 will do. If this sort of thing is not given in the problem statement, you state it clearly before proceeding. This will give you power when you argue the point with the grad student who marked it wrong.

    In your case, enter simply "=pmt(0.06/12,48,25000,0,0)"

    0.06/12 is the monthly interest.

    48 is the number of months. Notice that this is 4*12 = 48

    25000 is the beginning balance

    0 is the final balance. This is good for "salvage" questions. If there is no salvage value, or if the loan is completely paid off, you can use "0" or simply omit the value.

    0 is an option switch. Uusually, you can forget about it as a "0" or nothing means payments are at the end of each period. sometimes you want payments at the beginning of the period and this will require a "1" in the fifth paramenter.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Junior Member
    Joined
    Oct 2006
    From
    canada
    Posts
    68

    thank you.

    Thank you so much tkhunny, I appreciate your help, but I still don't understand, I suck at excel, could you please tkhunny go to this website Untitled Document and solve for me the exercises titled "special ll" and mail the answers to my email camachi@hotmail.com I solved special problems I, but I just can't solve the second set of exercises, they are the same exercises I already typed. But probably you could understand them better if you see them in an excel sheet. Please Tkhunny help me.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Forum Admin topsquark's Avatar
    Joined
    Jan 2006
    From
    Wellsville, NY
    Posts
    9,843
    Thanks
    320
    Awards
    1
    Quote Originally Posted by jhonwashington View Post
    Thank you so much tkhunny, I appreciate your help, but I still don't understand, I suck at excel, could you please tkhunny go to this website Untitled Document and solve for me the exercises titled "special ll" and mail the answers to my email camachi@hotmail.com I solved special problems I, but I just can't solve the second set of exercises, they are the same exercises I already typed. But probably you could understand them better if you see them in an excel sheet. Please Tkhunny help me.
    As opposed to learning how to do it and doing the problems yourself?? Sheesh!

    -Dan
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Some exercises
    Posted in the Trigonometry Forum
    Replies: 7
    Last Post: March 30th 2010, 07:32 AM
  2. Please help me with these exercises!
    Posted in the Algebra Forum
    Replies: 4
    Last Post: September 7th 2009, 08:24 AM
  3. Need help with few exercises. Please help!
    Posted in the Algebra Forum
    Replies: 1
    Last Post: May 26th 2009, 01:10 AM
  4. 2 exercises
    Posted in the Differential Geometry Forum
    Replies: 4
    Last Post: May 12th 2009, 12:37 PM
  5. please help me with these exercises
    Posted in the Algebra Forum
    Replies: 4
    Last Post: January 19th 2008, 12:48 PM

Search Tags


/mathhelpforum @mathhelpforum