ACCT 311 · Study Guide 2

Interpretation & Excel decisions · Page references 2.1Excel solution (.xlsx)

Reference: ACCT 311 course site ↗

ACCT 311 · Labs 4 & 5

Read the evidence.
Make the decision.

Practice interpreting distributions and regression output, then use Excel to solve three business cases.

36answer items

25 points9 statistics · 16 Excel

3 Excel toolsGoal Seek · Solver · Scenarios

The statistical exhibits are synthetic teaching examples. This is a local review copy.

Your workbook

Download the workbook. Complete the labeled Goal Seek and Solver templates using the given values, then complete the scenario analysis in desktop Excel. Enter your answers on this page. You do not need to submit the workbook for this study guide. Round numerical answers to two decimal places unless whole units are requested.

Download Excel workbook

Student information

NameGonzaga emailSection

Start with the exhibits →

Read the distribution

Invoice collection time

Reference: Module 5.1 — Statistics concepts ↗

Each observation is one invoice; collection time is measured in days. In Excel’s Histogram output, Bin is the upper limit. Bin 20 counts times above 10 and up to 20 days. More counts times above the final bin limit.

Bin (days)	Frequency
10	8
20	13
30	4
40	1
50	2
60	1
70	0
80	1
More	0

Statistic	Value
Count	30
Mean	20.77
Median	15.50
Standard Deviation	16.52
Standard Error	3.02
Confidence Level (95.0%)	6.17

⋮

M010.5 points

Which description best matches the histogram?A. A longer tail extends toward shorter times.B. The frequencies are approximately uniform.C. The distribution has two equally large peaks.D. A longer tail extends toward longer times.

M020.5 points

Which summary best represents a typical invoice in this skewed sample?A. The median of 15.50 days.B. The mean of 20.77 days.C. The maximum of 75.00 days.D. The standard deviation of 16.52 days.

M030.5 points

The reported standard error is 3.02 days. What does it describe?A. The largest observed departure from the mean.B. Precision of the estimated population mean.C. Variation among individual observations.D. The width of the histogram’s modal bin.

M040.5 points

Which interpretation of the 95% confidence output is appropriate?A. The population mean equals 6.17 days.B. The interval for the mean is 20.77 ± 16.52 days.C. The mean interval is 14.60 to 26.93 days.D. 95% of observations fall within 14.60 to 26.93.

M050.5 points

What does the frequency beside Bin 20 mean in the Histogram output?A. That interval contains the mean of every invoice.B. That many dollars were collected during the interval.C. That interval contains only overdue invoices.D. 13 invoices took more than 10 days and at most 20 days.

M060.5 points

If a valid 75-day invoice is removed, what happens to the mean?A. It falls because an above-mean value is removed.B. It rises because the sample count falls.C. It stays fixed because the median is unchanged.D. It becomes equal to the old standard deviation.

M070.5 points

With similar variation among invoices, what does a larger sample generally do to Standard Error?A. It increases Standard Error and makes the mean less precise.B. It reduces Standard Error and makes the mean more precise.C. It leaves Standard Error unchanged because the units stay days.D. It makes Standard Error equal to the largest observation.

M080.5 points

Two samples have the same estimated mean but different 95% confidence intervals. What does the narrower interval indicate?A. The individual invoice times have become identical.B. The estimated mean must be larger for that sample.C. The population mean is estimated more precisely.D. Every invoice falls inside the narrower interval.

M251 point

An invoice took 75.00 days to collect. Using the descriptive statistics shown, calculate its z-score and decide whether to flag it as a potential outlier under the three-standard-deviation rule.A. z = 3.28; flag it as a potential outlier.B. z = 3.28; do not flag it as a potential outlier.C. z = −3.28; flag it as a potential outlier.D. z = 4.54; do not flag it as a potential outlier.

Read model evidence

Explain the regression output

Reference: Module 5.2 — Regression in Excel ↗

Outcome: Service cost ($). One observation is one service week. Both models use the same 40 observations. Observed technician hours range: 10–50. Observed urgent jobs range: 0–7. Use α = 0.05 for significance questions.

Simple model

Regression statistics	Value
Multiple R	0.95
R Square	0.90
Adjusted R Square	0.90
Standard Error	179.56
Observations	40

ANOVA	df	SS	MS	F	Significance F
Regression	1	11,028,229.97	11,028,229.97	342.03	1.35e-20
Residual	38	1,225,248.49	32,243.38
Total	39	12,253,478.45

Term	Coefficient	Std. Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	437.16	65.64	6.66	7.124e-08	304.27	570.04
Technician hours	41.74	2.26	18.49	1.35e-20	37.17	46.31

Multiple model

Regression statistics	Value
Multiple R	0.99
R Square	0.97
Adjusted R Square	0.97
Standard Error	98.52
Observations	40

ANOVA	df	SS	MS	F	Significance F
Regression	2	11,894,338.40	5,947,169.20	612.70	4.361e-29
Residual	37	359,140.05	9,706.49
Total	39	12,253,478.45

Term	Coefficient	Std. Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	180.00	45.15	3.99	0.0003029	88.52	271.48
Technician hours	42.00	1.24	33.91	1.803e-29	39.49	44.51
Urgent jobs	65.00	6.88	9.45	2.118e-11	51.06	78.94

Prediction case: Technician hours = 30; Urgent jobs = 3. Actual outcome = 1,775.00. Use the multiple model for this case.

⋮

M130.5 points

How should the 42.00 coefficient on technician hours be interpreted?A. One more hour raises urgent jobs by 42.00.B. An extra hour changes the outcome by 42.00 percent.C. The average outcome for all observations is 42.00.D. One more hour is associated with 42.00 more dollars, holding urgent jobs fixed.

M140.5 points

Using the multiple regression output, predict Service cost ($) for a week with 30 Technician hours and 3 Urgent jobs. Enter dollars to two decimal places.Answer

M150.5 points

Which interpretation of the prediction-case residual is correct?A. The residual equals the predicted outcome of 1,635.00.B. Actual is 140.00 above predicted.C. Actual is 140.00 below predicted.D. The model explains 140.00% of variation.

M160.5 points

What does the multiple model’s R-squared describe?A. The next outcome will rise by 97.1%.B. The model causes 97.1% of the outcome.C. About 97.1% of sample outcome variation is explained.D. About 97.1% of predictions are exactly correct.

M180.25 points

What does the added predictor’s p-value support?A. The added predictor has evidence of a nonzero coefficient at 5%.B. The added predictor’s coefficient is known without uncertainty.C. The added predictor has been shown to cause the outcome.D. The added predictor lacks evidence of a nonzero coefficient at 5%.

M200.25 points

Which statement describes the regression line?A. The line passing through the largest observed cost.B. The line whose intercept is closest to zero.C. The line with the smallest total of squared prediction errors.D. The line with the smallest sum of predictor values.

M210.25 points

One actual cost is $100 above the regression line and another is $100 below it. What does this show about the model’s errors?A. They cancel, so the model predicted both costs exactly.B. The positive error matters more than the negative error.C. The negative error can be ignored when judging the fit.D. Both are prediction errors, even though their signs cancel.

M220.25 points

A manager predicts cost for 90 technician hours. What is the main concern?A. 90 hours is outside the observed predictor range.B. 90 hours forces the model’s R-squared to zero.C. The intercept no longer appears in the equation.D. An out-of-range prediction must equal the sample mean.

Excel · Goal Seek

Brightline Print Studio · target profit

Reference: Module 5.3 — Goal Seek, Solver & Scenarios ↗

Complete this task in Excel · Goal Seek

Open your downloaded Excel workbook. If you have not downloaded it yet, use the single download button on the Start page.
Open the Goal Seek worksheet. In desktop Excel, build the model from the given assumptions and use Goal Seek to find the required input.
Return to this page and enter your results and interpretation answers below.

One workbook contains all three Excel tasks.

This workbook is for practice. You do not need to upload it to Canvas.

Brightline sells printed display packages. The fixed assumptions are listed below and in the workbook. Design your own Excel model, then use Goal Seek to find the sales volume needed to reach the profit target. Evaluate whether the plan meets the capacity limit. Packages must be sold in whole units.

Input	Value
Price per unit ($)	48
Variable cost per unit ($)	19
Fixed costs ($)	7250
Target profit ($)	6000
Maximum units	450

⋮

G011 point

Which Goal Seek setup answers the volume question?A. Set revenue to the target by changing fixed cost.B. Set units to the target by changing price.C. Set profit to zero by changing variable cost.D. Set profit to the target by changing units.

G021 point

What unrounded-volume result does Goal Seek find? Report to two decimal places.Answer

G031.5 points

What is the operational conclusion?A. The decimal result represents a feasible whole-unit plan.B. 457 units are required; this exceeds the 450-unit limit.C. 456 units suffice because ordinary rounding is allowed.D. 450 units achieve exactly the stated target.

G041.5 points

At the 450-unit limit, what does your model imply?A. Profit is $13,050.00, above target.B. Profit is $-5,800.00, below target.C. Profit is $5,800.00, below target.D. Profit is $6,000.00, exactly at target.

Excel · Solver

Summit Display Kits · product mix

Reference: Module 5.3 — Goal Seek, Solver & Scenarios ↗

Complete this task in Excel · Solver

Open your downloaded Excel workbook. If you have not downloaded it yet, use the single download button on the Start page.
Open the Solver worksheet. In desktop Excel, build the model from the given assumptions and use Solver to find the best production mix.
Return to this page and enter your results and interpretation answers below.

One workbook contains all three Excel tasks.

This workbook is for practice. You do not need to upload it to Canvas.

Complete the labeled Excel template using the fixed assumptions below. Use Solver to recommend a product mix. Decide which cells represent the decision, the objective, and each constraint. Keep the solution, save the Solver settings, and create an Answer Report as evidence.

Per unit	Standard	Deluxe
Contribution ($)	40	65
Labor hours	2	4
Material units	3	2
Demand cap	50	35

Available: 160 labor hours and 150 material units. Choose nonnegative whole-unit quantities to maximize total contribution. No additional fixed cost changes with the mix.

⋮

S011 point

Use Solver to find the optimal mix and generate an Answer Report worksheet in Excel. Which mix does your solution recommend?A. 50 Standard and 0 DeluxeB. 34 Standard and 23 DeluxeC. 50 Standard and 35 DeluxeD. 0 Standard and 35 Deluxe

S021 point

What total contribution does the optimal mix earn ($)?Answer

S031 point

What does the optimal plan imply about unused resources?A. 160 labor hours and 148 material units remain.B. 10 labor hours and 2 material units remain.C. 5 labor hours and 7 material units remain.D. 0 labor hours and 2 material units remain.

S041 point

Why not simply make only the product with the higher unit contribution?A. Resource requirements and demand limits also affect the best mix.B. Unit contribution measures all resource tradeoffs by itself.C. Demand caps determine profit without considering quantities.D. Each product uses the same amount of every resource.

S051 point

At the recommended mix of 34 Standard and 23 Deluxe kits, how many Standard kits remain below the demand cap?Answer

S061 point

Why can the plan not use the remaining 2 material units to make another kit?A. The plan has reached both product demand caps.B. Each additional kit requires more than 2 material units.C. All 160 labor hours are used, and either kit requires labor.D. Solver requires every resource to have some unused capacity.

Excel · Scenario Manager

Meadow Event Rentals · alternative assumptions

Reference: Module 5.3 — Goal Seek, Solver & Scenarios ↗

Complete this task in Excel · Scenario Manager

Open your downloaded Excel workbook. If you have not downloaded it yet, use the single download button on the Start page.
Open the Scenarios worksheet. In desktop Excel, create the specified scenarios with Scenario Manager and generate a Scenario Summary.
Return to this page and enter your results and interpretation answers below.

One workbook contains all three Excel tasks.

This workbook is for practice. You do not need to upload it to Canvas.

Use the Scenarios worksheet. Store all three named scenarios using units, price, and variable cost as changing cells. Generate an actual Scenario Summary with profit as the result cell. Fixed cost is $12,000; target profit is $6,000. Save the scenarios and the generated summary.

Scenario	Units	Price ($)	Variable cost ($)
Base	800	40	17
Strong demand	1000	39	18
Cost pressure	750	40	22

⋮

C010.5 points

What is profit under the Cost pressure scenario ($)?Answer

C021 point

Which scenarios meet or exceed the target profit?A. Base, Strong demand onlyB. All three scenariosC. Base and Cost pressure onlyD. Cost pressure only

C031 point

Which statement explains the highest-profit scenario?A. Profit differences arise only from changes in fixed cost.B. Strong demand has enough volume to offset its lower contribution per unit.C. The scenario with the highest price must have the highest profit.D. The scenario with the lowest unit cost must have the highest profit.

C041 point

How should the Scenario Summary inform planning?A. It identifies the most likely outcome from the three cases.B. It finds the profit-maximizing values of all inputs.C. It compares outcomes conditional on the specified assumptions.D. It assigns a probability to each scenario’s profit.

C051.5 points

Under Cost pressure, hold units and price fixed. Use Goal Seek to find the maximum unit variable cost that would meet target profit ($).Answer

Reference: ACCT 311 course site ↗

Finish

Review your answers

0 of 36 questions answered.

Unanswered: M01 M02 M03 M04 M05 M06 M07 M08 M25 M09 M10 M11 M12 M13 M14 M15 M16 M18 M20 M21 M22 G01 G02 G03 G04 S01 S02 S03 S04 S05 S06 C01 C02 C03 C04 C05

The answer key and animated Excel solutions are always available through Solutions above. The workbook is for practice and does not need to be uploaded.

Your preliminary practice score is out of 25.

Local review copy. Use Check practice locally to review your answers.

All questions · jump to an answer

M01 M02 M03 M04 M05 M06 M07 M08 M25 M09 M10 M11 M12 M13 M14 M15 M16 M18 M20 M21 M22 G01 G02 G03 G04 S01 S02 S03 S04 S05 S06 C01 C02 C03 C04 C05

Reference: ACCT 311 course site ↗

Study Guide 2

Answer key & Excel solutions

The answer key and worked solutions are always available. The instructor solution workbook includes the original screenshots, Solver Answer Report, and Scenario Summary. No submission or password is needed.

Read the evidence.Make the decision.

Your workbook

Student information

Invoice collection time

Compare service teams

Explain the regression output

Simple model

Multiple model

Brightline Print Studio · target profit

Complete this task in Excel · Goal Seek

Summit Display Kits · product mix

Complete this task in Excel · Solver

Meadow Event Rentals · alternative assumptions

Complete this task in Excel · Scenario Manager

Review your answers

Answer key & Excel solutions

Read the evidence.
Make the decision.