8.9 Putting it all together

Putting it all together

Listing 3 shows an example program for Tasmanian Traders purchasing department. They would like to sell Mama s Raspberry Syrup, but Mama s Megacorp wants a $500 "marketing fee" before Tasmanian Traders can sell Mama s product. So the purchasing department has asked for an analysis of the situation. They want to know how many units of Mama s Raspberry Syrup they need to sell before they break even, and they want to see a breakdown of the sales of all condiments by year, so they can figure out whether the number required to break even is within the normal sales of all condiments. It has to look good, because Tasmanian Traders VP of Purchasing will take this report with her when she meets with representatives of Mama s Megacorp to finalize the deal.

Here s a solution. Use a PivotTable to provide the sales breakdown, and then use the GoalSeek method to find the break-even number. Once the number is calculated, annotate it with a striking star shape to draw attention to the quantity required. Then add the TasTrade logo. Figure 11 shows a report that will not only satisfy the requirements for the needed information, but also has visual appeal.

Listing 3 is available as XLSample2.PRG in the Developer Download files available at www.hentzenwerke.com.

 

Listing 3. Tasmanian Traders analysis worksheet for a new product. This sample demonstrates creating a PivotTable, using Goal Seek, and adding pictures and shapes. Figure 11 shows the results.

* Clean out any existing references to servers.

* This prevents memory loss to leftover instances.

RELEASE ALL LIKE o*

* For demonstration purposes, make certain objects

* available after this program executes.

PUBLIC oExcel, oBook, oSheet2, oPivotTable

#DEFINE xlDatabase 1

#DEFINE xlDataField 4

#DEFINE xlEdgeBottom 9

#DEFINE xlContinuous 1

#DEFINE msoShape24PointStar 95

#DEFINE autoIn2Pts 72

#DEFINE xlHAlignCenter -4108

#DEFINE xlVAlignCenter -4108

CLOSE DATA

OPEN DATABASE (_SAMPLES + "\TasTrade\Data\TasTrade")

SELECT Customer.Company_Name, ;

Customer.Country, ;

CMONTH(Orders.Order_Date) AS Order_Month, ;

YEAR(Orders.Order_Date) AS Order_Year, ;

Products.Product_Name, ;

Order_Line_Items.Quantity, ;

Order_Line_Items.Unit_Price * Order_Line_Items.Quantity ;

AS Total_Price;

FROM Orders, Order_Line_Items, Customer, Products ;

WHERE Order_Line_Items.Order_ID = Orders.Order_ID ;

AND Orders.Customer_ID = Customer.Customer_ID ;

AND Order_Line_Items.Product_ID = Products.Product_ID ;

AND Products.Category_ID = " 2" ;

INTO CURSOR Pivot

COPY TO (CURDIR() + "Pivot") TYPE XL5

oBook = GETOBJECT(CURDIR() + "Pivot.XLS")

* Open the workbook, and best-fit all the columns.

* Ensure there are two sheets: Pivot for the data

* and another for the "presentation"

WITH oBook

oExcel = .Application

.Application.Visible = .T.

.Windows[1].Activate()

.Sheets[1].Range("A1:G310").Columns.AutoFit()

oSheet2 = .Sheets.Add()

ENDWITH

* On the presentation worksheet, add the logo and title

WITH oSheet2

* Add the TasTrade Logo

LogoFile = _SAMPLES + "\TasTrade\Bitmaps\TTradeSm.bmp"

.Shapes.AddPicture(LogoFile, .T., .T., ;

0.25 * autoIn2Pts, 0.25 * autoIn2Pts, ;

1.00 * autoIn2Pts, 1.00 * autoIn2Pts)

* Title the worksheet

WITH .Range["C2"]

.Value = "Analysis for Mama's Raspberry Syrup"

.Font.Size = 18

.Font.Bold = .T.

ENDWITH

ENDWITH

* Create a new PivotTable

oSourceData = oBook.Sheets[2].Range["A1:G" + ;

ALLTRIM(STR(RECCOUNT("Pivot") + 1))]

oDestination = oSheet2.Range["C4"]

oPivotTable = oBook.Sheets[1].PivotTableWizard(xlDatabase, ;

oSourceData, oDestination, "Analysis", .T., .T.)

WITH oPivotTable

.AddFields("Product_Name", "Order_Year")

.PivotFields["Quantity"].Orientation = xlDataField

* Make the headings look nice

.PivotFields["Product_Name"].Name = "Product Name"

.PivotFields["Order_Year"].Name = "Year"

.PivotFields["Sum of quantity"].Name = "Quantity Sold"

ENDWITH

WITH oSheet2

* Format the columns a bit wider

.Columns[4].ColumnWidth = 9

.Columns[5].ColumnWidth = 9

.Columns[6].ColumnWidth = 9

.Columns[7].ColumnWidth = 9

.Columns[8].ColumnWidth = 9

* Find the break-even point with Goal Seek

* Title

.Range["C21"].Value = "Break-Even Analysis"

.Range["C21"].Font.Bold = .T.

* Revenue

.Range["C22"].Value = "Price per Unit"

.Range["D22"].Name = "UnitPrice"

.Range["D22"].Value = 10

.Range["D22"].NumberFormat = "$###.00"

.Range["C23"].Value = "Units Sold"

.Range["D23"].Name = "UnitsSold"

.Range["D23"].Value = 100

.Range["D23"].NumberFormat = "###.0"

.Range("C23:D23").Borders(xlEdgeBottom).LineStyle = xlContinuous

.Range["C24"].Value = "Total Revenue"

.Range["D24"].Name = "TotalRevenue"

.Range["D24"].Value = "= UnitPrice * UnitsSold"

.Range["D24"].NumberFormat = "$##,###.00"

* Costs

.Range["C26"].Value = "Cost per Unit"

.Range["D26"].Name = "UnitCost"

.Range["D26"].Value = 7.23

.Range["C27"].Value = "Marketing Fee"

.Range["D27"].Name = "MarketingFee"

.Range["D27"].Value = 500

.Range("C27:D27").Borders(xlEdgeBottom).LineStyle = xlContinuous

.Range["C28"].Value = "Total Costs"

.Range["D28"].Name = "TotalCost"

.Range["D28"].Value = "= (UnitCost * UnitsSold) + MarketingFee"

.Range["D26:D30"].NumberFormat = "$##,###.00"

* Profit

.Range["C30"].Value = "Profit"

.Range["D30"].Name = "Profit"

.Range["D30"].Value = "= TotalRevenue - TotalCost"

* Perform the Goal Seek

.Range["Profit"].GoalSeek(0, .Range("UnitsSold"))

* Add the shape with text indicating how many need

* to be sold to break even.

oShape = .Shapes.AddShape(msoShape24PointStar, ;

4.8 * autoIn2Pts, 3.5 * autoIn2Pts,;

2.0 * autoIn2Pts, 2.0 * autoIn2Pts)

cQuantity = ALLTRIM(STR(.Range["UnitsSold"].Value))

WITH oShape.TextFrame

WITH .Characters

.Text = "Sell " + cQuantity + " units to break even"

.Font.Color = RGB(255, 0, 0) && Red

.Font.Size = 12

.Font.Bold = .T.

ENDWITH

.HorizontalAlignment = xlHAlignCenter

.VerticalAlignment = xlVAlignCenter

ENDWITH

ENDWITH

Figure 11. TasTrade s analysis worksheet for a new product. The Goal Seek feature provides a break-even analysis, the results of which are highlighted in the shape. The number of units required can be compared to the PivotTable that contains the past sales history of all condiments. And the bitmap makes it look nice.

This chapter covers a few of the major analysis tools available in Excel. We ve touched on the more commonly used analysis tools to give you a taste of the techniques for working in Excel via Automation. There are additional tools available, which require similar techniques. The next chapter covers another kind of analysis tool: charting. Excel has a powerful charting engine to graph the results of the data analysis done in Excel.

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved