Melody Henderson has worked as a manager in the lawn service industry for 6 years. She is capable at recruiting and managing part-time workers. Her
Melody Henderson has worked as a manager in the lawn service industry for 6 years. She is capable at recruiting and managing part-time workers. Her workers like her. Melody treats her customers well and she is well-like by customers. Melody attracts new customers on a regular basis. So Melody has decided to take the plunge and start her own lawn service company. Melody is confident about her ability to attract customers to her new business. She is knowledgeable about costs.
Melody will start her business on April 1, and open for business with customers on May 1 of the current year. She needs projected information about her cash needs. She has contacted you. You are an Accountant and Consultant. Melody wants you to prepare:
Prepare a 12-month Sales and Billing Budget
Prepare a 12-month Collections from Customers Budget
Prepare a 12-month month Cash Budget
Prepare a 12-month and Annual income statement.
(12 month means there will be a separate column for each Month)
Upon your first meeting with Melody, you inform Melody that in order for the reports and projections she wants and needs to be useful, it will be necessary for Melody to work collaboratively with you to develop reliable information from which the repots can be assembled.
You and Melody have worked together to identify all of the following information.
Melody's business will involve fertilizing, seeding, cutting and treating lawns as well as removing snow and applying chemicals to melt ice. Melody plans to bill for her service by charging an hourly fee plus a sales charge per pound for chemicals and seed.
Melody will start here business with $1,500 in cash.
Estimates of projected Employee Hours, Owner Hours, Fertilizer, Seed, and Ice Melt are included on Table 1.
The owner, herself, will frequently work directly with customers. These hours will not be paid even though they will be billed to customers. All of the hours (including the owner’s unpaid hours) will be billed to customers monthly using the billing rate for hours in Table 1.
Fertilizer, Insecticide, Seed, and Ice Melt are billed to customers monthly using their respective billing rates from table 3. All customers will be billed at the end of each month. According to the billing terms, the customers should pay their bills when received in the following month. However, only 4% do so. 91% pay in the second month following receipt of their bill. The remaining 5% don't pay at all (Who knows why?). Uncollected receipts represent a bad debt expense.
Employees will be paid wages based on the rates in table 2. All wages are paid in the same month incurred. Payroll taxes will be 12% of the wages and are also paid in the month in which the wages are paid. The payments to the owner are not wages and are not subject to payroll taxes.
Fertilizer, insecticide, seed, and ice-melt are paid for in the same month purchased. The quantities purchased are reflected in table 1. The costs are reflected in table 2. The owner is concerned that fertilizer and seed might occasionally be in short supply. Therefore, fertilizer and seed must be purchased and paid for in the month before use. Insecticide, for which there is no expected shortage, will be purchased and paid for in the month used. It is expected that the winter will be mild (the Midwest El Nino effect) and therefore the snowmelt chemical will be readily available - these will also be purchased and paid for in the month of use.
The business will spend $2,500 for advertising in May and $1,700 in June. The business will advertise in early November to attract winter snow removal customers. The November advertising expenditure will be $800
The owner will purchase 15 lawnmowers at $900 apiece, a pickup truck for $11,000, a trailer for $4,500 and various other lawn implements for $8,000. All of these purchases will be made in April and also paid for in April.
The owner will borrow $18,000 at the end of April at 12%. The Principal payments will be $500 each month. The Principal payment will not change in amount. The first payment will be made in May. Interest payments on this loan will also start in May. The first payment will be $180. The payment will decrease by $5 each month thereafter.
In addition to the truck, trailer and implement purchases in April, in October the Owner will purchase 5 snow blowers for $900 each and a used snow blade for the front of the pickup truck for $7,500. These items will all be purchased and paid for in October.
The owner will borrow $12,000 early in October to finance these implement purchases. The interest rate on the loan will be 8%. The owner will make principal payments of $600 per month (Starting in October) on this loan. The owner will make interest payments of $80 the first month (October) with the payment decreasing by $4 per month for each month thereafter.
Gas, oil, and other supplies will amount to $1,080 per month and will be incurred and paid monthly starting with May and continuing until the end of October. In November, the gas and supplies amount will decrease to $300 and continue at that rate until the end of March.
Depreciation will be $750 per month starting in May. Depreciation will increase to $900 per month starting in November.
The owner will withdraw $5,000 for personal use per month starting in May.
The owner will start the business with $1,500 in Cash
REQUIRED
(There are two requirements: An Excel File and a Word Report)
Requirement 1:
Prepare an Excel Computer Model that includes the following:
Your file name for submission will be: Excel Case 1 YourName . xls
(Your model should be flexible enough so that the Instructor can change any individual cost or quantity number and have the computer model automatically adjust all other budget numbers and totals on the cash budget and income statements.)
(Note: this assignment is easier if you list each item separately - do not combine expenditures, expenses, etc.)
1. Input Schedules for Quantities, Costs, and Billing Rates (Tables 1, 2, and 3 at the end of this narrative need to be incorporated into your excel file)
2. A schedule reflecting monthly revenues by category and in total.
3. A schedule that reflects Total Cash Receipts from Customers by Month. It is not necessary to reflect cash receipts by revenue category
4. Prepare a monthly cash budget for the 12 months April through March
Receipts by category (categories are collections from customers, and loan proceeds).
Insert a subtotal total reeipts (loans and from customers)
List Disbursements by category and then total (there are numerous categories). Make each disbursement a separate line.
Subtract total disbursements from total receipts – this is change in cash or cash flow
Summarize – Start with Beginning Cash
Add Cash flow from above
Show ending cash. (The beginning and ending cash balances can be either positive or negative. In that way it will be easy to determine how much additional financing may be required.)
Non-cash items do not belong on the cash budget.
Prepare monthly income statements for the months April through March of the next year. Also prepare a 1-year income statement summarizing the monthly income statements from April through March.
Income statements show revenues. A single Revenue line labeled Sales or Revenue from
Customers is acceptable. Not all receipts are Revenues – the income statement shows revenues but no other cash receipts.
Income statements show expenses. List each expense separately. Not all cash payments are expenses. Only list expenses
Also, Include in expenses those expenses that do not use cash
Requirement 2:
Prepare a report in word (or compatible format)
Your reports should address the following issue.
1. Using your Excel model determine how much additional financing you will need. Explain how you arrived at your answer.
2. Answer this question: What do you see in the cash budget and what do you see in the income statement that would cause you to grant or deny financing to this Company? The most common error students make in answering this question is to make comments not supported by their model. Comments based only on the cash budget or only on the income statements will not be complete enough to earn all of the points.
3. Significant differences exist between the cash flow and the net income numbers. What causes the differences between income and cash flow? Your answer must be specific. I suggest you define what goes on the income statement and what goes on the cash budget. Then identify what items are on the cash budget but not on the income statement. What items are on the income statement but not on the cash budget? What items are on the cash budget in one time period but on the income statement in a different time period?
Table 1: Quantities of Hours, Fertilizer, Insecticide, Seed, and Ice Melt Used
| | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr |
| Employee Hours | 0 | 1,130 | 800 | 460 | 620 | 440 | 0 | 0 | 80 | 10 | 290 | 40 | 0 |
| Owner Hours | 0 | 160 | 160 | 160 | 160 | 160 | 100 | 75 | 160 | 160 | 160 | 160 | 150 |
| Fertilizer | 0 | 2,500 | 900 | 600 | 200 | 300 | 150 | 0 | 0 | 0 | 0 | 0 | 150 |
| Insecticide | 0 | 1,100 | 750 | 950 | 600 | 200 | 50 | 0 | 0 | 0 | 0 | 0 | 200 |
| Seed | 0 | 1,400 | 750 | 150 | 250 | 150 | 300 | 0 | 0 | 0 | 0 | 400 | 450 |
| Ice Melt | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 150 | 150 | 300 | 250 | 120 | 0 |
Table 2: Cost AmountsPer Hour or Per Pound
| | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar |
| Hours | $16.00 | $16.00 | $9.00 | $8.00 | $7.00 | $7.00 | $9.00 | $10.00 | $12.00 | $14.00 | $14.00 | $10.00 |
| Fertilizer | $9.00 | $9.00 | $6.00 | $6.00 | $4.00 | $4.00 | $5.00 | $3.00 | $3.00 | $3.00 | $3.00 | $3.00 |
| Insecticide | $9.00 | $8.00 | $8.00 | $3.00 | $3.00 | $3.00 | $3.00 | $3.00 | $3.00 | $3.00 | $3.00 | $4.00 |
| Seed | $15.00 | $12.00 | $12.00 | $6.00 | $4.00 |
Step by Step Solution
★★★★★
3.49 Rating (159 Votes ) There are 3 Steps involved in it Step: 11 2 3 Employee Hours 4 Rate Per Hour A Particulars 5 Employee Billing A 6 Owner Hours 7 Rate Per Hour 8 Owner Billing B 9 Fertilizer 10 Rate Per Pound 11 Fertilizer Sales C 12 Insecticide 13 Rate Per ...
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success Step: 2
Step: 3
Ace Your Homework with AIGet the answers you need in no time with our AI-driven, step-by-step assistance Get Started
International Management Managing Across Borders and Cultures Text and Cases
Authors: Helen Deresky 9th edition 134376048, 978-0134376042 |
