2.5 Chapter Practice
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
- Open the file named PR2 Data and then Save As PR2 Building Landscaping.
- Switch to the Annual Plan worksheet if needed.
- 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
- In cell B3, enter the average price per lawn cut of $50.
- 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.
- 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.
- 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.
- You will finish the rest of this worksheet after completing the Equipment Loans worksheet.
Equipment Loans Worksheet
- Switch to the Equipment Loans worksheet.
- 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
- Copy the PMT function from cell E3 to the other equipment items.
- 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.
- 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).
- If needed, apply Accounting format to all of the monetary values so that the placement of the dollar sign is consistent throughout the worksheet.
- Sort the data in the range A3:E6 first by Interest Rate and then by