<\/p>\n
CREATING A SPREADSHEET FOR DECISION SUPPORT<\/strong><\/p>\n In this assignment, you will produce a spreadsheet that models Philly Landscaping\u2019s 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\u2019s 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.<\/p>\n 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\u20142017 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:<\/p>\n \u2022 Constants 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<\/p>\n Philly Landscaping.xlsx<\/strong>.<\/p>\n Constants Section<\/strong><\/p>\n Your spreadsheet should include the constants, otherwise known as assumptions, shown in Figure 6-1. An explanation of the line items follows the figure.<\/p>\n \u2022 Prices\u2014These prices are based on averages that Steve provided. Inputs Section<\/strong><\/p>\n 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\u2019s 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.<\/p>\n 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.<\/p>\n \u2022 Customer Base Change %\u2014This value is the expected change in the size of the customer portfolio. The value could be positive, negative, or zero starting in 2017. Summary of Key Results Section<\/strong><\/p>\n Your spreadsheet should include the results shown in Figure 6-3. A general explanation of this section follows<\/p>\n the figure.<\/p>\n 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.<\/p>\n Calculations Section<\/strong><\/p>\n 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 \u201cNA.\u201d<\/p>\n An explanation of each item in this section follows the figure in which the item is shown.<\/p>\n \u2022 Rough Yard Work\u2014The 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. \u2022 Rough Yard Work\u2014Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals. \u2022 Labor and Materials\u2014Based 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. <\/p>\n Using the Spreadsheet to Gather Data<\/strong><\/p>\n 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.<\/p>\n You will run \u201cwhat-if\u201d 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.<\/p>\n 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.<\/p>\n <\/p>\n Documenting Your Results in a Memo<\/strong><\/p>\n 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:<\/p>\n \u2022 Set up your memo as described in Tutorial E. <\/p>\n GIVING AN ORAL PRESENTATION<\/strong><\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" Problem CREATING A SPREADSHEET FOR DECISION SUPPORT In this assignment, you will produce a spreadsheet that models Philly Landscaping\u2019s 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\u2019s questions. In Assignment 2, you will use […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","template":"","meta":[],"disciplines":[211],"paper_types":[],"tagged":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions\/17606"}],"collection":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions"}],"about":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/types\/questions"}],"author":[{"embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/comments?post=17606"}],"version-history":[{"count":0,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions\/17606\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/media?parent=17606"}],"wp:term":[{"taxonomy":"disciplines","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/disciplines?post=17606"},{"taxonomy":"paper_types","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/paper_types?post=17606"},{"taxonomy":"tagged","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/tagged?post=17606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\u2022 Inputs
\n\u2022 Summary of Key Results
\n\u2022 Calculations<\/p><\/blockquote>\n
\n\u2022 Rough Yard Work per Square Foot
\n\u2022 Gutter Cleaning per Linear Foot
\n\u2022 Power Washing per Square Foot
\n\u2022 Lawn Mowing and Edging per Square Foot
\n\u2022 Driveway Seal Coating per Square Foot
\n\u2022 Fall Leaf Clearing per Square Foot
\n\u2022 Snow Removal per Square Foot
\n\u2022 Costs\u2014The average cost of labor and materials is based on averages Steve provided from previous years.
\n\u2022 Customer Base\u2014These values show the company\u2019s current number of customers and average lot coverage areas for various company services. Most of these averages are shown in square footage (Sq Ft).
\n\u2022 Customers\u2014This value shows the number of customers currently served by the company.
\n\u2022 Average Lawn Surface (Sq Ft)
\n\u2022 Average Power Washing Surface (Sq Ft)
\n\u2022 Average Gutter Length (Linear Ft)
\n\u2022 Average Snow Removal Surface (Sq Ft)
\n\u2022 Average Driveway Seal Coating Surface (Sq Ft)
\n\u2022 Average Fall Leaf Clearing Surface (Sq Ft)
\n\u2022 Economic and Environmental Factors\u2014Based on conversations with his accountant, Steve feels comfortable using a 25 percent tax rate for the model.<\/p><\/blockquote>\n
\n\u2022 Annual Payments For Loan\u2014The bank\u2019s 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.
\n\u2022 Annual Income Required For Retirement\u2014This value represents what Steve is willing to accept as annual retirement income.<\/p><\/blockquote>\n
\n\u2022 Lawn Mowing and Edging\u2014The 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.
\n\u2022 Power Washing\u2014The product of the average power washing surface and the number of customers. Format cells for numbers with zero decimals.
\n\u2022 Gutter Cleaning\u2014The product of the average gutter length and the number of customers. Format cells for numbers with zero decimals.
\n\u2022 Snow Removal\u2014The product of the average snow removal surface and the number of customers. Format cells for numbers with zero decimals.
\n\u2022 Driveway Seal Coating\u2014The product of the average driveway seal coating surface and the number of customers. Format cells for numbers with zero decimals.
\n\u2022 Fall Leaf Clearing\u2014The 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.<\/p><\/blockquote>\n
\n\u2022 Lawn Mowing and Edging\u2014Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
\n\u2022 Power Washing\u2014Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
\n\u2022 Gutter Cleaning\u2014Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
\n\u2022 Snow Removal\u2014Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
\n\u2022 Driveway Seal Coating\u2014Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
\n\u2022 Fall Leaf Clearing\u2014Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
\n\u2022 Total Revenue\u2014The sum of all revenues for the year. Format cells for currency with zero decimals.<\/p><\/blockquote>\n
\n\u2022 Loan Repayment\u2014Repayment would start in 2018 if the loan offer is approved. Format cells for currency with zero decimals.
\n\u2022 Total Expense\u2014The sum of labor and materials and the loan repayment. Format cells for currency with zero decimals.
\n\u2022 Income Before Taxes\u2014The difference between total revenue and total expense. Format cells for currency with zero decimals.
\n\u2022 Tax Expense\u2014The tax liability based on the tax rate in the Constants section. Format cells for currency with zero decimals.
\n\u2022 Net Income\u2014The difference between net income before taxes and tax expense. Format cells for currency with zero decimals.
\n\u2022 Enough Income to Hire Manager?\u2014Starting in 2018, if the difference between net income and the amount required to retire is over $50,000, enter \u201cYes.\u201d Otherwise, enter \u201cNo.\u201d
\n\u2022 Income Over Expected Annuity Earnings?\u2014Starting in 2018, if net income is greater than the estimated annuity value ($100,000), enter \u201cYes.\u201d Otherwise, enter \u201cNo.\u201d<\/p><\/blockquote>\n
\n\u2022 In the first paragraph, briefly state the business situation and the purpose of your analysis.
\n\u2022 Next, describe the scenarios tested.
\n\u2022 State your conclusions.
\n\u2022 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.)
\n\u2022 Your table should have the format shown in Figure 6-7.<\/p><\/blockquote>\n