Please cancel this question.

ASSIGNMENT 1: 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.

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.

ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT

Complete the case by (1) using the spreadsheet to answer Steve’s questions, (2) thoroughly analyzing your data, and (3) documenting your findings in a memo. Steve needs to understand the impacts of a few different options he is considering. He wants to determine his available cash flow at the end of each year based on the following scenarios: 1. Do nothing—Assume that no changes will be made and keep all of Steve’s assumptions. With this scenario, he anticipates an annual 5 percent decrease in the customer base. Annual income in this scenario is $65,000. 2. Accept the bank loan with low return on investment (ROI)—Steve has met with the bank’s loan officer, who estimated that a 10-year loan with 3 percent annual payments would total $120,000. Using the loan for capital expenses and marketing campaigns, Steve expects that the customer base can grow at 6 percent annually starting in 2018. Annual income in this scenario is $100,000. 3. Accept the bank loan with high ROI—Again, the loan officer estimated that a 10-year loan with 3 percent annual payments would total $120,000. Using the loan for capital expenses and marketing campaigns, Steve expects that the customer base can grow at 13 percent annually

starting in 2018. 4. Accept buyout offer—In this scenario, Steve would accept the buyout offer from a competitor,

invest it, and start collecting $100,000 annually from the annuity. Note that this scenario would require a one-time change to the customer base, from 850 to 0.

Assignment 2A: 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.