I need help preparing question 1 which is preparing the cashflow worksheet (Seen in Exhibit 4) .. I've attached the case with this. Thank you so much!

Menotomy Diaper Services In November 2009, Ms. Carolyn Ringer, Treasurer of Menotomy Diaper Services (MDS), was prepar- ing some forecasts in conjunction with a loan request to the Norten Municipal Bank. Less than 8 months earlier, MDS had expanded its staff by 25 percent. At that time, the company had been in a secure financial position, and Ms. Ringer, anticipating no financial problems, had spoken to her board of directors in favor of the increase in services. However, in July, she realized that although MDS had begun training and pay- ing the new staff in June, the company would not receive the additional revenue they generated until later in the year. With the expansion in both full and part-time staff, she realized that the company might not have the required amount of cash on hand. Faced with this situation, Ms. Ringer had gone to the bank to apply for a short-term line of credit to cover the additional costs. Mr. Jansen, the bank officer, had granted MDS a line of credit, which had reached over $620,000 by September 30, 2009. Although he had allowed the company to continue using its line of credit into its next fiscal year (which ran from October 1 to September 30) he had raised some im- portant considerations. He explained that the bank was willing to continue a line of credit for the company but he was con- cerned that Ms. Ringer had not adequately anticipated the company's cash needs. He asked Ms. Ringer to present the bank at the outset with a detailed monthly statement of MDS's projected cash needs for fiscal year 2010. Consequently, Ms. Ringer began to review the company's financial statements (contained in Ex- hibits 1 and 2) and collect data that would help her plan for MDS's future cash requirements. BACKGROUND Menotomy Diaper Services was founded several years ago by four retired nurses. Each founder had between 10 and 35 years of experience working with infants in Norten's hospitals. Realizing that parents increasingly were using cloth diapers, the nurses saw a need for a diaper service, and started their own company. At first, MDS was small and operated out of one person's home. The company then employed the four founders, a part-time laundry worker, and three drivers. Within a few years, the organization had outgrown its "office space" as well as its organizational structure. Moreover, demand for the company's services had risen at an average rate of 10 percent per year, and forecasts at that time indicated a steady increase at the 10 percent level. As a result, MDS underwent a gradual expansion. They purchased a small building in downtown Norten for management offices, as well as laundry facilities with ample parking for the company's delivery trucks. They also bought some of the vehicles and equipment they had previously rented. In the subsequent 10 years, the staff doubled. A few years later, in conjunction with increased use of cloth diapers by environmentally concerned par- ents, MDS was again faced with an increasing demand for its services. A poll taken at Norten's three hos- pitals in March 2009 indicated that there would be a 20 percent increase in the use of cloth diapers. MDS management estimated that net revenue would reach $8,100,000 in FY 2010, an increase of 16 percent over the 2009 level. Further growth of between $750,000 and $1,000,000 a year was expected during the next three years. Consequently, MDS was forced to decide whether to hold staff and services steady and take a risk that other diaper services would fill in the gaps or to launch an expansion program aimed at providing more services to a greater number of families in the Norten area. After examining MDS's financial position and talking to Norten's staff and customers about the company's services, the board voted overwhelmingly to expand. to illustrate either DATA Demand for MDS's services, like that for many other cloth diaper agencies, was seasonal. Over two thirds of the company's annual sales were made during the late fall, winter, and spring months. During summer, when people were on vacation, they shifted from cloth to disposable diapers. Exhibit 3 contains the forecasted monthly revenue for FY 2010. In making these projections, Ms. Ringer took into account the In terms of expenses, disbursements related to overhead and administration were $150,000 a month throughout FY 2010. Included in that figure was Ms. Ringer's estimate of the monthly interest on the Nor- ten Municipal Bank line of credit. Contract service expenses were forecast at $9,000 a month, and were paid out each month. By contrast, supply purchases were projected at $34 200 per month; the company's policy was to charge these items and pay for each month's purchases in the following month. Employees were paid on the first of each month for carnings from the previous month. Starting in Oc- tober, salaries and wages were expected to be $412,500 a month, except for June, when many employees would be on vacation and some part-time wages would be eliminated; payments in July would thus de- crease to about $300,000. Otherwise, although demand for the company's services was somewhat seasonal, MDS's salaries and benefits were steady throughout the year. Ms. Ringer believed that this policy of regu- lar employment for its employees enabled it to maintain a highly skilled and committed staff, which in turn contributed significantly to the company's reputation for quality service. The initial depreciation expense was forecasted to be $330,000. In addition, the company was the lessee on several long-term leases of furniture, equipment, and automobiles, with terms ranging from 3 to 5 years. The payments on these leases totaled $225,000, payable in equal installments in December and June. Because of some difficulties in making timely tax payments in the past, MDS was required by the IRS to make monthly tax payments, which were projected to be $15,000 a month. However, she expected that MDS would be in a 50 percent tax bracket, and thus, as of the end of the year, might be owed for some overpayments. Some new delivery trucks were due for delivery in December. The trucks cost $180,000 and would be paid for in four equal monthly installments, beginning on delivery. The vehicles had an economic life of four years, at the end of which their salvage value would be zero. Depreciation would begin in the month of delivery, and would be added to the above $330,000 amount. Three years ago, MDS had borrowed $1,800,000 from a life insurance company under a 16-year note secured by the company's property and equipment. The current portion of the note, and the related interest payments, were made in equal installments in March and September of each year. In preparing her financial forecasts for 2010, Ms. Ringer planned to show separately the two principal payments, totaling $112,500, and the two interest payments, totaling $139,220. In FY 2010, MDS would need to repay George Wilson, one of its board members, $290,700 for an interest-free loan that he had made to the company a few yeas ago. The payments would be in equal install- ments in December and March. The only other cash outflow Ms. Ringer anticipated was $140,860 due to an equipment manufacturer. The sum payable was non-interest-bearing and due in equal installments in June and September. She did not expect to incur a liability of this sort during FY 2010. Inventory, prepaid expenses, other assets, and contributed capital were expected to remain essentially unchanged during FY 2010. Given the company's growth plans, Ms. Ringer did not anticipate making any dividend payments for 2010. Although Mr. Jansen had not hesitated to grant MDS a line of credit, he had stressed the importance of the bank's credit regulations. For a line of credit of $2,000,000 or less, the bank required the borrower to maintain a compensating cash balance of $200,000 at all times. Also, the entire line of credit, including the $654,000 currently owed by MDS, was to be completely liquidated for at least one month during the year. Using the worksheet in Exhibit 4, Ms. Ringer began to prepare a monthly cash flow forecast for FY 2010 that, she hoped, would show how much money the company would need on a monthly basis. Assignment 1. Prepare the cash flow worksheet contained in Exhibit 4. What does this tell you about the operations of MDS? 2. Prepare pro forma financial statements for FY 2010 (including a statement of cash flows). What do these tell you about the operations of MDS? How can you reconcile the information in the cash flow worksheet with that in the financial Calculate those ratios that might be important to the bank for each year in the 4-year period. What do these ratios tell What changes in financing policies took place in the most recent year as compared to previous years? What recommendations would you make to Ms. Ringer? statements? 3. you about MDS? 4. 5. MENOTOMY DIAPER SERVICES Exhibit 1. Balance Sheets As of September 30, 2007-2009 2007 2008 2002 Assets Cash Accounts receivable Inventory Prepaid expenses Total current assets Property and equipment (net) Other assets Total assets $1,434,600 526,500 266,220 321,180 $2,548,500 $ 328,050 581,400 307,800 343,030 $1,560,280 $ 200,000 681,000 399,600 415,850 $1,696,450 3,153,450 135.900 $5,837,850 4,117,350 159,750 $5,837,380 4,689,460 166,500 $6,552,410 Liabilities and Equity Line of credit (overdraft) Accounts payable Salaries and benefits payable Due to equipment manufac rer Loan payable (G. Wilson) Current portion of note payable Total current liabilities $ 0 27,450 310,500 120,600 212,400 112,500 $ 783,450 $ 0 35,420 324,000 131,530 262,350 112.500 $ 865,800 $ 625,400 29,250 351,000 140,860 290,700 112.500 $1,549,710 Note payable (non-current) Total liabilities 1,687,500 $2,470,950 1,575,000 $2,440,800 1,462,500 $3,012,210 Contributed capital Retained earnings Total liabilities and equity 2,700,000 666,900 $5,837,850 2,700,000 696,580 $5,837,380 2,700,000 840,200 $6,552,410 Exhibit 2. Operating Statements Fiscal Years 2007-2009 2007 2008 2009 $6,494,620 $7,224,930 $8,385,240 1.148.920 1302.780 1400 340 $5,345,700 $5,922,150 $6,984,900 Gross client revenue Less: bad debt expense Net client revenue Operating expenses: Salary and benefits Overhead and administration Supplies Contract services Equipment rental Depreciation Total operating expenses Income from operations Less: interest expense Income before tax Less: income tax Net income $3,726,000 631,290 244,770 81,230 172,500 225,000 $5,080,790 $3,888,000 993,880 279,670 86,400 187,500 285,000 $5,720,450 $ 201,700 159.470 $42.230 12.550 $ 29,680 $4,212,000 1,363,100 344,710 102,300 196,500 330.000 $6,548,610 $264,910 168,350 $96,560 30.580 $65,980 $436,290 149 350 $286,940 143.320 $ 143,620 Exhibit 3. Estimated Monthly Revenue and Month-End Accounts Receivable For FY 2010 Gross Net Accounts Client Client Receivable Revenue Revenue* End-of-Month* October $ 675,000 $ 558,000 $1,174,500 November 960,000 787,500 1,480 500 December 1,356,000 1,111,500 2,128,500 January 1.399,500 1,147,500 2,574,000 February 1,564,500 1,282,500 2,781,000 March 883,500 724,500 2,358,000 April 735,000 603,000 1,431,000 May 613,500 504,000 1,174,500 June 534,000 438.000 765,000 July 328,500 270,000 595,500 345,000 August 283,500 600,000 475 500 390.000 897,000 September $9,870,000 $8,100,000 * After adjustments for bad debts. MENOTOMY DIAPER SERVICES Exhibit 4. Cash Flow Worksheet Nov. Dec. Jan. Feb. Mar. Oct. Apr. May June July Aug. Sep. CASH IN CASH OUT Salary & Benefits O/H & Administration Payment of A/P Contract Services Payments to IRS Truck Expenditure Payment of loan Equip. Leases Note principal Note interest Equipment mfturer. TOTAL OUT NET Inflow (+) or Outflow (-) 200,000 FINANCING Cash Beginning balance Increase (decrease) Ending balance Line of Credit Beginning balance Increase (decrease) Ending balance 625,400 * Note: Total financing (decrease in cash plus increase in line of credit) must equal net outflow. Any net inflow first decreases the line of credit and then, if line of credit reaches zero, increases cash. Menotomy Diaper Services In November 2009, Ms. Carolyn Ringer, Treasurer of Menotomy Diaper Services (MDS), was prepar- ing some forecasts in conjunction with a loan request to the Norten Municipal Bank. Less than 8 months earlier, MDS had expanded its staff by 25 percent. At that time, the company had been in a secure financial position, and Ms. Ringer, anticipating no financial problems, had spoken to her board of directors in favor of the increase in services. However, in July, she realized that although MDS had begun training and pay- ing the new staff in June, the company would not receive the additional revenue they generated until later in the year. With the expansion in both full and part-time staff, she realized that the company might not have the required amount of cash on hand. Faced with this situation, Ms. Ringer had gone to the bank to apply for a short-term line of credit to cover the additional costs. Mr. Jansen, the bank officer, had granted MDS a line of credit, which had reached over $620,000 by September 30, 2009. Although he had allowed the company to continue using its line of credit into its next fiscal year (which ran from October 1 to September 30) he had raised some im- portant considerations. He explained that the bank was willing to continue a line of credit for the company but he was con- cerned that Ms. Ringer had not adequately anticipated the company's cash needs. He asked Ms. Ringer to present the bank at the outset with a detailed monthly statement of MDS's projected cash needs for fiscal year 2010. Consequently, Ms. Ringer began to review the company's financial statements (contained in Ex- hibits 1 and 2) and collect data that would help her plan for MDS's future cash requirements. BACKGROUND Menotomy Diaper Services was founded several years ago by four retired nurses. Each founder had between 10 and 35 years of experience working with infants in Norten's hospitals. Realizing that parents increasingly were using cloth diapers, the nurses saw a need for a diaper service, and started their own company. At first, MDS was small and operated out of one person's home. The company then employed the four founders, a part-time laundry worker, and three drivers. Within a few years, the organization had outgrown its "office space" as well as its organizational structure. Moreover, demand for the company's services had risen at an average rate of 10 percent per year, and forecasts at that time indicated a steady increase at the 10 percent level. As a result, MDS underwent a gradual expansion. They purchased a small building in downtown Norten for management offices, as well as laundry facilities with ample parking for the company's delivery trucks. They also bought some of the vehicles and equipment they had previously rented. In the subsequent 10 years, the staff doubled. A few years later, in conjunction with increased use of cloth diapers by environmentally concerned par- ents, MDS was again faced with an increasing demand for its services. A poll taken at Norten's three hos- pitals in March 2009 indicated that there would be a 20 percent increase in the use of cloth diapers. MDS management estimated that net revenue would reach $8,100,000 in FY 2010, an increase of 16 percent over the 2009 level. Further growth of between $750,000 and $1,000,000 a year was expected during the next three years. Consequently, MDS was forced to decide whether to hold staff and services steady and take a risk that other diaper services would fill in the gaps or to launch an expansion program aimed at providing more services to a greater number of families in the Norten area. After examining MDS's financial position and talking to Norten's staff and customers about the company's services, the board voted overwhelmingly to expand. to illustrate either DATA Demand for MDS's services, like that for many other cloth diaper agencies, was seasonal. Over two thirds of the company's annual sales were made during the late fall, winter, and spring months. During summer, when people were on vacation, they shifted from cloth to disposable diapers. Exhibit 3 contains the forecasted monthly revenue for FY 2010. In making these projections, Ms. Ringer took into account the In terms of expenses, disbursements related to overhead and administration were $150,000 a month throughout FY 2010. Included in that figure was Ms. Ringer's estimate of the monthly interest on the Nor- ten Municipal Bank line of credit. Contract service expenses were forecast at $9,000 a month, and were paid out each month. By contrast, supply purchases were projected at $34 200 per month; the company's policy was to charge these items and pay for each month's purchases in the following month. Employees were paid on the first of each month for carnings from the previous month. Starting in Oc- tober, salaries and wages were expected to be $412,500 a month, except for June, when many employees would be on vacation and some part-time wages would be eliminated; payments in July would thus de- crease to about $300,000. Otherwise, although demand for the company's services was somewhat seasonal, MDS's salaries and benefits were steady throughout the year. Ms. Ringer believed that this policy of regu- lar employment for its employees enabled it to maintain a highly skilled and committed staff, which in turn contributed significantly to the company's reputation for quality service. The initial depreciation expense was forecasted to be $330,000. In addition, the company was the lessee on several long-term leases of furniture, equipment, and automobiles, with terms ranging from 3 to 5 years. The payments on these leases totaled $225,000, payable in equal installments in December and June. Because of some difficulties in making timely tax payments in the past, MDS was required by the IRS to make monthly tax payments, which were projected to be $15,000 a month. However, she expected that MDS would be in a 50 percent tax bracket, and thus, as of the end of the year, might be owed for some overpayments. Some new delivery trucks were due for delivery in December. The trucks cost $180,000 and would be paid for in four equal monthly installments, beginning on delivery. The vehicles had an economic life of four years, at the end of which their salvage value would be zero. Depreciation would begin in the month of delivery, and would be added to the above $330,000 amount. Three years ago, MDS had borrowed $1,800,000 from a life insurance company under a 16-year note secured by the company's property and equipment. The current portion of the note, and the related interest payments, were made in equal installments in March and September of each year. In preparing her financial forecasts for 2010, Ms. Ringer planned to show separately the two principal payments, totaling $112,500, and the two interest payments, totaling $139,220. In FY 2010, MDS would need to repay George Wilson, one of its board members, $290,700 for an interest-free loan that he had made to the company a few yeas ago. The payments would be in equal install- ments in December and March. The only other cash outflow Ms. Ringer anticipated was $140,860 due to an equipment manufacturer. The sum payable was non-interest-bearing and due in equal installments in June and September. She did not expect to incur a liability of this sort during FY 2010. Inventory, prepaid expenses, other assets, and contributed capital were expected to remain essentially unchanged during FY 2010. Given the company's growth plans, Ms. Ringer did not anticipate making any dividend payments for 2010. Although Mr. Jansen had not hesitated to grant MDS a line of credit, he had stressed the importance of the bank's credit regulations. For a line of credit of $2,000,000 or less, the bank required the borrower to maintain a compensating cash balance of $200,000 at all times. Also, the entire line of credit, including the $654,000 currently owed by MDS, was to be completely liquidated for at least one month during the year. Using the worksheet in Exhibit 4, Ms. Ringer began to prepare a monthly cash flow forecast for FY 2010 that, she hoped, would show how much money the company would need on a monthly basis. Assignment 1. Prepare the cash flow worksheet contained in Exhibit 4. What does this tell you about the operations of MDS? 2. Prepare pro forma financial statements for FY 2010 (including a statement of cash flows). What do these tell you about the operations of MDS? How can you reconcile the information in the cash flow worksheet with that in the financial Calculate those ratios that might be important to the bank for each year in the 4-year period. What do these ratios tell What changes in financing policies took place in the most recent year as compared to previous years? What recommendations would you make to Ms. Ringer? statements? 3. you about MDS? 4. 5. MENOTOMY DIAPER SERVICES Exhibit 1. Balance Sheets As of September 30, 2007-2009 2007 2008 2002 Assets Cash Accounts receivable Inventory Prepaid expenses Total current assets Property and equipment (net) Other assets Total assets $1,434,600 526,500 266,220 321,180 $2,548,500 $ 328,050 581,400 307,800 343,030 $1,560,280 $ 200,000 681,000 399,600 415,850 $1,696,450 3,153,450 135.900 $5,837,850 4,117,350 159,750 $5,837,380 4,689,460 166,500 $6,552,410 Liabilities and Equity Line of credit (overdraft) Accounts payable Salaries and benefits payable Due to equipment manufac rer Loan payable (G. Wilson) Current portion of note payable Total current liabilities $ 0 27,450 310,500 120,600 212,400 112,500 $ 783,450 $ 0 35,420 324,000 131,530 262,350 112.500 $ 865,800 $ 625,400 29,250 351,000 140,860 290,700 112.500 $1,549,710 Note payable (non-current) Total liabilities 1,687,500 $2,470,950 1,575,000 $2,440,800 1,462,500 $3,012,210 Contributed capital Retained earnings Total liabilities and equity 2,700,000 666,900 $5,837,850 2,700,000 696,580 $5,837,380 2,700,000 840,200 $6,552,410 Exhibit 2. Operating Statements Fiscal Years 2007-2009 2007 2008 2009 $6,494,620 $7,224,930 $8,385,240 1.148.920 1302.780 1400 340 $5,345,700 $5,922,150 $6,984,900 Gross client revenue Less: bad debt expense Net client revenue Operating expenses: Salary and benefits Overhead and administration Supplies Contract services Equipment rental Depreciation Total operating expenses Income from operations Less: interest expense Income before tax Less: income tax Net income $3,726,000 631,290 244,770 81,230 172,500 225,000 $5,080,790 $3,888,000 993,880 279,670 86,400 187,500 285,000 $5,720,450 $ 201,700 159.470 $42.230 12.550 $ 29,680 $4,212,000 1,363,100 344,710 102,300 196,500 330.000 $6,548,610 $264,910 168,350 $96,560 30.580 $65,980 $436,290 149 350 $286,940 143.320 $ 143,620 Exhibit 3. Estimated Monthly Revenue and Month-End Accounts Receivable For FY 2010 Gross Net Accounts Client Client Receivable Revenue Revenue* End-of-Month* October $ 675,000 $ 558,000 $1,174,500 November 960,000 787,500 1,480 500 December 1,356,000 1,111,500 2,128,500 January 1.399,500 1,147,500 2,574,000 February 1,564,500 1,282,500 2,781,000 March 883,500 724,500 2,358,000 April 735,000 603,000 1,431,000 May 613,500 504,000 1,174,500 June 534,000 438.000 765,000 July 328,500 270,000 595,500 345,000 August 283,500 600,000 475 500 390.000 897,000 September $9,870,000 $8,100,000 * After adjustments for bad debts. MENOTOMY DIAPER SERVICES Exhibit 4. Cash Flow Worksheet Nov. Dec. Jan. Feb. Mar. Oct. Apr. May June July Aug. Sep. CASH IN CASH OUT Salary & Benefits O/H & Administration Payment of A/P Contract Services Payments to IRS Truck Expenditure Payment of loan Equip. Leases Note principal Note interest Equipment mfturer. TOTAL OUT NET Inflow (+) or Outflow (-) 200,000 FINANCING Cash Beginning balance Increase (decrease) Ending balance Line of Credit Beginning balance Increase (decrease) Ending balance 625,400 * Note: Total financing (decrease in cash plus increase in line of credit) must equal net outflow. Any net inflow first decreases the line of credit and then, if line of credit reaches zero, increases cash