Financial Plan for a Building Landscaping Business

Download Data File: PR2-Data-2

Running your own landscaping business to maintain a couple commercial buildings can be an excellent way to make money or to supplement your existing income for the purpose of saving money for retirement or for a college fund. However, managing the costs of the business will be critical in order for it to be a profitable venture. In this exercise you will create a simple financial plan for a landscaping business by using the skills covered in this chapter.

There are two worksheets in the workbook you will be using.

• Annual Plan – provides calculations to determine how much money the lawn care business brings in  for one year, based on the average price per lawn cut and the number cut per year,  as well as the expenses for the year.
• Equipment Loans – calculates the monthly payments for the various lawn care equipment loans.

Annual Plan Worksheet

1. Open the file named PR2 Data and then Save As PR2 Building Landscaping.
2. Switch to the Annual Plan worksheet if needed.
3. Enter the following data into cells B14, B15, and B16:
   • Gasoline cost (per cut) = $10
   • Number of customers = 30
   • Annual lawn cuts per customer = 20
4. In cell B3, enter the average price per lawn cut of $50.
5. In cell B4, write a formula that calculates the total number of lawns cut in the year. This is the number of customers multiplied by the annual lawn cuts per customer.
6. In cell B5, write a formula that calculates the total annual sales.  This is found by multiplying the average price per lawn by the total number of lawn cuts.
7. In cell B8, write a formula to calculate the total cost of gasoline for the year. This is found by multiplying the gasoline cost per cut by the total number of lawns cut. 
8. You will finish the rest of this worksheet after completing the Equipment Loans worksheet.

1. Switch to the Equipment Loans worksheet.
2. In cell E3, write a PMT function to calculate the monthly payment for the Commercial Lawn Mower.  Don’t forget the negative sign in between the equal sign and the PMT!  Remember to convert the interest rate and years to monthly terms and to use cell references.  The arguments of the PMT function should be as follows:
   • RATE: B3/12
   • NPER: C3*12
   • PV: D3
3. Copy the PMT function from cell E3 to the other equipment items.
4. In cell E10, use the SUM function to calculate the total for the monthly loan payments. Make sure that the blank rows (7 through 9) were included in the range for the SUM function so that you can add more equipment items later if needed.
5. In cell E11, write a formula that calculates the total annual loan payments. This will be the monthly total multiplied by 12 (the number of months in a year).
6. If needed, apply Accounting format to all of the monetary values so that the placement of the dollar sign is consistent throughout the worksheet.
7. Sort the data in the range A3:E6 first by Interest Rate and then by Loan Amount using the following steps:
   • Select the range A3:E6.
   • Click the Sort button in the Data tab of the Ribbon.
   • In the Sort dialog box, select the Interest Rate option in the “Sort by” drop-down box. Select Largest to Smallest for the sort order.
   • Click the Add Level button in the Sort dialog box.
   • Select the Loan Amount option in the “Then by” drop-down box. Select Largest to Smallest for the sort order.
   • Click the OK button in the Sort dialog box.
8. Add a header with the date on the left and the worksheet name on the right.  Be sure to insert the date and worksheet name so that they will automatically update.
9. Check Print Preview and make any other changes necessary for professional printing.

1. Switch back to the Annual Plan worksheet.
2. In cell B9, write a formula that displays the annual monthly payments total from cell E11 in the Equipment Loans worksheet using the following steps:
   •  Type an equal sign =
   • Click the Equipment Loans worksheet
   • Click cell E11
   • Press the ENTER key
3. In cell B10, calculate the Total Expenses by adding the Gasoline Cost and the Annual Equipment Payments.
4. In cell B12, calculate the Annual Profit by finding the difference between the Total Annual Sales and the Total Expenses.  Hint: This will hopefully be a positive number which shows that your business is making money instead of losing money.
5. Format all cells that contain monetary amounts in the Annual Plan worksheet for Accounting Number Format ($) with no decimal places. Format all other numerical values as Comma format with no decimal places.
6. Add a header with the date on the left and the worksheet name on the right.  Be sure to insert the date and worksheet name so that they will automatically update.
7. Check Print Preview and make any changes necessary for professional printing.
8. Check the spelling on all of the worksheets and make any necessary changes.

Attribution

Adapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.