Question
In: Accounting
The finance manager wants to prepare a cash budget for the July, 2020 through December, 2020...
The finance manager wants to prepare a cash budget for the July,
2020 through December, 2020 period.
The finance manager has received the following information from the
marketing and operations
managers:
• The Sales were $140,000 in January, 2020 and then the sales grew
by 2% each month in the first
three months (i.e., from February to April 2020) and by 5% in the
next two months (i.e., in May
and June 2020). The sales are expected to grow by 1% each month
thereafter.
• 45% of the Sales are collected in the same month. 30% of the
sales are collected in the following
month. 24% of the sales are collected after two months and the
remainder are not collected.
• The Purchases are 80% of each month’s sales and paid in the same
month.
• Wages and Salaries are $25,000 each month and paid in the same
month.
• Other administrative expenses are $15,000 and paid in the same
month.
• Depreciation expense is $5,000 each month.
• An electrical device worth $30,000 will be purchased in October
2020. 50% of the amount due
will be paid immediately and the balance will be paid in November,
2020.
• The company had previously taken a loan of $200,000. The annual
interest rate on the loan
amount is 4%. The interest is paid once a year in December each
year. Assume that no principal
repayments are made in this period, only interest payments are
made.
• The company pays rent of $3,500 quarterly (in March, June,
September, and December each
year).
1. Determine the total cash inflows for each month from July
2020 to December 2020.
Show your work in Excel.
2. Determine the total cash outflows for each month from July
2020 to December 2020.
Show your work in Excel.
3. Determine the expected change in cash for each month from
July 2020 to December 2020.
Show your work in Excel.
4. Describe in your own words some of the short-term borrowing
options that the company may adopt.
Solutions
Expert Solution
| January | February | March | April | May | June | July | August | September | October | November | December | ||||
| Total Sales | 140000 | =B2+(B2*2%) | =C2+(C2*2%) | =D2+(D2*2%) | =E2+(E2*5%) | =F2+(F2*5%) | =G2+(G2*1%) | =H2+(H2*1%) | =I2+(I2*1%) | =J2+(J2*1%) | =K2+(K2*1%) | =L2+(L2*1%) | |||
| Cash Inflows: | |||||||||||||||
| 45% | =H2*45% | =I2*45% | =J2*45% | =K2*45% | =L2*45% | =M2*45% | |||||||||
| 30% | =G2*30% | =H2*30% | =I2*30% | =J2*30% | =K2*30% | =L2*30% | |||||||||
| 24% | =F2*24% | =G2*24% | =H2*24% | =I2*24% | =J2*24% | =K2*24% | |||||||||
| Total Cash INFLOWS | =SUM(H4:H6) | =SUM(I4:I6) | =SUM(J4:J6) | =SUM(K4:K6) | =SUM(L4:L6) | =SUM(M4:M6) | |||||||||
| Cash Outflows: | |||||||||||||||
| Purchases (80% of sales) | =H2*80% | =I2*80% | =J2*80% | =K2*80% | =L2*80% | =M2*80% | |||||||||
| Wages & Salaries | 25000 | 25000 | 25000 | 25000 | 25000 | 25000 | |||||||||
| Other Admin Expenses | 15000 | 15000 | 15000 | 15000 | 15000 | 15000 | |||||||||
| Electrical device | 0 | 0 | 0 | =30000*50% | =K14 | 0 | |||||||||
| Interest on loan (200000*4%) | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||
| Rent | 0 | 0 | 3500 | 0 | 0 | 3500 | |||||||||
| Total Cash OUTFLOWS | =SUM(H11:H16) | =SUM(I11:I16) | =SUM(J11:J16) | =SUM(K11:K16) | =SUM(L11:L16) | =SUM(M11:M16) | |||||||||
| Change in cash | =H8-H18 | =I8-I18 | =J8-J18 | =K8-K18 | =L8-L18 | =M8-M18 |
Final Answers would look like this :
 ekkarill92 answered 1 year agoRelated SolutionsLamia's House of Software (LHS) wants to prepare a cash budget for months of September through...
Lamia's House of Software (LHS) wants to prepare a cash budget
for months of
September through Dêcember. Using the following
information,
Sales were $50,000 in June and $60,000 in July. Sales have
been
forecasted to be $65,000, $72,000, $63,000, $59,000, and
$56,000 for months
of August, September, October, November, and December,
respectively. In the
past, 10 percent of sales were on cash basis, and the
collection were 50
percent in the first month, 30 percent in the second month,
and...
Lamia's House of Software (LHS) wants to prepare a cash budget for months of September through...
Lamia's House of Software (LHS) wants to prepare a cash budget
for months of
September through Dêcember. Using the following
information,
Sales were $50,000 in June and $60,000 in July. Sales have
been
forecasted to be $65,000, $72,000, $63,000, $59,000, and
$56,000 for months
of August, September, October, November, and December,
respectively. In the
past, 10 percent of sales were on cash basis, and the
collection were 50
percent in the first month, 30 percent in the second month,
and...
Lamia's House of Software (LHS) wants to prepare a cash budget for months of September through...
Lamia's House of Software (LHS) wants to prepare a cash budget
for months of September through December. Using the following
information,
∙ Sales were $50,000 in June and $60,000 in July. Sales have
been forecasted to be $65,000, $72,000, $63,000, $59,000, and
$56,000 for months of August, September, October, November, and
December, respectively. In the past, 10 percent of sales were on
cash basis, and the collection were 50 percent in the first month,
30 percent in the second month,...
Use the following information to prepare a cash budget for December. The cash balance on December...Use the following
information to prepare a cash budget for December.
The cash balance on
December 1 is $53,400.
Actual sales for
October and November and expected sales for December are as
follows:
October
November
December
Cash
sales
$
77,000
$
81,200
$
87,800
Sales on
account
$
435,000
$
538,000
$
644,000
Sales on account are
collected over a three-month period as follows: 20% collected in
the month of sale, 60% collected in the month following sale, and
18%...
. Prepare a cash budget for January through March and for the first quarter in...
. Prepare a cash
budget for January through March and for the first quarter in
total. The company maintains a minimum cash balance of $70,000, and
this was the balance in the cash account on January 1st.
Other expenses include $35,000 per month for rent, $24,000 per
month for advertising, and $66,000 per month for depreciation. In
addition, variable Selling & Administrative cost is $12 per
unit sold, and the company paid a $20,000 dividend in February.
The company...
.The government of Kenya through the Minister of Finance presented the 2020 Budget statement to Parliament....The government of Kenya through the Minister of Finance
presented the 2020 Budget statement to Parliament in November
2019.The Coronavirus Disease 2019 (COVID -19) pandemic that has hit
the world has impacted on global economy including Kenya, thus
affecting our macroeconomic targets in the budget statement
presented in November 2019. The Minister of Finance presented a
statement to Parliament on the economic impact of COVID – 19
pandemic on the economy of Kenya and the way forward at the end...
The government of Ghana through the Minister of Finance presented the 2020 Budget statement to Parliament...
The government of Ghana through the Minister of Finance
presented the 2020 Budget statement to Parliament in November
2019.The Coronavirus Disease 2019 (COVID -19) pandemic that has hit
the world has impacted on global economy including Ghana, thus
affecting our macroeconomic targets in the budget statement
presented in November 2019. The Minister of Finance presented a
statement to Parliament on the economic impact of COVID – 19
pandemic on the economy of Ghana and the way forward at the end...
The government of Ghana through the Minister of Finance presented the 2020 Budget statement to Parliament...The government of Ghana through the Minister of Finance
presented the 2020 Budget statement to Parliament in November
2019.The Coronavirus Disease 2019 (COVID -19) pandemic that has hit
the world has impacted on global economy including Ghana, thus
affecting our macroeconomic targets in the budget statement
presented in November 2019. The Minister of Finance presented a
statement to Parliament on the economic impact of COVID – 19
pandemic on the economy of Ghana and the way forward at the end...
The government of Ghana through the Minister of Finance presented the 2020 Budget statement to Parliament...The government of Ghana through the Minister of Finance
presented the 2020 Budget statement to Parliament in November
2019.The Coronavirus Disease 2019 (COVID -19) pandemic that has hit
the world has impacted on global economy including Ghana, thus
affecting our macroeconomic targets in the budget statement
presented in November 2019. The Minister of Finance presented a
statement to Parliament on the economic impact of COVID – 19
pandemic on the economy of Ghana and the way forward at the end...
The government of Ghana through the Minister of Finance presented the 2020 Budget statement to Parliament...
The government of Ghana through the Minister of Finance presented
the 2020 Budget statement to Parliament
in November 2019.The Coronavirus Disease 2019 (COVID -19) pandemic
that has hit the world has impacted
on global economy including Ghana, thus affecting our macroeconomic
targets in the budget statement
presented in November 2019. The Minister of Finance presented a
statement to Parliament on the economic
impact of COVID – 19 pandemic on the economy of Ghana and the way
forward at the end...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
ADVERTISEMENT
|