Question Details

(solution) FIN 5130 Quiz 5 Extra Credit Instructor: Dr. Palkar Late


This assignment will require you to prepare the cash budget in excel, and determine the cashsurplus and shortage each month.The management estimates total sales for the period January through July based on actualsales from the immediate past quarter.

The following assumptions are made:Sales: Past and ExpectedOctober $300,000 January $150,000November $350,000 February $200,000December $400,000 March $200,000April $300,000May $250,000June $200,000July $300,000Historical Forecasta. Sales are 75% on credit basis and 25% on cash basis. 60% of credit sales arecollection one month after sale, 30% in the second month, and 10% in the thirdmonth. Bad debts are insignificant.b. Purchases are 80% of sales and are paid as follows: 50% of sales are paid in the samemonth and the remaining 50% are paid in the following month.c. Wages and salaries are as follows:January $30,000February $40,000March $50,000April $50,000May $40,000June $35,000d. Rent is $2,000 per monthe. Interest on $500,000 of 16% bonds is due on the calendar quarter. Hint: you need tocalculate the quarterly interest amount, and this interest will be paid each calendarquarter.f. A tax prepayment of $50,000 is due in April.g. A machinery worth $30,000 will be purchased in June.h. The company has a cash balance of $100,000 on January 1st, which is the minimumbalance maintained each month.1. Prepare cash budget for Jan to June.2. Determine the cash surplus and shortages for each month.


FIN 5130

 

Quiz 5 Extra Credit Instructor: Dr. Palkar Late assignments will not be accepted so please plan accordingly. You need to show

 

your working notes for credit. You must submit your work using excel files (with

 

.xls or .xlsx for credit). You must upload your files on Blackboard under the

 

Assignment Dropbox for credit.

 

This assignment will require you to prepare the cash budget and determine the cash

 

surplus and shortage each month.

 

The management estimates total sales for the period January through July based on actual

 

sales from the immediate past quarter. The following assumptions are made:

 

Sales: Past and Expected

 

Historical Forecast October

 

$300,000 January

 

November $350,000 February

 

December $400,000 March

 

April

 

May

 

June

 

July $150,000

 

$200,000

 

$200,000

 

$300,000

 

$250,000

 

$200,000

 

$300,000 a. Sales are 75% on credit basis and 25% on cash basis. 60% of credit sales are

 

collection one month after sale, 30% in the second month, and 10% in the third

 

month. Bad debts are insignificant.

 

b. Purchases are 80% of sales and are paid as follows: 50% of sales are paid in the same

 

month and the remaining 50% are paid in the following month.

 

c. Wages and salaries are as follows:

 

January

 

February

 

March

 

April

 

May

 

June $30,000

 

$40,000

 

$50,000

 

$50,000

 

$40,000

 

$35,000 d. Rent is $2,000 per month

 

e. Interest on $500,000 of 16% bonds is due on the calendar quarter. Hint: you need to

 

calculate the quarterly interest amount, and this interest will be paid each calendar

 

quarter.

 

f. A tax prepayment of $50,000 is due in April.

 

g. A machinery worth $30,000 will be purchased in June.

 

h. The company has a cash balance of $100,000 on January 1st, which is the minimum

 

balance maintained each month.

 

1. Prepare cash budget for Jan to June.

 

2. Determine the cash surplus and shortages for each month. Page 1 of 1

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 13, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now