Problem
CREATING A SPREADSHEET FOR DECISION SUPPORT
In this assignment, you will produce a spreadsheet that models Philly Landscaping’s estimated 2017 revenues, expenses, and profits; provides forecasts of 10 years of cash flows for the company; and allows for the input of other variables to answer Steve’s questions. In Assignment 2, you will use your spreadsheet to gather data and then write a memorandum that documents your analysis and findings. In Assignment 3, you will prepare and give an oral presentation of your analysis and conclusions to Steve.
First, you need to create the spreadsheet model based on your conversations with Steve and your understanding of the questions he would like to have answered. The model will cover 11 years—2017 as the base year and 10 subsequent years as requested by the bank to provide estimated cash flows. This section helps you set up each of the following spreadsheet components before entering cell formulas:
• Constants
• Inputs
• Summary of Key Results
• Calculations
A discussion of each section follows. The spreadsheet skeleton for this case is available for you to use; it will save you time. To access the spreadsheet skeleton, go to your data files, select Case 6, and then select
Philly Landscaping.xlsx.
Constants Section
Your spreadsheet should include the constants, otherwise known as assumptions, shown in Figure 6-1. An explanation of the line items follows the figure.
• Prices—These prices are based on averages that Steve provided.
• Rough Yard Work per Square Foot
• Gutter Cleaning per Linear Foot
• Power Washing per Square Foot
• Lawn Mowing and Edging per Square Foot
• Driveway Seal Coating per Square Foot
• Fall Leaf Clearing per Square Foot
• Snow Removal per Square Foot
• Costs—The average cost of labor and materials is based on averages Steve provided from previous years.
• Customer Base—These values show the company’s current number of customers and average lot coverage areas for various company services. Most of these averages are shown in square footage (Sq Ft).
• Customers—This value shows the number of customers currently served by the company.
• Average Lawn Surface (Sq Ft)
• Average Power Washing Surface (Sq Ft)
• Average Gutter Length (Linear Ft)
• Average Snow Removal Surface (Sq Ft)
• Average Driveway Seal Coating Surface (Sq Ft)
• Average Fall Leaf Clearing Surface (Sq Ft)
• Economic and Environmental Factors—Based on conversations with his accountant, Steve feels comfortable using a 25 percent tax rate for the model.
Inputs Section
As Steve explained earlier, he would like to answer some important questions to determine his best option for retirement. First, the model needs to evaluate the impact of the loan on the customer base’s growth. Second, the model needs to evaluate the repayment of the loan if it is approved. Finally, Steve has different ideas on how much money he will need to retire comfortably; he thinks an amount between $75,000 and $100,000 annually would be sufficient. The DSS will determine whether these options are viable.
Your spreadsheet should include the following inputs, as shown in Figure 6-2. Note that the spreadsheet extends to 2027, as explained earlier, but the remaining figures in this case have been cropped to fit the page.
• Customer Base Change %—This value is the expected change in the size of the customer portfolio. The value could be positive, negative, or zero starting in 2017.
• Annual Payments For Loan—The bank’s loan officer has provided an estimate of an annual total payment of $120,000 for a loan of $1 million with a 3 percent interest rate over 10 years.
• Annual Income Required For Retirement—This value represents what Steve is willing to accept as annual retirement income.
Summary of Key Results Section
Your spreadsheet should include the results shown in Figure 6-3. A general explanation of this section follows
the figure.
For each year starting in 2017, this section should include values that are already calculated elsewhere in the spreadsheet. The formulas in the Summary of Key Results section will echo results from throughout your model; no long or complicated formulas need to be used in this section. The purpose of gathering the results together is to make for an easier job when configuring Scenario Manager later.
Calculations Section
To create an accurate decision tool, you should calculate intermediate results that will be used to determine the year-end numbers needed for the model. It is generally a good idea to arrive at these final numbers in a series of steps rather than in one short calculation. Errors are easier to identify if the steps are broken out, and it also makes troubleshooting a breeze. The calculations shown in Figures 6-4, 6-5, and 6-6 are based on 2017 values in the Constants section (Customer Base values and prices); starting in 2018, the calculations take into account the inputs from each scenario. When called for, use absolute referencing properly. Values must be computed by cell formula; hard-code numbers in formulas only when you are told to do so. Cell formulas should not reference a cell with a value of “NA.”
An explanation of each item in this section follows the figure in which the item is shown.
• Rough Yard Work—The product of the average lawn surface and the number of customers. Steve tells you that only 25 percent of customers request this service. Format cells for numbers with zero decimals.
• Lawn Mowing and Edging—The product of the average lawn surface and the number of customers. Steve tells you that only 25 percent of customers request this service. Format cells for numbers with zero decimals.
• Power Washing—The product of the average power washing surface and the number of customers. Format cells for numbers with zero decimals.
• Gutter Cleaning—The product of the average gutter length and the number of customers. Format cells for numbers with zero decimals.
• Snow Removal—The product of the average snow removal surface and the number of customers. Format cells for numbers with zero decimals.
• Driveway Seal Coating—The product of the average driveway seal coating surface and the number of customers. Format cells for numbers with zero decimals.
• Fall Leaf Clearing—The product of the average lawn surface and the number of customers. Steve tells you that 75 percent of customers request this service. Format cells for numbers with zero decimals.
• Rough Yard Work—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Lawn Mowing and Edging—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Power Washing—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Gutter Cleaning—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Snow Removal—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Driveway Seal Coating—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Fall Leaf Clearing—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Total Revenue—The sum of all revenues for the year. Format cells for currency with zero decimals.
• Labor and Materials—Based on current estimates, the annual expense for labor and materials is $500,000. This number will need to be updated based on customer base changes starting in 2018. Format cells for currency with zero decimals.
• Loan Repayment—Repayment would start in 2018 if the loan offer is approved. Format cells for currency with zero decimals.
• Total Expense—The sum of labor and materials and the loan repayment. Format cells for currency with zero decimals.
• Income Before Taxes—The difference between total revenue and total expense. Format cells for currency with zero decimals.
• Tax Expense—The tax liability based on the tax rate in the Constants section. Format cells for currency with zero decimals.
• Net Income—The difference between net income before taxes and tax expense. Format cells for currency with zero decimals.
• Enough Income to Hire Manager?—Starting in 2018, if the difference between net income and the amount required to retire is over $50,000, enter “Yes.” Otherwise, enter “No.”
• Income Over Expected Annuity Earnings?—Starting in 2018, if net income is greater than the estimated annuity value ($100,000), enter “Yes.” Otherwise, enter “No.”
Using the Spreadsheet to Gather Data
You have built the spreadsheet to model several possible situations. For each of the four test scenarios, you want to know the annual cash flow, whether Steve will be able to hire a general manager, and whether income from the company surpasses the estimated annuity value.
You will run “what-if” scenarios with the four sets of input values using Scenario Manager. (See Tutorial C for details on using Scenario Manager.) Set up the four scenarios. Your instructor may ask you to use conditional formatting to make sure your input values are proper. Note that in Scenario Manager you can enter noncontiguous cell ranges, such as C19, D19, C20:F20.
The relevant output cells are Annual Income, Enough Income to Hire Manager?, and Income Over Expected Annuity Earnings? from 2018 to 2027. All of these cells are shown in the Summary of Key Results section. Run Scenario Manager to gather the data in a report. When you finish, print the spreadsheet with the input for any of the scenarios, print the Scenario Manager summary sheet, and then save the spreadsheet file a final time.
Documenting Your Results in a Memo
Use Microsoft Word to write a brief memo that documents your analysis and results. You can address the memo to Steve, the owner of Philly Landscaping. Observe the following requirements:
• Set up your memo as described in Tutorial E.
• In the first paragraph, briefly state the business situation and the purpose of your analysis.
• Next, describe the scenarios tested.
• State your conclusions.
• Support your statements graphically, as your instructor requires. Your instructor may ask you to return to Excel and copy the results of the Scenario Manager summary sheet into the memo. You should include a summary table built in Word based on the Scenario Manager summary sheet results. (This procedure is described in Tutorial E.)
• Your table should have the format shown in Figure 6-7.
GIVING AN ORAL PRESENTATION
Your instructor may ask you to explain your analysis and results in an oral presentation. If so, assume that Steve wants the presentation to last 10 minutes or less. Use visual aids or handouts that you think are appropriate. See Tutorial F for tips on preparing and giving an oral presentation.