Question: Use the Markdown Pricing Model spreadsheet model

> 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

> For the garage-band model you developed in Problem 7, define and run some reasonable scenarios using the Scenario Manager to evaluate profitability for the following scenarios:,,,

> A bank has historically found that the average monthly charges in recent years on its credit card were $1,350. With an improving economy, they suspect that this has increased. A sample of 42 customers resulted in an average monthly charge of $1,376.54 wi

> Use PivotTables to find the number of loans by different purposes, marital status, and credit risk in the Excel file Credit Risk Data. Illustrate the results on a PivotChart

> For the nonprofit ballet company fundraising model you developed in Problem 11, use a data table to show how the amount varies based on the number of solicitations.In Problem 11The director of a nonprofit ballet company in a medium-sized U.S. city is pla

> Develop a spreadsheet model to determine how much a person or a couple can afford to spend on a house. Lender guidelines suggest that the allowable monthly housing expenditure should be no more than 28% of monthly gross income. From this, you must subtra

> For the stockbroker model you developed in Problem 10, use data tables to show how the commission is a function of the number of calls made.In Problem 10A stockbroker calls on potential clients from referrals. For each call, there is a 10% chance that th

> For the Koehler Vision Associates model you developed in Problem 13, use data tables to study how revenue is affected by changes in the number of appointments accepted and patient demand.In Problem 13Koehler Vision Associates (KVA) specializes in laser-a

> Create a pie chart showing the breakdown of occupations for each year in the Science and Engineering Jobs Excel file, and contrast these with simple column charts

> Data obtained from a county auditor in the Excel file Home Market Value provide information about the age, square footage, and current market value of houses along one street in a particular subdivision. Considering these data as a population of homeowne

> The worksheet Base Data in the Excel file Credit Risk Data provides information about 425 bank customers who had applied for loans. The data include the purpose of the loan, checking and savings account balances, number of months as a customer of the ban

> In the Excel file Credit Risk Data, test the hypotheses that the number of months employed is the same for applicants with low credit risk as those with high credit risk evaluations. Use a level of significance of 0.01

> The weekly price at an extended-stay hotel (renting by the week for business travelers) is $950. Operating costs average $20,000 per week, regardless of the number of rooms rented. Construct a spreadsheet model to determine the profit if 40 rooms are ren

> Problem 16 in Chapter 1 posed the following situation: 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 airl

> Problem 15 in Chapter 1 posed the following situation: 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 t

> Using the matrix of intercity distances in the American Baseball League file, add list box form controls so that the user can choose two cities (using two list boxes) and find the distance between them. Ignore the model section in the worksheet

> A college is trying to determine if there is a significant difference in the mean GMAT score of students from different undergraduate backgrounds who apply to the MBA program. The Excel file GMAT Scores contain data from a sample of students. What conclu

> Create a new worksheet in the Retirement Portfolio workbook. In this worksheet, add a list box form control to allow the user to select one of the mutual funds on the original worksheet, and display a summary of the net asset value, number of shares, and

> Create a PivotTable to find the average amount of travel expenses for each sales representative in the Excel file Travel Expenses. Illustrate your results with a PivotChart

> Find the 10th and 90th percentiles and 1st, 2nd, and 3rd quartiles for the combined amounts of checking and savings accounts in the Excel file Credit Risk Data

> For the Pro Forma Income Statement model in the Excel file Net Income Models (Figure 11.7), add a scroll bar form control to allow the user to easily change the level of sales from 3,000,000 to 10,000,000 in increments of 1,000 and recalculate the spread

> A bank sampled its customers to determine the proportion of customers who use their debit card at least once each month. A sample of 50 customers found that only 12 use their debit card monthly. Find 95% and 99% confidence intervals for the proportion of

> Insert a spin button in the Moore Pharmaceuticals model to change the discount rate in cell B8 from 1% to 10% in increments of one-tenth

> Use the Excel Random Number Generation tool to generate 100 samples of the number of customers that the financial consultant in Problem 31 will have on a daily basis. What percentage will meet his target of at least 5?In Problem 31A financial consultant

> Develop a spreadsheet model for Problem 5 in Chapter 1. Apply the principles of spreadsheet engineering in developing your model. Use the spreadsheet to create a table for a range of prices to help you identify the price that results in the maximum reven

> Insert a spin button and scroll bar in the Outsourcing Decision Model to allow the user to easily change the production volume in cell B12 from 500 to 3000. Which one is easier to use? Discuss the pros and cons of each

> Apply the Data Validation tool to the President’s Inn Guest Database file with an error alert message box to ensure that dates are in the month of December and correctly entered, and that the number of guests entered is no greater than 6. Enter some fict

> The Schoch Museum 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 identified a pool of 8,000 potenti

> The Executive Committee of Reder Electric Vehicles 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 be produced for the next 4 year

> With the growth of digital photography, a young entrepreneur is considering establishing a new business, Cruz Wedding Photography. He believes that the average number of wedding bookings per year is 15. One of the key variables in developing his business

> The worksheet Purchasing Survey in the Performance Lawn Care database provides data related to predicting the level of business (Usage Level) obtained from a third-party survey of purchasing managers of customers Performance Lawn Care.8 The seven PLE att

> A popular soft drink is sold in 2-liter (2,000-milliliter) bottles. Because of variation in the filling process, bottles have a mean of 2,000 milliliters and a standard deviation of 20, normally distributed.a. If the process fills the bottle by more than

> Create a line chart for the closing prices for all years, and a stock chart for the high/low/close prices for August 2013 in the Excel file S&P 500

> Tanner Park 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 and children’s tickets sold are 20,000 and 10,000, respectively. Adult ticket prices are

> Koehler Vision Associates (KVA) specializes in laser-assisted corrective eye surgery. Prospective patients make appointments for prescreening exams to determine their candidacy for the surgery: if they qualify, a $250 charge is applied as a deposit for t

> Use PivotTables to construct a cross-tabulation for marital status and housing type in the Excel file Credit Risk Data. Illustrate the results on a PivotChart

> Identify each of the variables in the Excel file Credit Approval Decisions as categorical, ordinal, interval, or ratio and explain why

> A gasoline mini-mart orders 25 copies of a monthly magazine. Depending on the cover story, demand for the magazine varies. The gasoline mini-mart purchases the magazines for $1.50 and sells them for $4.00. Any magazines left over at the end of the month

> The director of a nonprofit ballet company in a medium-sized U.S. city is planning its next fundraising campaign. In recent years, the program has found the following percentages of donors and gift levels:Develop a spreadsheet model to calculate the tota

> A stockbroker calls on potential clients from referrals. For each call, there is a 10% chance that the client will decide to invest with the firm. Fifty-five percent of those interested are found not to be qualified, based on the brokerage firm’s screeni

> Develop a spreadsheet model for gasoline usage scenario, Problem 4 in Chapter 1, using the data provided. Apply the principles of spreadsheet engineering in developing your model.Problem 4 in Chapter 1Suggest some metrics that a manager of a fast-food re

> A manager needs to identify some information from the Purchase Orders Excel file but has only the order number. Modify the Excel file to use the VLOOKUP function to find the item description and cost per order for the following order numbers: Aug11008, S

> For the Colleges and Universities data, use XLMiner to find four clusters using each of the other clustering methods (see Figure 10.13); compare the results with Example 10.6.Figure 10.13,,,

> For the four clusters identified in Example 10.6, find the average and standard deviations of each numerical variable for the schools in each cluster and compare them with the averages and standard deviations for the entire data set. Does the clustering

> For the Excel file Colleges and Universities, normalize each column of the numerical data (i.e., compute a z-score for each of the values) and then compute the Euclidean distances between the following schools: Amherst, Cal Tech, and Duke

> A sample of 20 international students attending an urban U.S. university found that the average amount budgeted for expenses per month was $1612.50 with a standard deviation of $1179.64. Find a 95% confidence interval for the mean monthly expense budget

> In the Excel file Restaurant Sales, determine if the variance of weekday sales is the same as that of weekend sales for each of the three variables (lunch, dinner, and delivery)

> Compute the Euclidean distance between the following sets of points:a. (2,5) and (8,4)b. (2, -1, 3) and (8, 15, -5)

> Construct a variable plot for all the variables in the Excel file Banking Data

> Classify each of the data elements in the Sales Transactions database (Figure 1.1) as categorical, ordinal, interval, or ratio data and explain why

> Construct a scatterplot matrix for Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance in the Excel file Banking Data. What conclusions can you reach?

> Use PivotTables to construct a cross-tabulation for the purpose of the loan and credit risk in the Excel file Credit Risk Data. Illustrate the results on a PivotChart

> In the Excel file Banking Data, apply the following data visualization tools:a. Use data bars to visualize the relative values of Median Home Value.b. Use color scales to visualize the relative values of Median Household Wealth.c. Use an icon set to show

> Construct a parallel coordinates chart for Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance in the Excel file Banking Data. What conclusions can you reach?

> The Excel file Myatt Steak House provides 5 years of data on key business results for a restaurant. Identify the leading and lagging measures, find the correlation matrix, and propose a cause-and-effect model using the strongest correlations

> The Excel file Automobile Options provides data on options ordered together for a particular model of automobile. Develop a market basket analysis using the XLMiner association rules procedure with the input parameters support = 6 and confidence = 80

> Use the Excel file Banking Data.a. Construct a boxplot for the Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance.b. What observations can you make about these data?

> A sample of 33 airline passengers found that the average check-in time is 2.167. Based on long-term data, the population standard deviation is known to be 0.48. Find a 95% confidence interval for the mean check-in time. Use the appropriate formula and ve

> For the PC Purchase Data, identify association rules with the following input parameters for the XLMiner association rules procedure:a. Support = 3; confidence = 90,b. Support = 7; confidence = 90,c. Support = 3; confidence = 70,d. Support = 7; confidenc

> The Excel file Credit Risk Data provides a database of information about loan applications, along with a classification of credit risk in column L. Convert the categorical data into numerical codes as appropriate. Then apply logistic regression to classi

> Using the data in the Excel file Student Grades, which represent exam scores in one section of a large statistics course, test the hypothesis that the variance in grades is the same for both tests

> The Excel file Credit Risk Data provides a database of information about loan applications along with a classification of credit risk in column L. Convert the categorical data into numerical codes as appropriate. Sample 200 records from the data set. The

> Suggest some metrics that a manager of a fast-food restaurant, such as McDonald’s or Chipotle, might want to collect. Describe how the manager might use the data to facilitate better decisions

> Fruity Juices, Inc., produces five different flavors of fruit juice: apple, cherry, pomegranate, orange, and pineapple. Each batch of product requires processing in three departments (blending, straining, and bottling). The relevant data (per 1,000-gallo