SPD Tax Service is a regional tax preparation firm that competes with such national chains as H&R Block. The company is considering expanding and needs a financial model to analyze the decision to open a new store. Key factors affecting this decision include the demographics of the proposed location, price points that can be achieved in the target market, and the availability of funds for marketing and advertising. Capital expenditures will be ignored because unused equipment from other locations can often be shifted to a new store for the first year until they can be replaced periodically through the fixed cost budget. SPD’s target markets being considered are communities with populations between 30,000 and 50,000, assumed to be uniformly distributed. Market demand for tax preparation service is directly related to the number of households in the territory; approximately 15% of households are anticipated to use a tax preparation service. Assuming an average of 2.5 people per household, this can be expressed as 0.15*population/2.5. SPD estimates that its first year demand will have a mean of 5% of the total market demand, and for every dollar of advertising, the mean increases by 2%. The first year demand is assumed to be normal with a standard deviation of 20% of the mean demand. An advertising budget of $5,000 has been approved but is limited to 10% of annual revenues. Demand grows fairly aggressively in the second and third year and is assumed to have a triangular distribution with a minimum value of 20%, most likely value of 35%, and maximum value of 40%. After year 3, demand growth is between 5% and 15%, with a most likely value of 7%. The average charge for each tax return is $175, and increases at a rate that is normally distributed with a mean of 4% with a standard deviation of 1.0% each year. Variable costs average $15 per customer, and increase annually at a rate that is normally distributed with a mean of 3% with a standard deviation of 1.5%. Fixed costs are estimated to be approximately $35,000 for the first year, and grow annually at a rate between 1.5% and 3%. Develop a Monte Carlo simulation model to find the distribution of the net present value of the profitability of a new store over a 5-year period using a discount rate of 5%
> Using PivotTables, find the average and standard deviation of sales in the Sales Transactions database. Also, find the average sales by source (Web or e-mail). Do you think this information could be useful in advertising? Explain how and why or why not
> Number the rows in the Excel file Credit Risk Data to identify each record. The bank wants to sample from this database to conduct a more-detailed audit. Use the Excel Sampling tool to find a simple random sample of 20 unique records
> In the Excel file Cell Phone Survey, use PivotTables to find the average for each of the numerical variables for different cell phone carriers and gender of respondents
> The Excel file Internet Usage provides data about users of the Internet. Construct stacked bar charts that will allow you to compare any differences due to age or educational attainment and draw any conclusions that you can. Would another type of charts
> Using the data in the worksheet Consumer Transportation Survey, develop 95% confidence intervals for the following:a. The proportion of individuals who are satisfied with their vehicleb. The proportion of individuals who have at least one child
> Trade associations, such as the United Dairy Farmers Association, frequently conduct surveys to identify characteristics of their membership. If this organization conducted a survey to estimate the annual per-capita consumption of milk and wanted to be 9
> Call centers typically have high turnover. The director of human resources for a large bank has compiled data on about 70 former employees at one of the bank’s call centers in the Excel file Call Center Data. In writing an article about call center worki
> An investor estimates that there is a 1 in 10 chance that a stock purchase will lose 20% of its value, a 2 in 10 chance that it will break even, a 4 in 10 chance that it will gain 15%, and a 3 in 10 chance that it will gain 30%. What is the expected retu
> In the roulette example described in Problem 7, what is the probability that the outcome will be green twice in a row? What is the probability that the outcome will be black twice in a row?In Problem 7Roulette is played at a table similar to the one in F
> An investor estimates that there is a 1 in 10 chance that a stock purchase will lose 20% of its value, a 2 in 10 chance that it will break even, a 4 in 10 chance that it will gain 15%, and a 3 in 10 chance that it will gain 30%. What is the expected retu
> Part 1: The Performance Lawn Equipment database contains data needed to develop a pro forma income statement. Dealers selling PLE products all receive 18% of sales revenue for their part of doing business, and this is accounted for as the selling expense
> In the scenario in Problem 2, what is the probability of drawing an ace first followed by a 2? How does this differ if the first card is replaced in the deck? Clearly explain what formulas you use and why
> In the roulette example described in Problem 7, what is the probability that the outcome will be green twice in a row? What is the probability that the outcome will be black twice in a row?In Problem 7Roulette is played at a table similar to the one in F
> In the scenario in Problem 2, what is the probability of drawing an ace first followed by a 2? How does this differ if the first card is replaced in the deck? Clearly explain what formulas you use and why
> Four key marketing decision variables are price (P), advertising (A), transportation (T), and product quality (Q). Consumer demand (D) is influenced by these variables. The simplest model for describing demand in terms of these variables isD = k - pP + a
> Identify some business applications in which judgmental forecasting techniques such as historical analogy and the Delphi method would be useful
> Elizabeth Burke wants to develop a model to more effectively plan production for the next year. Currently, PLE has a planned capacity of producing 9,100 mowers each month, which is approximately the average monthly demand over the previous year. However,
> The demand for airline travel is quite sensitive to price. Typically, there is an inverse relationship between demand and price; when price decreases, demand increases and vice versa. One major airline has found that when the price (P) for a round trip b
> One of PLE’s manufacturing facilities produces metal engine housings from sheet metal for both mowers and tractors. Production of each product consists of five steps: stamping, drilling, assembly, painting, and packaging to ship to its
> Construct a column chart for the data in the Excel file State Unemployment Rates to allow comparison of the June rate with the historical highs and lows. Would any other charts be better to visually convey this information? Why or why not?
> An online bookseller is considering selling an e-reader but will only do so if they have evidence that the proportion of customers that will likely purchase one is at least 0.4. Based on a survey of 25 customers, it was found that 8 of them stated that t
> In the Excel file Facebook Survey, use a PivotTable to find the average and standard deviation of hours online/week and number of friends for females and males in the sample
> Historical data show that customers who download music from a popular Web service spend approximately $26 per month, with a standard deviation of $4. Find the probability that a customer will spend at least $20 per month. How much (or more) do the top 10
> One of PLE’s manufacturing plants supplies various engine components to manufacturers of motorcycles on a just-in-time basis. Planned production capacity for one component is 100 units per shift, and the plant operates one shift per day. Because of fluct
> Part 1: PLE originally produced lawn mowers, but a significant portion of sales volume over recent years has come from the growing small-tractor market. As we noted in the case in Chapter 1, PLE sells their products worldwide, with sales regions includin
> Mountain Ski Sports, a chain of ski-equipment shops in Colorado, purchases skis from a manufacturer each summer for the coming winter season. The most popular intermediate model costs $150 and sells for $275. Any skis left over at the end of the winter a
> An information system consultant is bidding on a project that involves some uncertainty. Based on past experience, if all went well (probability 0.1), the project would cost $1.2 million to complete. If moderate debugging were required (probability 0.7),
> In reviewing the PLE data, Elizabeth Burke noticed that defects received from suppliers have decreased (worksheet Defects After Delivery). Upon investigation, she learned that in 2010, PLE experienced some quality problems due to an increasing number of
> For the profit model developed in Example 11.2 in Chapter 11 and the Excel model in Figure 11.4, suppose that the demand is triangular with a minimum of 35,000, maximum of 60,000 and most likely value of 50,000; fixed costs are normal with a mean of $400
> The Schoch Museum (see Problem 17 in Chapter 11) is embarking on a 5-year fundraising campaign. As a nonprofit institution, the museum finds it challenging to acquire new donors as many donors do not contribute every year. Suppose that the museum has ide
> A manufacturer of mp3 players is preparing to set the price on a new model. Demand is thought to depend on the price and is represented by the modelD = 2,500 - 3PThe accounting department estimates that the total costs can be represented byC = 5,000 + 5D
> A supermarket has been experiencing long lines during peak periods of the day. The problem is noticeably worse on certain days of the week, and the peak periods sometimes differ according to the day of the week. There are usually enough workers on the jo
> In the Excel file Economic Poll, find the proportions of each categorical variable
> For the Hyde Park Surgery Center scenario described in Problem 33 in Chapter 11, suppose that the following assumptions are made. The number of patients served the first year is uniform between 1,300 and 1,700; the growth rate for subsequent years is tri
> Develop and analyze a simulation model for Koehler Vision Associates (KVA) in Problem 13 of Chapter 11 with the following assumptions. Assume that the demand is uniform between 110 and 160 per week and that anywhere between 10% and 20% of prospective pat
> The Executive Committee of Reder Electric Vehicles (see Problem 16 in Chapter 11) is debating whether to replace its original model, the REV-Touring, with a new model, the REV-Sport, which would appeal to a younger audience. Whatever vehicle chosen will
> A university believes that the average retirement age among the faculty is now 70 instead of the historical value of 65. A sample of 85 faculty found that the average of their expected retirement age is 68.4 with a standard deviation of 3.6. Can the univ
> Create a bubble chart for the first five colleges in the Excel file Colleges and Universities for which the x-axis is the Top 10% HS, y-axis is Acceptance Rate, and bubbles represent the Expenditures per Student
> Elizabeth Burke has identified some additional questions she would like you to answer.1. Are there significant differences in ratings of specific product/service attributes in the 2014 Customer Survey worksheet?2. In the worksheet On-Time Delivery, has t
> Develop a simulation model for a 3-year financial analysis of total profit based on the following data and information. Sales volume in the first year is estimated to be 100,000 units and is projected to grow at a rate that is normally distributed with a
> The Kelly Theater produces plays and musicals for a regional audience. For a typical performance, the theater sells at least 250 tickets and occasionally reaches its capacity of 600 seats. Most often, about 450 tickets are sold. The fixed cost for each p
> A plant manager is considering investing in a new $30,000 machine. Use of the new machine is expected to generate a cash flow of about $8,000 per year for each of the next 5 years. However, the cash flow is uncertain, and the manager estimates that the a
> Develop a Monte Carlo simulation model for the garage-band in Problem 7 in Chapter 11 with the following assumptions. The expected crowd is normally distributed with mean of 3,000 and standard deviation 400 (truncate the distribution to have a minimum of
> In the Sales Transactions database, find the proportion of customers who used PayPal and the proportion of customers who used credit cards. Also, find the proportion that purchased a book and the proportion that purchased a DVD
> The manager of the extended-stay hotel in Problem 27 of Chapter 11 believes that the number of rooms rented during any given week has a triangular distribution with minimum 32, most likely 38, and maximum 50. The weekly price is $950 and weekly operating
> Determine if there is evidence to conclude that the mean GPA of males who plan to attend graduate school is larger than that of females who plan to attend graduate school using the data in the Excel file Graduate School Survey
> Use the Newsvendor Model spreadsheet to set up and run a Monte Carlo simulation assuming that demand is Poisson with a mean of 45 but a minimum value of 40 (use the lower cutoff parameter in the distribution dialog to truncate the distribution and ensure
> O’Brien Chemicals makes three types of products: industrial cleaning, chemical treatment, and some miscellaneous products. Each is sold in 55-gallon drums. The selling price and unit manufacturing cost are shown below:Fixed costs are as
> Sturgill Manufacturing, Inc. needs to predict the numbers of machines and employees required to produce its planned production for the coming year. The plant runs three shifts continuously during the workweek, for a total of 120 hours of capacity per wee
> In reviewing your previous reports, several questions came to Elizabeth Burke’s mind. Use point and interval estimates to help answer these questions.1. What proportion of customers rate the company with “top box” survey responses (which is defined as sc
> The Excel file Economic Poll provides some demographic and opinion data on whether the economy is moving in the right direction. Convert this data into an Excel table, and filter the respondents who are homeowners and perceive that the economy is not mov
> J&G Bank receives a large number of credit-card applications each month, an average of 30,000 with a standard deviation of 4,000, normally distributed. Approximately 60% of them are approved, but this typically varies between 50% and 70%. Each customer c
> A national homebuilder builds single-family homes and condominium-style townhouses. The Excel file House Sales provides information on the selling price, lot cost, type of home, and region of the country (Midwest, South) for closings during 1 month. Cons
> In the Excel file Bicycle Inventory, find the proportion of bicycle models that sell for less than $200
> Cruz Wedding Photography (see Problem 15 in Chapter 11) believes that the average number of wedding bookings per year can be estimated by triangular distribution with a minimum of 10, maximum of 22, and most likely value of 15. One of the key variables i
> Waring Solar Systems provides solar panels and other energy-efficient technologies for buildings. In response to a customer inquiry, the company is conducting a feasibility study to determine if solar panels will provide enough energy to pay for themselv
> For the Moore Pharmaceuticals model, suppose that analysts have made the following assumptions:• R&D costs: Triangular ($500, $700, $800) in millions of dollars• Clinical trials costs: Triangular ($135, $150, $160) in millions of dollars• Market size: No
> Automobiles have different fuel economies (mpg), and commuters drive different distances to work or school. Suppose that a state Department of Transportation (DOT) is interested in measuring the average monthly fuel consumption of commuters in a certain
> Adam is 24 years old and has a 401(k) plan through his employer, a large financial institution. His company matches 50% of his contributions up to 6% of his salary. He currently contributes the maximum amount he can. In his 401(k), he has three funds. In
> The retirement planning model described in Chapter 11 (Example 11.11) assumes that the data in rows 5–8 of the spreadsheet are the same for each year of the model. Modify the spreadsheet to allow the annual salary increases and return on investment to ch
> Review the retirement-planning situation described in Chapter 11 (Example 11.11). Modify the spreadsheet to include the assumptions that the annual salary increase is triangular with a minimum of 1%, most likely value of 3%, and maximum value of 5%, and
> PLE collects a variety of data from special studies, many of which are related to the quality of its products. The company collects data about functional test performance of its mowers after assembly; results from the past 30 days are given in the worksh
> For the Excel file Credit Approval Decisions, find 95% confidence and prediction intervals for the credit scores and revolving balance of homeowners and nonhomeowners. How do they compare?
> The Excel file Restaurant Sales provides sample information on lunch, dinner, and delivery sales for a local Italian restaurant. Develop 95% prediction intervals for the daily dollar sales of each of these variables and also for the total sales dollars o
> The Excel file EEO Employment Report shows the number of people employed in different professions for various racial and ethnic groups. Find the proportion of men and women in each ethnic group for the total employment and in each profession
> A financial advisor believes that the proportion of investors who are risk–averse (i.e., try to avoid risk in their investment decisions) is at least 0.7. A survey of 32 investors found that 20 of them were risk-averse. Formulate and test the appropriate
> Using the data in the worksheet Consumer Transportation Survey, develop 95% and 99% prediction intervals for the following:a. The hours per week that an individual will spend in his or her vehicleb. The number of miles driven per week
> A study of nonfatal occupational injuries in the United States found that about 31% of all injuries in the service sector involved the back. The National Institute for Occupational Safety and Health (NIOSH) recommended conducting a comprehensive ergonomi
> The Excel file Facebook Survey provides data gathered from a sample of college students. Create a scatter diagram showing the relationship between Hours online/week and Friends
> A marketing study found that the mean spending in 15 categories of consumer items for 297 respondents in the 18–34 age group was $91.86 with a standard deviation of $50.90. For 536 respondents in the 35+ age group, the mean and standard deviation were $8
> Total marketing effort is a term used to describe the critical decision factors that affect demand: price, advertising, distribution, and product quality. Let the variable x represent total marketing effort. A typical model that is used to predict demand
> The Excel file Baseball Attendance shows the attendance in thousands at San Francisco Giants’ baseball games for the 10 years before the Oakland A’s moved to the Bay Area in 1968, as well as the combined attendance for both teams for the next 11 years. D
> Using data in the Excel file Colleges and Universities, find 95% confidence intervals for the median SAT for each of the two groups, liberal arts colleges and research universities. Based on these confidence intervals, does there appear to be a differenc
> For the Store and Regional Sales database, apply a four-traffic light icon set to visualize the distribution of the number of units sold for each store, where green corresponds to at least 30 units sold, yellow to at least 20 but less than 30, red to at
> Lily’s Gourmet Ice Cream Shop offers a variety of gourmet ice cream and shakes. Although Lily’s competes with other ice cream shops and frozen yogurt stores, none of them offer gourmet ice creams with a wide variety of different flavors. The shop is also
> A marketing study of 800 adults in the 18–34 age group reported the following information:• Spent less than $100 but more than $0 on children’s clothing per year: 50 responses• Spent $100–$499.99 on children’s clothing per year: 275 responses• Spent $500
> Tanner Park (see Problem 14 in Chapter 11) is a small amusement park that provides a variety of rides and outdoor activities for children and teens. In a typical summer season, the number of adult tickets sold has a normal distribution with a mean of 20,
> The Excel file Restaurant Sales provides sample information on lunch, dinner, and delivery sales for a local Italian restaurant. Develop 95% confidence intervals for the mean of each of these variables, as well as total sales for weekdays and weekends. W
> Find the standard deviation of the total assets held by the bank in the Excel file Credit Risk Data.a. Treating the records in the database as a population, use your sample in Problem 2 and compute 90%, 95%, and 99% confidence intervals for the total ass
> A retailer believes that its new advertising strategy will increase sales. Previously, the mean spending in 15 categories of consumer items in both the 18–34 and 35+ age groups was $70.00.a. Formulate a hypothesis test to determine if the mean spending i
> Using the data in the Excel file Accounting Professionals, find and interpret 95% confidence intervals for the following:a. Mean years of serviceb. Proportion of employees who have a graduate degree
> If, based on a sample size of 200, a political candidate found that 125 people would vote for her in a two-person race, what is the 99% confidence interval for her expected proportion of the vote? Would she be confident of winning based on this poll?
> For a new product, sales volume in the first year is estimated to be 80,000 units and is projected to grow at a rate of 4% per year. The selling price is $12 and will increase by $0.50 each year. Per-unit variable costs are $3, and annual fixed costs are
> If, based on a sample size of 850, a political candidate finds that 458 people would vote for him in a two-person race, what is the 95% confidence interval for his expected proportion of the vote? Would he be confident of winning based on this poll? Use
> The Radio Shop sells two popular models of portable sport radios, model A and model B. The sales of these products are not independent of each other (in economics, we call these substitutable products, because if the price of one increases, sales of the
> Use PivotTables to find the number of sales transactions by product and region, total amount of revenue by region, and total revenue by region and product in the Sales Transactions database
> The data in the Excel file Church Contributions were reported on annual giving for a church. Estimate the mean and standard deviation of the annual contributions of all parishioners by implementing formulas (4.13) and (4.15) on a spreadsheet, assuming th
> Using the data in the Excel file Cell Phone Survey, apply ANOVA to determine if the mean response for Value for the Dollar is the same for different types of cell phones
> A garage band wants to hold a concert. The expected crowd is 3,000. The average expenditure on concessions is $15. Tickets sell for $10 each, and the band’s profit is 80% of the gate and concession sales, minus a fixed cost of $12,000. Develop a general
> A (greatly) simplified model of the national economy can be described as follows. The national income is the sum of three components: consumption, investment, and government spending. Consumption is related to the total income of all individuals and to t
> A company is trying to predict the long-run market share of a new men’s deodorant. Based on initial marketing studies, they believe that 35% of new purchasers in this market will ultimately try this brand. They believe that customers will purchase their
> MasterTech is a new software company that develops and markets productivity software for municipal government applications. In developing their income statement, the following formulas are used:Gross profit = net sales - cost of salesNet operating profit
> The admissions director of an engineering college has $500,000 in scholarships each year from an endowment to offer to high-achieving applicants. The value of each scholarship offered is $25,000 (thus, 20 scholarships are offered). The benefactor who pro
> For the new-product model in Problem 9, construct a tornado chart and explain the sensitivity of each of the model’s parameters on the NPV of profit.In Problem 9For a new product, sales volume in the first year is estimated to be 80,000 units and is proj
> Classify each of the variables in the Excel file Weddings as categorical, ordinal, interval, or ratio and explain why
> Return on investment (ROI) is computed in the following manner: ROI is equal to turnover multiplied by earnings as a percent of sales. Turnover is sales divided by total investment. Total investment is current assets (inventories, accounts rece
> For the garage-band model in Problem 7, construct a tornado chart and explain the sensitivity of each of the model’s parameters on total profit.In Problem 7A garage band wants to hold a concert. The expected crowd is 3,000. The average expenditure on con
> Each worksheet in the Excel file LineFit Data contains a set of data that describes a functional relationship between the dependent variable y and the independent variable x. Construct a line chart of each data set, and use the Add Trendline tool to dete
> The Hyde Park Surgery Center specializes in high-risk cardiovascular surgery. The center needs to forecast its profitability over the next 3 years to plan for capital growth projects. For the first year, the hospital anticipates serving 1,200 patients, w
> Think of any retailer that operates many stores throughout the country, such as Old Navy, Hallmark Cards, or Radio Shack, to name just a few. The retailer is often seeking to open new stores and needs to evaluate the profitability of a proposed location
