Exam 2: Introduction to Spreadsheet Modeling

Which of the following is not one of the required arguments for a VLOOKUP function

Exhibit 2-2 A small sporting goods company is considering investing $2000 in a project at the start of year 1 that will produce volleyballs over the next five years. The company plans to produce and sell 200 volleyballs in the first year, and expects that volume to grow by 10% each year thereafter. The unit selling price forecast the company has developed is $20 in year 1, $22 in year 2, $25 in year 3, $28 in year 4, and $31.50 in year 5. Variable costs are forecast to be $15 per unit produced, and there will be a fixed overhead cost in each year of $500.  (Unless otherwise indicated, assume that all cash flows occur at the end of the year.) -[Part 1] Refer to Exhibit 2-2.Use the graphing function in Excel to construct a scatterplot of forecasted price versus time,and fit a linear trendline to the data.What are the coefficients of the linear model,and what is the MAPE of a linear model forecast,compared to the company's forecast

In Excel terminology,the unknown value used in Goal Seek is called the input cell.

To evaluate which of a set of curves fits the data best,we can use:

Exhibit 2-2 A small sporting goods company is considering investing $2000 in a project at the start of year 1 that will produce volleyballs over the next five years. The company plans to produce and sell 200 volleyballs in the first year, and expects that volume to grow by 10% each year thereafter. The unit selling price forecast the company has developed is $20 in year 1, $22 in year 2, $25 in year 3, $28 in year 4, and $31.50 in year 5. Variable costs are forecast to be $15 per unit produced, and there will be a fixed overhead cost in each year of $500.  (Unless otherwise indicated, assume that all cash flows occur at the end of the year.) -Refer to Exhibit 2-2.Use the above information to develop a simple cash flow proforma sheet,and then apply Excel's NPV function to calculate the project value assuming a 10% discount rate.What is your answer

Much of the power of spreadsheet models derives from their flexibility.

Exhibit 2-1 A t-shirt company is planning a production run for an event where the attendance (and thus demand for t-shirts) is uncertain. The event planners have indicated that they think the attendance will be 500, 750 or 1000, with probabilities of 30%, 50% and 20% respectively. The company must pre-order the blank t-shirts (cost=$5 per shirt) and it can sell finished shirts for $12 apiece. Any finished shirts that cannot be sold at the event can be sold for $2 apiece to a used clothing vendor. -Refer to Exhibit 2-1.What Excel function is useful for calculating the expected value of demand for t-shirts What is the expected demand

Exhibit 2-1 A t-shirt company is planning a production run for an event where the attendance (and thus demand for t-shirts) is uncertain. The event planners have indicated that they think the attendance will be 500, 750 or 1000, with probabilities of 30%, 50% and 20% respectively. The company must pre-order the blank t-shirts (cost=$5 per shirt) and it can sell finished shirts for $12 apiece. Any finished shirts that cannot be sold at the event can be sold for $2 apiece to a used clothing vendor. -Refer to Exhibit 2-1.What Excel function is useful for calculating revenue Explain why it is useful.

Estimating the relationships between variables in a spreadsheet model can be done using:

The HLOOKUP function works exactly the same as the VLOOKUP function,except the lookup table is arranged in columns instead of rows.