PLEASE EXPLAIN IN EXCEL STEP BY STEP

1. Eastern Auto Parts Inc. has 25 percent of its sales paid for in cash and 75 percent on credit. All credit accounts are collected in the following month. Assume the following sales: (15 pts] January $5000 February $5000 March $10000 April $15000 May $100000 June $200000 Sale in December of the prior year were $10,000. Complete the table below using the assumptions and instructions in the following steps. (a) Prepare a cash receipt schedule (b) Assume further that the items are sold for $20 each and that a level production schedule will be used over the next 6 months. Generate a monthly production schedule. (c) Assume that at the start of January Eastern Auto Parts had inventory schedule. Assume they started with 0 units. (d) Determine the cash payment schedule. Assume the production cost is $10 per item. (e) Determine the months (if any) for which there the cash balance is below zero (assume we start off with 0 cash). 2. Complete the following table for the financial year ending in Dec 30, 2017 using finan- cial statements from Office Depot (5 points) Ratio Average Inventory Average Receivables Average Payables daily Sales daily COGS daily Purchases Use the information you computed above to complete the table: value (days) DIO DSO DPO CCC If the industry average Cash conversion cycle is 40 days (using a threshold of 2 days) Which of the following is true: (a) The firm has superior cash conversion cycle (b) The firm has inferior cash conversion cycle (c) The firm has an average cash conversion cycle 1. Eastern Auto Parts Inc. has 25 percent of its sales paid for in cash and 75 percent on credit. All credit accounts are collected in the following month. Assume the following sales: (15 pts] January $5000 February $5000 March $10000 April $15000 May $100000 June $200000 Sale in December of the prior year were $10,000. Complete the table below using the assumptions and instructions in the following steps. (a) Prepare a cash receipt schedule (b) Assume further that the items are sold for $20 each and that a level production schedule will be used over the next 6 months. Generate a monthly production schedule. (c) Assume that at the start of January Eastern Auto Parts had inventory schedule. Assume they started with 0 units. (d) Determine the cash payment schedule. Assume the production cost is $10 per item. (e) Determine the months (if any) for which there the cash balance is below zero (assume we start off with 0 cash). 2. Complete the following table for the financial year ending in Dec 30, 2017 using finan- cial statements from Office Depot (5 points) Ratio Average Inventory Average Receivables Average Payables daily Sales daily COGS daily Purchases Use the information you computed above to complete the table: value (days) DIO DSO DPO CCC If the industry average Cash conversion cycle is 40 days (using a threshold of 2 days) Which of the following is true: (a) The firm has superior cash conversion cycle (b) The firm has inferior cash conversion cycle (c) The firm has an average cash conversion cycle