Simple Accounting Questions (To be answered in Excel)

Answer the attached accounting questions for Week7, Week8, and Mini-case in Excel.

FIN 5130 Mini-case 3
Preparing Cash Budgets
Dr. Palkar
This assignment will require you to prepare the cash budget. Note: You need to use the Cash Budget
template from our handout cash budget comprehensive Cash Budget problem from Chapter 16 and
edit from there to apply the information given in the Mini-case.
The finance manager wants to prepare a cash budget until June 2024. The finance manager has received
the following information from the marketing and operations managers:
•
•
•
•
•
•
•
•
•
The sales in October 2023 were $150,000. The finance department has determined that, based
on the past sales data and near-term market conditions, it may be reasonable to expect that
sales will grow by a modest 1% each month.
The credit department expects:
a. 60% of the sales each month to be collected in the same month.
b. 38% of the sales to the collected in the following month.
c. 2% of the sales will not be collected (bad debts)
The Accounts Department expects the cost of raw materials to be 45% of sales for each month.
The company pays the suppliers as follows:
a. 50% of the purchases are paid in the same month.
b. Remaining 50% of the purchases are paid in the following month.
Wages and Salaries are $60,000 each month and paid in the same month.
Depreciation expense is $25,000 each month.
The company will pay taxes of $45,000 in the month of April.
The company is expected to purchase a new machine worth $50,000 in May 2024. It is expected
to pay for the machine in two equal instalments in May 2024 and in June 2024.
The company pays rent of $12,000 monthly.
The company has an outstanding loan. The company pays an interest of $18,000 in March and
an another $18,000 in September each year.
Questions:
[30 points]
1. Determine the total cash inflows until June 2024.
Show your work in Excel.
[30 points]
2. Determine the total cash outflows until June 2024.
Show your work in Excel.
[ 5 points]
3. Determine the expected change in cash until June 2024.
Show your work in Excel.
[20 points]
4. Assume that the beginning balance in January 2024 is $8,000 and the target
cash balance each month is $10,000.
Determine the excess cash or loan needed for January 2023-June 2023.
Show your work in Excel.
[15 points]
5. Describe in your own words some of the short-term borrowing options that
the company can possibly adopt. You will find the different borrowing options
in our class handout. Please explain using the numbers you calculated in your
Mini-case. Please write your answer on the same excel file, no need to upload
another Word Document file. Keep your work organized and presentable.
Page 1 of 1
Sources and Uses of Funds
Problem Set 1
Determine whether the following is a source or use of fund (also provide your
explanation):
A dividend is paid
2. Issue of short-term debt
3. Long-term debt decreased by $1,000,000
4. Next year’s taxes are prepaid
5. Wages and salaries are paid
6. Accounts receivable balance is reduced
7. Accounts payable balance is reduced
8. Marketable Securities are purchased
9. Marketable Securities are liquidated
10. A bank loan is repaid
1.
Changes in Operating Cycle
Problem set 2
Determine whether the following will INCREASE, DECREASE, or have NO
EFFECT on the operating cycle (also provide your explanation):
Accounts payable goes down
2. Accounts receivable goes up
3. Customers take longer to pay for the goods
4. Payments to suppliers are accelerated
5. Inventory gets sold quickly
6. Accounts Receivables Turnover ratio decreases from 8 times to 6
times
7. Accounts Payable Turnover ratio increases from 6 times to 8 times
8. Inventory Turnover ratio increases from 6 times to 8 times
1.
Changes in Cash Cycle
Problem set 3
Determine whether the following will INCREASE, DECREASE, or have NO
EFFECT on the cash cycle (also provide your explanation):
Accounts payable goes down
2. Accounts receivable goes up
3. Customers take longer to pay for the goods
4. Payments to suppliers are accelerated
5. Inventory gets sold quickly
6. Accounts Receivables Turnover ratio decreases from 8 times to 6
times
7. Accounts Payable Turnover ratio increases from 6 times to 8 times
8. Inventory Turnover ratio increases from 6 times to 8 times
1.
Operating Cycle and Cash Cycle Calculations
Problem set 4
A company has an average collection period (ACP) of 20 days and
AR balance of $100,000. Assume 365 days. Note: ACP is same as AR
Period.
1. What is the Accounts Receivables Turnover (ART)?
2. What is the sales amount?
2. A company has an Accounts Payable period of 25 days and annual
COGS of $300,000. Assume 365 days.
1. What is the Accounts Payable Turnover (APT)?
2. What is the AP balance?
3. ABC Company has an average collection period of 18 days and
annual sales of $694,000.
1. What is the Accounts Receivable Turnover?
2. What is the amount of Accounts Receivable as shown on
the balance sheet?
4. Inventory Turnover Ratio is 5 times, Accounts Receivable Turnover
is 8 times, Account Payable Turnover is 3 times. What is the
Operating Cycle? What is the cash cycle?
1.
5.
Inventory Period is 30 days, AR period is 25 days, AP Period is 10
days. What is the Operating Cycle? What is the Cash Cycle? What is
the Inventory Turnover Ratio? Accounts Receivable Turnover?
What is the Account Payable Turnover?
Also upload your excel files showing your work.
Calculating Cash Collections
Problem set 5
ABC Corporation has the following sales collection policy: 15% of
the sales are collected immediately. 40% of the sales are received in
the first month after the sale. 20% of the sales are received in the
second month after the sale. 15% of the sales are received in the
third month of after the sale. 6% of the sales are received in the
fourth month after the sale. The remainder are bad debts and not
collected. Suppose the company has the following expected sales:
Jan $50,000, Feb $60,000, March $70,000, April $45,000, May
$66,000. Determine the cash collections for May.
2. ABC Corporation has the following sales collection policy: 25% of
the sales are received in the same month, 50% of the sales are
received in the first month after the sale. 20% of the sales are
received in the second month after the sale. The remainder are
received in the third month after the sale. Suppose the company has
the following expected sales: Jan $50,000, Feb $60,000, March
$70,000, April $45,000, May $66,000. Determine the cash
collections for May.
1.
Also upload your excel files showing your work.
Effective Rate
Problem set 6
1.
The terms of trade are 2/15 net 60. What is the APR? What is the
effective rate of forgoing the cash discount.
2.
The terms of the sale were 1/7, net 21. What is the APR? What is the
effective rate of forgoing the cash discount.
Also upload your excel files showing your work.
Inventory Management
Problem set 7
ABC Furniture sells 2,400 sofas a year at an average price per sofa of $1,250.
The carrying cost per unit is $11.60. The company orders 80 sofas at a time
and has a fixed order cost of $52 per order. The sofas are sold out before
they are restocked.
What are the total number of orders in a year?
2. What is the total carrying cost?
3. What is the total restocking cost? Note: restocking cost is same as
shortage cost.
4. What are the total costs?
5. Should the company increase or decrease its number of orders and
the quantity it orders each time?
6. What is the economic order quantity (EOQ)?
7. How many orders will company place if it follows the EOQ model?
8. What are the total carrying costs if the company follows the EOQ
model?
9. What are the total shortage costs if the company follows the EOQ
model?
10. What are the total costs if the company follows the EOQ model?
1.
Also upload your excel files showing your work.
Cross-rates
Practice set 1
Suppose the exchange rate between U.S. dollars and Swiss francs is
CHF 1.41 = USD 1.00, and the exchange rate between the U.S.
dollar and the euro is USD 1.00 = EUR 1.64. Solve for:
1. CHF/EUR
2. EUR/CHF
2. Suppose that 1 British pound currently equals 1.62 U.S. dollars and
1 U.S. dollar equals 1.62 Swiss francs. What is the cross rate of Swiss
francs to British pound?
3. A trader observes that, in the spot exchange market, $1 can be
exchanged for 9 Mexican pesos or for 111.23 Japanese yen. What is
the cross-rate between the yen and the peso, that is, how many yen
would you receive for every peso exchanged?
1.
Also upload your excel files showing your work.
Currency Conversion
Pracice set 2
1.
You are just back from a summer trip to Norway. Suppose you got
back 350 krones with you. If you convert your krones to dollars,
how many dollars would you have, if the exchange rate is 1 krone =
0.0947 dollars?
Your friend from Chile plans to visit you after your final exams.
Suppose you estimate the cost of her trip to be $1,500. How many
Chilean pesos would your friend need if the exchange rate is 1 CLP=
0.0012 USD?
3. Suppose you plan to visit Japan in December. You think you will
need 150,000 yen for your trip. How many dollars do you need if
one Japanese Yen is worth $0.0088.
2.
Arbitrage Gain
Practice set 3
Suppose the Japanese yen exchange rate is ¥106 = $1 and the
British Pound exchange rate is £1 = $1.51. Suppose the cross-rate is
quoted as ¥165 = £1. Is there an arbitrage opportunity? If yes,
calculate the arbitrage profit on $20,000.
2. Suppose the Swiss Franc exchange rate is CHF 2.00 = USD 1.00
and the British Pound exchange rate is GBP 0.60 = USD
1.00. Suppose the cross-rate is quoted as 3 CHF = 1 GBP. Is
there an arbitrage opportunity? If yes, calculate the arbitrage profit
on $5,000.
1.
Also upload your excel files showing your work.