new perspectives excel 2019 or module 8 end of module project 1 four w
Search for question
Question
New Perspectives Excel 2019 | Module 8: End of Module Project 1
Four Winds Energy
PERFORM WHAT-IF ANALYSES
sam
GETTING STARTED
Open the fileNP_EX19_EOM8-1_FirstLastName_1.xlsx, available for download from
the SAM website.
Files downloaded from the SAM website are safe and do not contain viruses, but due to
a recent Microsoft policy update, macros in downloaded files are disabled by default. To
complete this project, you will need to enable macros in the file. To enable macros on
this file:
О
о
For PC: Open Windows File Explorer and go to the folder where you saved the file.
Right-click the file and choose Properties from the context menu. At the bottom of
the General tab, select the Unblock checkbox and select Apply, and then click OK.
For Mac: If a dialog box about macros appears, click Enable Macros.
Save the file as NP_EX19_EOM8-1_FirstLastName_2.xlsx by changing the "1" to a
"2".
If you do not see the .xlsx file extension in the Save As dialog box, do not type it.
The program will add the file extension for you automatically.
With the file NP_EX19_EOM8-1_FirstLastName_2.xlsx still open, ensure that your
first and last name is displayed in cell B6 of the Documentation sheet.
о If cell B6 does not display your name, delete the file and download a new copy
from the SAM website.
This project requires you to use the Solver add-in. If this add-in is not available on the
Data tab in the Analyze group (or if the Analyze group is not available), install Solver as
follows:
о
In Excel, click the File tab, and then click the Options button in the left navigation
bar. Click the Add-Ins option in the left pane of the Excel Options dialog box. Click
the Manage arrow, click the Excel Add-Ins option, and then click the Go button. In
the Add-Ins dialog box, click the Solver Add-In check box and then click the OK
button. Follow any remaining prompts to install Solver.
PROJECT STEPS
1.
Madhu Patel is a sales analyst for Four Winds Energy, a manufacturer of wind energy
products, in San Antonio, Texas. Madhu is developing a workbook to analyze the
profitability of the company's wind turbines. She asks you to help her analyze the sales
data to determine how the company can increase profits.
Go to the Income Analysis worksheet, which lists the revenue and expenses for the
Boreas wind turbine and calculates the net income. Madhu wants to compare the
financial outcomes for varying amounts of turbines sold and identify the number of units
the company needs to sell to break even. Madhu has already entered formulas in the
range E5:H5 to extract data from the income analysis in the range B4:C26. In the range
E5:H14, create a one-variable data table using cell C5 as the Column input cell, to
calculate the revenue, expenses, and net income based on units sold.
CENGAGE New Perspectives Excel 2019 | Module 8: End of Module Project 1
2.
3.
4.
5.
Madhu asks you to provide a visual representation of the break-even data. Create a
Scatter with Straight Lines chart based on the units sold, revenue, and expenses in the
data table (range E4:G14). Resize and position the chart so it covers the range 13:N15.
Madhu wants to clarify the purpose of the chart and focus on the areas containing data.
Use Break-Even Point as the chart title. Change the Minimum bound of the horizontal
axis to 2,500 and let the Maximum bound adjust automatically. Change the Minimum
bound of the vertical axis to 2,000,000 and let the Maximum bound adjust
automatically.
Madhu also wants to examine how varying sales price and volume affects net income
from wind turbines. She has already entered the net income in cell E18 and sales prices
in the range F18:J18. For the range E18:J27, create a two-variable data table using the
price per unit (cell C6) as the Row input cell and the units sold (cell C5) as the Column
input cell. In cell E18, create a custom number format that displays "Units Sold" instead
of the net income value.
Madhu has also created two scenarios in the Income Analysis worksheet. The Current
scenario assumes the current values for units sold, price, and fixed expenses (salaries
and benefits, distribution, and miscellaneous). The Lower Price scenario assumes more
units sold at a lower price. She also wants to create a scenario that assumes fewer units
sold at a higher price. Create a scenario using the data shown in bold in Table 1 without
applying any scenarios.
Table 1: Income Analysis Scenario Values
Scenario name
Changing cells
Units sold (C5)
Price_per_unit (C6)
Salaries_and_benefits (C18)
Distribution (C19)
Miscellaneous (C20)
Raise Price
C5:C6, C18:C20
4500
1239
800000
510000
300000
6.
7.
8.
9.
Use the Scenario Manager to create a Scenario Summary report that summarizes the
effect of the Current, Lower Price, and Raise Price scenarios. Use the total revenue, total
expenses, and net income in the range C24:C26 as the result cells. Go to the Scenario
Summary worksheet and delete column D, which repeats the current values.
Return to the Income Analysis worksheet. Create a Scenario PivotTable report of the
three scenarios displaying the total revenue, total expenses, and net income (range
C24:C26) for each scenario.
Go to the Scenario PivotTable worksheet. Madhu wants to make the PivotTable easier to
interpret. Remove the filter from the PivotTable. Use Total Revenue as the row label in
cell B3, use Total Expenses as the row label in cell C3, and use Net Income as the
row label in cell D3. Display the Revenue, Expenses, and Net Income values in the
Currency number format with no decimal places. Display negative values in red,
enclosed in parentheses. Resize columns B:D to their best fit using AutoFit.
Madhu also wants to compare the three scenarios in a chart. Create a Clustered Column
PivotChart based on the PivotTable. Resize and position the chart so it covers the range
A8:E24.
CENGAGE New Perspectives Excel 2019 | Module 8: End of Module Project 1
10. Go to the Product Line worksheet, which lists three wind turbines that Four Winds
Energy produces and sells. Madhu wants to find the product mix that generates the most
net income for the company. Use Solver to maximize the percentage of difference
between the even and optimal product mixes (cell F23) by changing the optimal product
mix (range C10:E10) subject to the following constraints:
'
. The total units sold in the optimal product mix (cell D18) must be 4,750.
· The company needs to produce 1,200 or more of each turbine model, so the optimal
mix values for each model (range C10:E10) must be at least 1,200.
Those same values in the range C10:E10 must be integers.
The Remaining values for each assembled part (range J5:J13) must be greater than or
equal to zero because the company cannot produce more wind turbines than the parts
available.
11. Run Solver, keep the solution, and then return to the Solver Parameters dialog box.
Save the model to the range H17: H24, and then close the Solver Parameters dialog box.
Your workbook should look like the Final Figures on the following pages. Save your changes,
close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your
completed project.
CENGAGE New Perspectives Excel 2019 | Module 8: End of Module Project 1
*Final Figure 1: Scenario Summary Worksheet
1
2
1
- N
12356788
+ |
י
A
B
Scenario Summary
Changing Cells:
Units_sold
C
D
E
F
G
Current
Lower Price
Raise Price
Price_per_unit
4,750
$1,139
5,000
$1,039
4,500
$1,239
Salaries_and_benefits $ 833,500 $ 833,500
$800,000
9
Distribution
10
Miscellaneous
11
Result Cells:
12
Revenue
535,000
550,000
510,000
310,000
325,000
300,000
5,410,250
5,195,000 5,575,500
13
Expenses
5,193,500
5,408,500
4,940,000
14
15
16
Net_income
$216,750 $ (213,500) $635,500
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
17
18
19
20
Documentation
Scenario Summary
Scenario PivotTable
Income Analysis Product Line
CENGAGE New Perspectives Excel 2019 | Module 8: End of Module Project 1
Final Figure 2: Scenario PivotTable Worksheet
A
B
с
D
E
F
G
12345678927
10
Row Labels
Total Revenue Total Expenses Net Income
Current
Lower Price
6 Raise Price
$5,410,250
$5,195,000
$5,575,500
$5,193,500
$216,750
$5,408,500
($213,500)
$4,940,000
$635,500
Total Revenue Total Expenses Net Income
$6,000,000
11
12
$5,000,000
LLL
13
$4,000,000
14
15
$3,000,000
16
17
$2,000,000
18
19
$1,000,000
20
21
22
($1,000,000)
23
$C$5:$C$6, $C$18: $C$20
24
25
26
$0
Current
Lower Price
Raise Price
Values
■Total Revenue
■Total Expenses
■Net Income
Documentation
Scenario Summary Scenario PivotTable
Income Analysis
Product Lir
CENGAGE