Question 1

Use the table below to answer the following question(s).
Sheila joined Simsin Tradings at the age of 36 with a starting salary of $75,000. She expects a salary increase of 5 percent every year. Her retirement plan requires her to pay 9 percent of her salary, while the company matches it at 32 percent. She expects an annual return of 7 percent on her retirement portfolio. Using a predictive model for Sheila's first five years, calculate the following, assuming that the salary increases at the same rate every year, and the return of interest does not change. $\begin{array}{|l|l|}
\hline \text { Retirement Plan Model for Sheila } & \
\hline & \
\hline \text { Data } & \
\hline & \\hline \text { Retirement Contribution (percent of salary) } & 9 \text { percent } \
\hline \text { Employer Match } & 32 \text { percent } \
\hline \text { Annual Salary Increase } & 5 \text { percent } \
\hline \text { Annual Return on Investment } & 7 \text { percent } \
\hline
\end{array}$
-Calculate the employer contribution in Sheila's fourth year at Simsin.

Accepted Answer

A)   $546.98
B)   $703.26
C)   $2,500.47
D)   $2,160  C

Question 2

In Excel's Analytic Solver Platform, a parameter is a set of outputs in a model.

Accepted Answer

A) True 
 B)False  False

Question 3

Use a modern software tool to perform statistical calculations.
Use the table below to answer the following question(s).
Below is the profit model spreadsheet for the Lazarus Shoe Company producing their latest model of shoes for the month of January. $\begin{array}{|l|l|}
\hline\begin{array}{l}
\text { Profit Model for Lazarus } \
\text { Shoe Company for } \
\text { January }
\end{array} & \text { (All cost in } \$) \
\hline & \\hline \text { Unit Price } & 47 \
\hline \text { Unit Cost } & 22 \
\hline \text { Fixed Cost for Production } & 350,000 \
\hline \text { Demand } & 40,000 \
\hline & \\hline \text { Model } & \
\hline & \
\hline \text { Unit Price } & 47 \
\hline \text { Quantity Sold } & 38,000 \
\hline \text { Rev enue } & \\hline & \
\hline \text { Unit Cost } & 22 \
\hline \text { QuantityProduced } & 38,000 \
\hline \text { Variable Cost } & \
\hline \text { Fixed Cost } & 350,000 \\hline & \
\hline \text { Profit } & \
\hline
\end{array}$
-Calculate the revenue for units sold.

Accepted Answer

A)   $836,000
B)   $1,136,000
C)   $600,000
D)   $1,786,000  D

Question 4

Which of the following formula is used to make the recording decision in B20?

Accepted Answer

A)   =IF(B19>0,"In-house","Studio") 
B)   =IF(B19
C)   =SUM(B19
D)   =IF(B19>0,"Studio","In-house")

Question 5

Use a modern software tool to perform statistical calculations. Using the spreadsheet below to answer the following question(s).
The spreadsheet below shows the net income model for a company that sells shoes. $\begin{array}{|l|l|l|} 
\hline& \text { A } & \text { B } \
\hline 1 & \text { Net Income Model } & \
\hline 2 & & \
\hline 3 & \text { Data } & \\hline 4 & & \
\hline 5 & \text { Sales } & \$ 10,000,000 \
\hline 6 & \text { Cost of Goods Sold } & \$ 6,400,000 \\hline 7 & \text { Administrative Expenses } & \$ 500,000 \
\hline 8 & \text { SellingExpenses } & \$ 900,000 \
\hline 9 & \text { Depreciation Expenses } & \$ 750,000 \
\hline 10 & \text { Interest Expenses } & \$ 70,000 \
\hline 11 & \text { Taxes } & \$ 620,000 \\hline 12 & & \
\hline 13 & \text { Model } & \
\hline 14 & & \\hline 15 & \text { Gross Profit } & \$ 3,600,000 \
\hline 16 & \text { Operating Expenses } & \$ 2,150,000 \
\hline 17 & \text { Net Operating Income } & \$ 1,450,000 \
\hline 18 & \text { Eamings Before Taxes } & \$ 1,380,000 \
\hline 19 & & \
\hline 20 & \text { Net Income } & \
\hline
\end{array}$
-Which of the following formulas would be used to calculate the net income value using only the data value?

Accepted Answer

A)   =SUM(B5:B10) -B11
B)   =SUM(B5:B11) 
C)   =B5-SUM(B6:B11) 
D)   =B5-SUM(B6:B10) +B11

Question 6

Use the table below to answer the following question(s).
In the spreadsheet below, there is data on the price, cost, demand, and quantity produced for an item. There are also different "what if" values that can help a manager to calculate costs and revenue with variability in demand. $\begin{array}{|l|l|l|l|}
\hline & \text { A } & \text { B } & \text { C } \
\hline 1 & \text { Profit Model } & & \
\hline 2 & & & \\hline 3 & \text { Data } && \begin{array}{l}
\text { What-If } \text { Demand } \
\text { Values }
\end{array} \\hline 4 & & & 20,000 \
\hline 5 & \text { Unit Price (\$) } & 50 & 40,000 \
\hline 6 & \text { Unit Cost (\$) } & 25 & 55,000 \
\hline 7 & \text { Fixed Cost (\$) } & 550,000 & 60,000 \
\hline 8 & \text { Demand } & 60,000 & 65,000 \
\hline 9 & \text { Quantity Produced } & 55,000 & \
\hline 10 & & & \
\hline
\end{array}$
-Calculate the total revenue when the quantity produced is 55,000 and demand is 60,000.

Accepted Answer

A)   $1,375,000
B)   ($1,320,000) 
C)   $1,430,000
D)   $2,750,000

Question 7

Use the table below to answer the following question(s).
Dresden Pharmaceuticals has decided to go ahead and start clinical trials on a potential new drug. The total R&D costs are estimated to reach around $875,000,000 with clinical trials mounting to
$145,000,000. The current market size is estimated to be around 3,000,000 and is expected to grow at 4 percent every year. The market share Dresden hopes to capture in the first year is 7 percent, and is projected to grow by 25 percent each year for the next 4 years. A monthly prescription is anticipated to generate revenue of $420 while incurring variable costs of $150. A discount rate of 8 percent is assumed. $$\begin{array} { | l | l | } 
\hline \text { Dresden Pharmaceuticals } & \
\hline & \
\hline \text { Data } & \
\hline & \
\hline \text { Market Size } & 3,000,000 \
\hline \text { Unit (monthly Rx) revenue (\$) } & 420 \
\hline \text { Unit (monthly Rx) cost (\$) } & 150 \
\hline \text { Discount Rate (per cent) } & 8 \
\hline & \
\hline \text { Project Costs } & \
\hline \text { R\&D (\$) } & 875,000,000 \
\hline \text { Clinical Trials (\$) } & 145,000,000 \
\hline & \
\hline
\end{array}$$
-Calculate the annual revenue for the fourth year.

Accepted Answer

A)   $ 2,325,304,800
B)   $830,466,000
C)   $1,494,838,800
D)   $1,149,876,000

Question 8

Use the table below to answer the following question(s).
Sheila joined Simsin Tradings at the age of 36 with a starting salary of $75,000. She expects a salary increase of 5 percent every year. Her retirement plan requires her to pay 9 percent of her salary, while the company matches it at 32 percent. She expects an annual return of 7 percent on her retirement portfolio. Using a predictive model for Sheila's first five years, calculate the following, assuming that the salary increases at the same rate every year, and the return of interest does not change. $\begin{array}{|l|l|}
\hline \text { Retirement Plan Model for Sheila } & \
\hline & \
\hline \text { Data } & \
\hline & \\hline \text { Retirement Contribution (percent of salary) } & 9 \text { percent } \
\hline \text { Employer Match } & 32 \text { percent } \
\hline \text { Annual Salary Increase } & 5 \text { percent } \
\hline \text { Annual Return on Investment } & 7 \text { percent } \
\hline
\end{array}$
-What will be the amount of employee contribution to retirement plan when Sheila has reached the age of 38?

Accepted Answer

A)   $7,441.88
B)   $7,813.97
C)   $24,450
D)   $2381.40

Question 9

How can managers judge the validity of a model?

Accepted Answer

The answer of How can managers judge the validity of...

Question 10

Use the table below to answer the following question(s).
Dresden Pharmaceuticals has decided to go ahead and start clinical trials on a potential new drug. The total R&D costs are estimated to reach around $875,000,000 with clinical trials mounting to
$145,000,000. The current market size is estimated to be around 3,000,000 and is expected to grow at 4 percent every year. The market share Dresden hopes to capture in the first year is 7 percent, and is projected to grow by 25 percent each year for the next 4 years. A monthly prescription is anticipated to generate revenue of $420 while incurring variable costs of $150. A discount rate of 8 percent is assumed. $$\begin{array} { | l | l | } 
\hline \text { Dresden Pharmaceuticals } & \
\hline & \
\hline \text { Data } & \
\hline & \
\hline \text { Market Size } & 3,000,000 \
\hline \text { Unit (monthly Rx) revenue (\$) } & 420 \
\hline \text { Unit (monthly Rx) cost (\$) } & 150 \
\hline \text { Discount Rate (per cent) } & 8 \
\hline & \
\hline \text { Project Costs } & \
\hline \text { R\&D (\$) } & 875,000,000 \
\hline \text { Clinical Trials (\$) } & 145,000,000 \
\hline & \
\hline
\end{array}$$
-Calculate the annual cost incurred for the second year.

Accepted Answer

A)   $1,224,120,000
B)   $884,520,000
C)   $491,400,000
D)   $638,820,000

Question 11

Use the table below to answer the following question(s).
Dresden Pharmaceuticals has decided to go ahead and start clinical trials on a potential new drug. The total R&D costs are estimated to reach around $875,000,000 with clinical trials mounting to
$145,000,000. The current market size is estimated to be around 3,000,000 and is expected to grow at 4 percent every year. The market share Dresden hopes to capture in the first year is 7 percent, and is projected to grow by 25 percent each year for the next 4 years. A monthly prescription is anticipated to generate revenue of $420 while incurring variable costs of $150. A discount rate of 8 percent is assumed. $$\begin{array} { | l | l | } 
\hline \text { Dresden Pharmaceuticals } & \
\hline & \
\hline \text { Data } & \
\hline & \
\hline \text { Market Size } & 3,000,000 \
\hline \text { Unit (monthly Rx) revenue (\$) } & 420 \
\hline \text { Unit (monthly Rx) cost (\$) } & 150 \
\hline \text { Discount Rate (per cent) } & 8 \
\hline & \
\hline \text { Project Costs } & \
\hline \text { R\&D (\$) } & 875,000,000 \
\hline \text { Clinical Trials (\$) } & 145,000,000 \
\hline & \
\hline
\end{array}$$
-Calculate cumulative net profit at the fourth year.

Accepted Answer

A)   $1,073,538,462
B)   $3,189,634,800
C)   $1,312,041,240
D)   $1,494,838,800

Question 12

Which of the following formulas is used to calculate the total studio recording cost?

Accepted Answer

A)   =SUM(B6:B12) -B10
B)   (B6+B7-B16) B12
C)   B6+B7*B12
D)   B6+B7*B12-B16

Question 13

Use a modern software tool to perform statistical calculations.
Use the table below to answer the following question(s).
Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. $\begin{array}{|l|l|}\hline\text { Marked Down Pricing Model for } \
\text { Fiberia Accessories's new sweater} \
\hline & \
\hline \text { Data } & \
\hline \text { Retail Price } & \
\hline \text { Inventory } & \$ 65 \
\hline \text { Selling Season (days) } & 1500 \
\hline \text { Days at Full Retail } & 60 \
\hline \text { Intermediate Markdown } & 45 \
\hline \text { Clearance Markdown } & 25 \text { percent } \
\hline \text { Demand Function } & 65 \text { percent } \
\hline \text { A } & \
\hline \text { B } & 79.5 \
\hline
\end{array}$
-What is the average daily sale during the full retail sales period?

Accepted Answer

A)   15
B)   33.33
C)   8
D)   24.55

Question 14

Use a modern software tool to perform statistical calculations. Using the spreadsheet below to answer the following question(s).
The spreadsheet below shows the net income model for a company that sells shoes. $\begin{array}{|l|l|l|} 
\hline& \text { A } & \text { B } \
\hline 1 & \text { Net Income Model } & \
\hline 2 & & \
\hline 3 & \text { Data } & \\hline 4 & & \
\hline 5 & \text { Sales } & \$ 10,000,000 \
\hline 6 & \text { Cost of Goods Sold } & \$ 6,400,000 \\hline 7 & \text { Administrative Expenses } & \$ 500,000 \
\hline 8 & \text { SellingExpenses } & \$ 900,000 \
\hline 9 & \text { Depreciation Expenses } & \$ 750,000 \
\hline 10 & \text { Interest Expenses } & \$ 70,000 \
\hline 11 & \text { Taxes } & \$ 620,000 \\hline 12 & & \
\hline 13 & \text { Model } & \
\hline 14 & & \\hline 15 & \text { Gross Profit } & \$ 3,600,000 \
\hline 16 & \text { Operating Expenses } & \$ 2,150,000 \
\hline 17 & \text { Net Operating Income } & \$ 1,450,000 \
\hline 18 & \text { Eamings Before Taxes } & \$ 1,380,000 \
\hline 19 & & \
\hline 20 & \text { Net Income } & \
\hline
\end{array}$
-Which of the following formulas would be used to calculate the net income value using only the information in the Model, and not in the Data section?

Accepted Answer

A)   =B5-B17
B)   =B6-B15
C)   =B15-B16-B17+B18
D)   =B18-B11

Question 15

How does a tornado chart help make sense of inputs in analyzing data and models?

Accepted Answer

The answer of How does a tornado chart help make...

Question 16

The process of developing good, useful, and correct spreadsheet models is known as spreadsheet engineering.

Accepted Answer

A) True 
 B)False

Question 17

Use a modern software tool to perform statistical calculations.
Use the table below to answer the following question(s).
Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. $\begin{array}{|l|l|}\hline\text { Marked Down Pricing Model for } \
\text { Fiberia Accessories's new sweater} \
\hline & \
\hline \text { Data } & \
\hline \text { Retail Price } & \
\hline \text { Inventory } & \$ 65 \
\hline \text { Selling Season (days) } & 1500 \
\hline \text { Days at Full Retail } & 60 \
\hline \text { Intermediate Markdown } & 45 \
\hline \text { Clearance Markdown } & 25 \text { percent } \
\hline \text { Demand Function } & 65 \text { percent } \
\hline \text { A } & \
\hline \text { B } & 79.5 \
\hline
\end{array}$
-Calculate the total revenue during the discount sales period.

Accepted Answer

A)   $4,478.91
B)   $18,921.09
C)   $10,042.73
D)   $43,321.09

Question 18

Use a modern software tool to perform statistical calculations.
Use the table below to answer the following question(s).
Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. $\begin{array}{|l|l|}\hline\text { Marked Down Pricing Model for } \
\text { Fiberia Accessories's new sweater} \
\hline & \
\hline \text { Data } & \
\hline \text { Retail Price } & \
\hline \text { Inventory } & \$ 65 \
\hline \text { Selling Season (days) } & 1500 \
\hline \text { Days at Full Retail } & 60 \
\hline \text { Intermediate Markdown } & 45 \
\hline \text { Clearance Markdown } & 25 \text { percent } \
\hline \text { Demand Function } & 65 \text { percent } \
\hline \text { A } & \
\hline \text { B } & 79.5 \
\hline
\end{array}$
-Calculate the total revenue during the full retail sales period.

Accepted Answer

A)   $23,400
B)   $16,200
C)   $2,880
D)   $17,550

Question 19

Calculate the economic value of a loyal customer for a company given that the customer purchases, on an average, worth $43worth per visit and comes three times a year. The company's gross profit margin is 35 per cent with a customer defection rate of 0.4.

Accepted Answer

The answer of Calculate the economic value of a loyal...

Question 20

Use the table below to answer the following question(s).
In the spreadsheet below, there is data on the price, cost, demand, and quantity produced for an item. There are also different "what if" values that can help a manager to calculate costs and revenue with variability in demand. $\begin{array}{|l|l|l|l|}
\hline & \text { A } & \text { B } & \text { C } \
\hline 1 & \text { Profit Model } & & \
\hline 2 & & & \\hline 3 & \text { Data } && \begin{array}{l}
\text { What-If } \text { Demand } \
\text { Values }
\end{array} \\hline 4 & & & 20,000 \
\hline 5 & \text { Unit Price (\$) } & 50 & 40,000 \
\hline 6 & \text { Unit Cost (\$) } & 25 & 55,000 \
\hline 7 & \text { Fixed Cost (\$) } & 550,000 & 60,000 \
\hline 8 & \text { Demand } & 60,000 & 65,000 \
\hline 9 & \text { Quantity Produced } & 55,000 & \
\hline 10 & & & \
\hline
\end{array}$
-From the "what if" values, calculate the total cost when demand is 40,000.

Accepted Answer

A)   $ 2,000,000
B)   $1,925,000
C)   $1,100,000
D)   $75,000

Exam 11: Spreadsheet Modeling and Analysis