Details of this Paper

aCCOUNTING Exam QuestioN

Description

solution


Question

Question;You are a relatively recent hire to the;Hartz & Co., a local manufacturer of plumbing supply products. You have;been asked to prepare for a presentation to the company?s management a;condensed cash-flow statement for the months of November and December, 2013.;The cash balance at November 1st was $51,000. It is the;company?s policy to maintain a minimum cash balance of $51,000 at the end of;each month. Cash receipts (from cash sales and collection of accounts;receivable) are projected to be $563,700 for November and $455,000 for;December. Cash disbursements (sales commissions, advertising, delivery;expense, wages, utilities, etc.), prior to financing activity, are scheduled;to be $554,000 in November and $500,700 in December.;Borrowing, when needed, is done at the beginning of the month -;in increments of $1,000. The annual interest rate on any such loans is;estimated to be 13.00%. Interest on any outstanding loans is paid in cash at;the end of the month. Interest on any outstanding loans is paid in cash at;the end of the month. Repayments of principal (if any, in whole dollars) are;assumed to occur at the end of the month. As of November 1st, the company has;a $51,000 short-term loan from the local bank.;Required Use the preceding information to prepare;the cash budget for November and December. (Hint: The December 31st cash;balance should be $51,483.);Data;Input;Cash balance, November;1st;$51,000;Minimum required cash;balance;$51,000;Budgeted cash receipts;November;#######;December;#######;Budgeted cash;disbursements;November;#######;December;#######;Interest rate on;borrowings;13.0%;per year;Short-term loan payable;as of November 1st;$51,000;Borrowings in increments;of;$1,000;Solution;Select from dropdown;Select from dropdown;November;December;Cash balance, beginning;Formula;Formula;Plus: Cash receipts;Formula;Formula;Total Cash Available;Formula;Formula;Cash disbursements, prior;to financing;Formula;Formula;Plus: Minimum cash;balance (given);Formula;Formula;Total Cash Needed;Formula;Formula;Excess (deficiency of);cash, before financing effects;Formula;Formula;Financing;Balance Short term loan;beginning of month;Formula;Formula;Short-term borrowing;beginning of the month*;Formula;Formula;Repayments (loan;principal), end of the month;Formula;Formula;Balance Short term loan;end of month;Formula;Formula;Interest (@13.00%), paid;in cash @ end of the month;Formula;Formula;Total effects of;financing;Formula;Formula;Ending cash balance;Formula;Formula;* Hint: the borrowing is at the beginning of the month;therefore your algebra;needs the following;what you borrow needs to;include the interest to be paid;interest to be paid;needs to be a function of the amount borrowed and any existing balance;therefore a circular;reference problem;so, use High School;algebra;amount to borrow = AM;amount of interest = I;formulate two linear;equations;AM = f();?;AM =;-I35+I;=;-I35+(H38+AM)*H18/12;IF(I35-H38*H18/12-(I29-I33)*H18/12<0;Borrow, Not Borrow);I = f();?;I =;(H38+I39)*H18/12;Borrow = Roundup in;multiples of borrowing amounts;solve for AM and I;Not Borrow = 0;The repayment is at the;end of the month;You need two IF;statements;IF (There is a loan;balance, IF (Excess cash, Make a repayment, Do not make repayment), Do not;make repayment);Make a repayment = MIN (Amount borrowed or The Amount of;excess cash available);Make the repayment in;whole dollars - use INT;Test your model's algebra;by entering various amounts for Cash receipts;e.g.;0;100,000;500,000;750,000

 

Paper#41367 | Written in 18-Jul-2015

Price : $22
SiteLock