Question
Using EXCEL This assignment will require you to prepare the cash budget and determine the cash surplus and shortage each month. The management estimates total
Using EXCEL
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 June based on
actual sales from the immediate past six months. The following assumptions are made:
Historical Sales Forecasted Sales
July, 2016 $100,000January, 2017 $110,000
August, 2016 $125,000 February, 2017 $ 90,000
September, 2016 $105,000 March, 2017 $120,000
October, 2016 $155,000 April, 2017 $ 80,000
November, 2016 $ 60,000 May, 2017 $ 70,000
December, 2016 $ 70,000 June, 2017 $ 60,000
a. 50% of the Sales are collected immediately. 30% of the Sales will be collected one
month after the sale. 10% will be collected two months after the sale. 6% will be
collected three months after the sale. The remainder will be collected four months
after the sale. Bad debts are insignificant.
b. Purchases were $50,000 in July, 2016 and are expected to grow by 2% each month.
The purchases will be paid in the same month.
c. Wages and salaries of $30,000 will be paid each month.
d. Depreciation expenses are $20,000 each month.
e. Rent of $15,000 will be paid at the end of each calendar quarter.
f. Machinery worth $150,000 will be purchased in March.
Prepare the cash budget from January to June. Determine the cash surplus and
shortages for each month from January to June. Provide your analysis.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started