Assignment 1
PREVIEW
Your father-in-law, Steve, owns and operates a small but successful landscaping company in the suburbs of Philadelphia. However, the continuing success of the company strongly depends on his ability to do a lot of the day-to-day work. Steve recently turned 50, and the thought of continued intense labor is not very appealing to him. He is faced with a choice: invest money in the company to build it to a point that all he has to do is manage it from a distance, or sell the company. Steve has come to you to help him model the outcome of his choices and provide recommendations. You will use Microsoft Excel to provide Steve a look into the future.
Task: 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, you will use your spreadsheet to gather data and then write a memorandum that documents your analysis and findings.
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.
FIGURE 6-2
Inputs section
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.
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 OverExpected Annuity Earnings?-Starting in 2018, if net income is greater than the estimated annuity value ($100,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."
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 andmarketing 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.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Data-Philly-Landscaping.rar