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) 1) La Metropolitana, S.A., has trained its personnel with courses at a renowned private university in the northwest, in order to be up to date in the planning of its financial resources. It knows that, in order to know the necessary rotations to quantify its cash cycle, it is indispensable to apply some financial ratios, studied by its personnel and that it cost a lot of money to do so, so it does not expect an "I don't know" for an answer. So how often you pay your suppliers, how often you rotate your inventories, and how often you charge your customers, will have to be calculated with the following data: Total company sales..........................$9'365,199.36 Proportion of credit sales.......................      73.¾ % Average customer balance............................. $ 767,426.06 Cost of sales ratio......................       67½ % Average inventory balance...................... $1'053,584.93 Average suppliers' balance................... $ 896,884.26 Beginning inventory................................................ $ 985,556.36 The company spends about $15.525 million in operating cycle investments. With this data it is necessary to calculate: 1) The operating cycle. 2) The cash conversion cycle. 3) The cash turnover. 4) The minimum cash balance. Note: In the image, this is the original exercise, it is in Spanish, but it is easy to understand. 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. please... understand the context of the exercise in Spanish, it is easy to understand -THERE IS AN EXAMPLE OF EXCEL FORMATTING (IN THE PICTURE) , I WANT YOU TO USE A SIMILAR FORMAT. ---------------------------- Formulas (those given to me by the professor, this to guide you as a bartleby expert.) -Accounts receivable turnover  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

FINANCIAL ACCOUNTING
10th Edition
ISBN:9781259964947
Author:Libby
Publisher:Libby
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
icon
Related questions
Question

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)
1) La Metropolitana, S.A., has trained its personnel with courses at a renowned private university in the northwest, in order to be up to date in the planning of its financial resources. It knows that, in order to know the necessary rotations to quantify its cash cycle, it is indispensable to apply some financial ratios, studied by its personnel and that it cost a lot of money to do so, so it does not expect an "I don't know" for an answer. So how often you pay your suppliers, how often you rotate your inventories, and how often you charge your customers, will have to be calculated with the following data:

Total company sales..........................$9'365,199.36
Proportion of credit sales.......................      73.¾ %
Average customer balance............................. $ 767,426.06
Cost of sales ratio......................       67½ %
Average inventory balance...................... $1'053,584.93
Average suppliers' balance................... $ 896,884.26
Beginning inventory................................................ $ 985,556.36

The company spends about $15.525 million in operating cycle investments. With this data it is necessary to calculate:

1) The operating cycle.
2) The cash conversion cycle.
3) The cash turnover.
4) The minimum cash balance.

Note:
In the image, this is the original exercise, it is in Spanish, but it is easy to understand.

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.

please... understand the context of the exercise in Spanish, it is easy to understand

-THERE IS AN EXAMPLE OF EXCEL FORMATTING (IN THE PICTURE) , I WANT YOU TO USE A SIMILAR FORMAT.

----------------------------
Formulas (those given to me by the professor, this to guide you as a bartleby expert.)
-Accounts receivable turnover 
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
------------------------------------------------------------------

CICLO DE CAJA
CC2) La Reina de Oriente S.A.
DATOS:
Ventas Totales
Proporción a crédito
Saldo promedio Clientes
Saldo promedio Inventarios
Gastos anuales
Inventario Inicial
CÁLCULOS PREVIOS:
Rotación CxC
Rotación Inventarios
Rotación CxP
Costo de Ventas
Compras
Ventas a crédito
Edad Promedio Inventario
Período Promedio Cobro
Período Promedio Pago
Inventario Final
Saldo promedio Proveedor
CÁLCULOS:
1) Ciclo Operativo
2) Ciclo Conversión Efectivo
3) Rotación de Caja
dias
días
veces
4) Saldo mínimo en caja
Transcribed Image Text:CICLO DE CAJA CC2) La Reina de Oriente S.A. DATOS: Ventas Totales Proporción a crédito Saldo promedio Clientes Saldo promedio Inventarios Gastos anuales Inventario Inicial CÁLCULOS PREVIOS: Rotación CxC Rotación Inventarios Rotación CxP Costo de Ventas Compras Ventas a crédito Edad Promedio Inventario Período Promedio Cobro Período Promedio Pago Inventario Final Saldo promedio Proveedor CÁLCULOS: 1) Ciclo Operativo 2) Ciclo Conversión Efectivo 3) Rotación de Caja dias días veces 4) Saldo mínimo en caja
1) La Metropolitana, S.A., ha capacitado a su personal con cursos en
afamada universidad privada del noroeste, para estar al día en la
planeación de sus recursos financieros. Sabe que, para conocer las
rotaciones necesarias para cuantificar su ciclo de caja, es indispensable
aplicar algunas razones financieras, estudiadas por su personal y que le
costó una lana que lo hiciera, por lo que no espera un "no sé" por
respuesta. Así que cada cuánto paga a sus proveedores, cada cuánto
rotan sus inventarios, y cada cuánto cobra a sus clientes, habrá que
calcularlo con los siguientes datos:
Ventas totales de la empresa.
Proporción de ventas a crédito..
Saldo promedio de clientes.
Proporción del costo de ventas..
Saldo promedio de inventarios..
Saldo promedio de proveedores..
Inventario Inicial .
.$9'365,199.36
73.% %
$ 767,426.06
672 %
$1'053,584.93
$ 896,884.26
$ 985,556.36
La empresa gasta alrededor de $15.525 millones en inversiones del
ciclo operativo. Con estos datos hay que calcular:
1) El ciclo operativo.
2) El ciclo de conversión de efectivo.
3) La rotación de caja.
4) El saldo mínimo de caja.
Transcribed Image Text:1) La Metropolitana, S.A., ha capacitado a su personal con cursos en afamada universidad privada del noroeste, para estar al día en la planeación de sus recursos financieros. Sabe que, para conocer las rotaciones necesarias para cuantificar su ciclo de caja, es indispensable aplicar algunas razones financieras, estudiadas por su personal y que le costó una lana que lo hiciera, por lo que no espera un "no sé" por respuesta. Así que cada cuánto paga a sus proveedores, cada cuánto rotan sus inventarios, y cada cuánto cobra a sus clientes, habrá que calcularlo con los siguientes datos: Ventas totales de la empresa. Proporción de ventas a crédito.. Saldo promedio de clientes. Proporción del costo de ventas.. Saldo promedio de inventarios.. Saldo promedio de proveedores.. Inventario Inicial . .$9'365,199.36 73.% % $ 767,426.06 672 % $1'053,584.93 $ 896,884.26 $ 985,556.36 La empresa gasta alrededor de $15.525 millones en inversiones del ciclo operativo. Con estos datos hay que calcular: 1) El ciclo operativo. 2) El ciclo de conversión de efectivo. 3) La rotación de caja. 4) El saldo mínimo de caja.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 2 images

Blurred answer
Knowledge Booster
Conceptual framework for financial reporting
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
Accounting
ISBN:
9781259964947
Author:
Libby
Publisher:
MCG
Accounting
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Horngren's Cost Accounting: A Managerial Emphasis…
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
Intermediate Accounting
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
Financial and Managerial Accounting
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education