Problem 1: What-if and Goal-seeking analysis
Rama is developing a business plan and an application for financing for a new lawn-mowing business. She intends to run the service for 4 years, and then dissolve it. She has developed the following information on various aspects of the business.
Vehicle costs
Rama plans on buying three new Toyota Utes for the business for a net cost of 25,000 each. The resale value for a Ute after 4 years is estimated at 10,000. The following describes the annual operating costs for each vehicle:
• Annual insurance and registration costs: 2,000
• Maintenance and repairs: 1000 in the first year, and increases by 400 in each subsequent year.
Lawn mowers
In addition to vehicles, Rama must purchase three lawn mowers for 1,500 each. They are expected to last 4 years, but will have negligible (zero) resale value. Maintaining and repairing each lawn mower is estimated at 300 per year.
Fuel costs
Both the vehicles and lawn mowers utilise regular unleaded petrol. The estimated fuel costs are:
• 10 per job for round trips to the job location
• 4 per job for operating each lawn mower
Labour
Three people are employed at rate of 22 per hour and work according to demand. Each oneVperson mowing job typically requires 2.5 hours of labour including travel time. Business overheads (office, phone, etc.) are estimated to be 20% of the lawnVmowing labour costs. Rama will be the manager, with an annual salary of 55,000.
Demand
The demand for service is expected to be around 2000 jobs per year. The fee per oneVperson job is 130.
Rama's plan is to borrow the 80,000 required to purchase the vehicles and other equipment.
Tasks:
a) Based on the above information, develop a model using an Excel spreadsheet to project financial statements for Rama's Lawn Mowing.
The bank usually wishes to see the following components for financial performance on an annual basis and over the 4Vyear period: total revenue, total costs, and cash flow. The cash flow should be used to calculate the net present value of the 4V year investment. (The net present value of an investment is the value of the investment in ‘today's money'). The builtVin EXCEL function NPV can be used to calculate net present value. Use a discount rate of 12%.
b) Now perform some whatVif analysis to see what happens to the net present value if:
i) Fuel costs increase by 20%.
ii) Expected demand is only 1500 jobs per year.
c) Use goal seeking to find how many jobs per year would be required for Rama's company to break even; i.e., to achieve a net present value of 0.00.