Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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.

image text in transcribed 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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Investing In Financial Research A Decision Making System For Better Results

Authors: Cheryl Strauss Einhorn, Tony Blair

1st Edition

1501732757, 9781501732751

More Books

Students also viewed these Finance questions