PLEASE, PERFORM THE EXERCISE IN EXCEL AND SHOW THE FORMULAS PLEASE, PERFORM THE EXERCISE IN EXCEL AND SHOW THE FORMULAS PLEASE, PERFORM THE EXERCISE IN EXCEL AND SHOW THE FORMULAS (Not written.. please) 2) The company La Planetaria, S.A., presents you with the following income statement and additional information, so that you can support it with the calculations to make a decision on the planning of its cash management. La Planetaria, S.A. Income Statement 2020 Sales.................................................... $35'112,356.84 Cost of Sales.................................. 18'960,672.69 Gross Profit.....
PLEASE, PERFORM THE EXERCISE IN EXCEL AND SHOW THE FORMULAS
PLEASE, PERFORM THE EXERCISE IN EXCEL AND SHOW THE FORMULAS
PLEASE, PERFORM THE EXERCISE IN EXCEL AND SHOW THE FORMULAS
(Not written.. please)
2) The company La Planetaria, S.A., presents you with the following income statement and additional information, so that you can support it with the calculations to make a decision on the planning of its cash management.
La Planetaria, S.A.
Income Statement 2020
Sales.................................................... $35'112,356.84
Cost of Sales.................................. 18'960,672.69
Gross Profit....................................... $16'151,684.15
Operating Expenses.......................... 7'258,741.36
Operating Income......................... $ 8'892,942.79
Financial Expenses.............................. 2'940,168.18
UAIR(Integrated Risk Management Unit)........................................................ $5'952,774.61
In addition, the manager of La Planetaria, S.A., Mr. Gustavo Fiambres, reports that this company only sells on credit, and that its average customer balance is $829,041.76. The current accounts payable turnover is 8 times a year. The average inventory is $1'580,056.06.
They plan to make modifications to their policies so that they manage to decrease their PPC by 2.35 times, which will increase their days, but with inventories there is an opposite effect, their EPI increases by 5 days. On the positive side, you have successfully negotiated with your supplier to extend your payment terms by 10 days. With this data you have to calculate:
1) The operating cycle.
2) The cash conversion cycle.
3) The cash turnover.
4) The minimum cash balance.
5) Re-calculate the Operating Cycle, the CCE, RC and SMC by introducing the proposed changes.
6) Calculate the opportunity cost that the changes will cause, if the company's interest rate is 8½ %.
Very important Note:
It is necessary that you make a solution approach and then the result. Above all, to check the procedure and/or the formulas used, especially when you use excel.
-THERE IS AN EXAMPLE OF EXCEL FORMATTING (IN THE PICTURE) , I WANT YOU TO USE A SIMILAR FORMAT. (it is in Spanish, but it is easy to understand)
----------------------------
Formulas (those given to me by the professor, this to guide you as a bartleby expert.)
-
Sales (on credit) / Average customer balance.
-Average collection period:
360 /accounts receivable turnover
------------------------------------------------------------------
-Inventory turnover
Cost of sales / average inventory
-Average inventory age
360/inventory turnover
------------------------------------------------------------------
-Accounts Payable Rotation
STEP ONE: Determine Ending Inventory
STEP TWO: Determine Purchases
STEP THREE:
-Calculate accounts payable turnover.
Purchases / Average supplier balance
-Average payment period
360/accounts payable turnover
------------------------------------------------------------------
Operating Cycle = Average age of inventory + Average collection period
Cash Conversion Cycle = Average payable period - Cash cycle
Cash Turnover = 360 / Cash Conversion Cycle
Minimum Cash Balance = Annual Expense / Cash Turnover
------------------------------------------------------------------
Step by step
Solved in 2 steps
