r as of January 1st. For which he provides the following: Sales are made 25% cash and 75% on credit. Of the credit sales, 75% is collected one month after the sale, and the remaining 25% two months later.
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)
Empresa Comercial El Europeo, S.A., hires you because of the "good" references of your professor of the Financial Administration course, because he wants to know how much and at what time your cash needs will be manifested in the following semester as of January 1st. For which he provides the following:
Sales are made 25% cash and 75% on credit. Of the credit sales, 75% is collected one month after the sale, and the remaining 25% two months later.
You buy in a given month what you expect to sell the following month. The material cost is 45% of sales. The supplier gives the conditions of 60% cash and the other 40% the month after the purchase.
Provisional tax payments are calculated, considering 1.25% of each month's sales and are paid the month following the month of sale.
It is desired to maintain a minimum of $80,000 in cash; the same amount constitutes the initial balance. Loans are obtained at 2% interest per month. Surplus can be invested in bonds bearing interest at 1.5% per month.
Actual and budgeted sales have been shown in the following table: (It is in the pictures)
Wages and salaries for the period in question will be: (This in the images)
Monthly rent is $60,000 and
Last year's profits amounted to $825,950, the company pays an average of 29.5% ISR, and for the purposes of the annual payment in March, it considers that 90% had already been paid in last year's provisional payments. It also considers that 10% of said profit is distributed to employees on May
The following is requested:
(a) Collection schedule of credit sales.
b) Cash receipts statement.
c) Supplier payment schedule.
d)
e)
f) Financial budget.
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 (RINV)
Cost of sales / average inventory
-Average inventory age
360/inventory turnover
------------------------------------------------------------------
-Accounts Payable Rotation (RCxP)
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
------------------------------------------------------------------
Ok, bartleby expert, now I want the subparts:
d) Cash Outflow Statement.
e) Cash Budget.
f) Financial budget.
(You already answered me a), b) and c) )
:) (don't cancel my question, a bartleby expert has already compromised that he can solve it, I just had to ask the question again to solve the missing subparts.)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
