MayCeeâ€™s Departmental Store: A Case Study MayCeeâ€™s Departmental Stores Inc. is currently in troubled waters. Competition from online retailers has resulted in less frequent store visits from customers. A recent survey has revealed that customers on an average are also spending less time on the shopping floor. Accurate budgeting is therefore a key element of business success and survival for MayCeeâ€™s. You have just been hired as an intern at MayCeeâ€™s headquarters in Cleveland OH and your first assignment is to help the CFO Ms. Cathy Wilkins prepare a cash budget. At your first meeting with the CFO, you are provided with the following information. Past Sales Nov 2017: $650,000 Dec 2017: $870,000 Projected Sales for Six-Months ending June 2018 are as follows: January February March April May June $350,000 $400,000 $475,000 $650,000 $500,000 $675,000 Sales Receivables Cash Sales: 25% of Net Sales; Payment for 45% of Net Sales are received the following month; Receivables for all other sales are collected after 60 days (i.e., two months). Cost of Goods Sold Cost of Goods Sold is currently estimated at 65% of Net Sales. MayCeeâ€™s pays 20% in cash upfront, 45% next month and for the remaining, it enjoys a Net 60 credit terms. Wages and Salaries Fixed Cost $81,000 and this is applicable for Winter months January and February only. For all other months, the variable wages and salaries (in addition to the fixed cost) are estimated at 25% of difference between the Net Sales for the month and the average sales for January and February. Miscellaneous Fixed Overhead Expenses Store Lease $34,336; Utilities and Water $2,600; Interest on Long-Term Corporate Bonds $11,000. Taxes $5,585 for January; $8,320 for June. Dividends Estimated quarterly dividends in the tune of $19,257 will be paid to shareholders in January and $32,335 in April. Additional Information (i) Ending cash balance for December 2017 is $21,000 (ii) Short-Term Borrowings (i.e., for 30 days), for December 2017 is $52,000 (iii) MayCeeâ€™s borrows money (Short-Term Borrowings, i.e., for 30 days) by issuing Commercial Paper (CP) if the month-end cash balance falls below $14,275. All borrowed funds must be paid off after 30 day along with interest. Interest on MayCeeâ€™s CP is currently estimated at 7.25%. (iv) MayCeeâ€™s invests cash in excess of $43,000 in 30 days Money Market Instruments. Principal along with interest is received after 30 days. Interest on Money Market Instruments are currently estimated at 5.25%.
The CFO wants you to 1. Prepare an estimate of monthly cash balances for the six months ending June 2018. 2. Do a â€œWhat-if Analysisâ€ if projected sales go down by 10% (80) (20)
Show excel functions