Chapter AE, Problem AE7.1

Explanation of Solution

a.

Creating worksheet for each sales region:

Step1: Open excel file.

Step2: Save the file into a particular location and name it as “workfile.xlsx”.

Step3: Rename “sheet1” as...

Explanation of Solution

b.

Importing word file:

Step1: Open the file “Ch07Ex01_U10e” and choose “Layout” option from the menu bar. Select “Convert to Text” option from it.

Screenshot of the “Ch07Ex01_U10e” file

Step2: A new pop up window will appear. In that, select “commas” option. Click “OK” button after selecting.

Screenshot of the “Ch07Ex01_U10e” file

Step3: Save the file as a text file with the name “Ch07Ex01_U10eR1”.  Remove the contents other than “Region 1 2018 Sales Summary” data.

Step4: To import text file, open the file “workfile.xlsx”. Select “Data” from the menu bar. In that, select “From Text” option.

Screenshot of the excel file

Step5: Choose the text file and click “Import” button.

Screenshot of the excel file

Step6: A new dialog box appears. Click “Next” button.

Screenshot of the excel file

Explanation of Solution

c.

Computing discrepancies:

Step1: Add a column named “Average” in “Region 1” sheet. To compute average for every month for “Region 1”: Use the formula “((B7-B6)+(C7-C6)+(D7-D6)+(E7-E6)+(F7-F6)+(G7-G6)+(H7-H6))/7” to compute monthly average for “Jan-18”. Similarly calculate average for every month.

Screenshot of the excel file

Explanation of Solution

d.

Computing Adjusted forecast:

Step1:

In order to compute adjusted forecast, find the average of Forecast sales and Actual sales for every month, and total forecast sales for each month.

Compute Average Forecast sale by using the below formula:

$\begin{array}{l}Avg.ofForecastSale\left(AvgofFS\right) =(ForecastSale(product1+\mathrm{...}+product7))/7\\ \left(AvgofFS\right) =AVERAGE\left(B5:H5\right)\\ \left(AvgofFS\right) =10+20+5+12+20+5+34=>15.14\end{array}$

Compute Average Actual sale by using the below formula:

$\begin{array}{l}Avg.of\text{ Actual }Sale\left(Avgof\text{ A}S\right) =(ActualSale(product1+\mathrm{...}+product7)/7)\\ \left(Avgof\text{ A}S\right)=AVERAGE\left(B6:H6\right)\\ \left(Avgof\text{ A}S\right)=12+21+5+12+21+5+36=>16.00\end{array}$

Compute monthly forecast sales total by using the below formula:

$\begin{array}{l}MonthlyForecastsaletotal\text{=ForecastSale(product1+}\mathrm{}\end{array}$

Explanation of Solution

e.

Total sales projections:

Step 1: Take a fresh sheet and name it as “graph”. In that sheet, calculate total sales projections for every region. For region 1, the total projection sale is calculated by adding all the values from the column “Monthly Forecast Sale total”. Do this for the two other regions.

Screenshot of the excel file

Explanation of Solution

f.

Draw Bar graph:

Step 1: In the sheet “graph”, choose “Insert” option from the menu bar and select “Bar” option. A graph platform will appear. Place it in a position as shown below.

Screenshot of the excel file

Screenshot of the excel file

Step 2: Select data from Region 1 and paste it in the graph as shown below...