COSC1371                               Project-6               Due: Thursday, October 20, 2011.

                               Net Income Optimization

Objectives:

  1.    Using EXCEL in generating two spreadsheets for business analysis.

  2.    Entering labels, values and formulas into spreadsheet cells.

  3.    Computing Cost of Goods Manufactured, Cost of Goods Sold, Gross Margin and Net Income.

  4.    Finding the Optimal NET INCOME under the assumption that the Sales will be increased 

        by 10% each time

        a. Advertising cost is doubled and

        b. Purchases of Raw Materials is increased by 10% and

        c. Direct Labor is increased by 10%

Steps:

  1.    Open the EXCEL from the Programs Folder.

  2.    Create the initial spreadsheet by entering the given data for the Fareway Incorporated.

        Into a cell, you enter a number without $ or a label (text) or a formula or date (such as 

        6/22/2011)

a.  To enter a label or a number, just click a cell and type in the data and press

the ENTER key.

        b. To enter a formula, click a cell and type '=' sign in the

           very first position of the cell followed by the formula like: "=F5+F6". 

           In order to enter a new data value or edit an existing data value, EXCEL needs to be 

           in the “Edit” mode.

           There are three ways to enter the “Edit” Mode:

1.  Click the Cell and click the Formula Bar. Make the changes and then click 

Enter button on the left side of Formula Bar. 

2.  Double-click the Cell and make the changes and press the Enter key.  

3.  Click the Cell and then press F2 Key. Make the changes and press the

Enter key.  

          Once in the "Edit" mode, clicking another cell will insert the clicked cell reference into the 

          formula for the cell you are editing. So, be careful.

  3.    Name this spreadsheet file: ORIGINAL.XLSX (by "SAVE AS" Command)

        Note that "XLSX" is the default file extension in EXCEL, and so you 

        do not have to type it.

  4.    Rename this file as OPTIMAL.XLSX using the same Command.

  5.    Modify the second file (OPTIMAL.XLSX) by repeatedly adjusting the 

        following four items until the NET INCOME does not increase any more:

        a. Increase the following three items by 10% by Formulas:

             Purchases of Raw Materials

             Direct Labor

             Sales

        b. Double the Advertising by a formula.

        Note that only the first repetition may need some editing of cells while subsequent 

        repetitions may need only copying the first repetition.

        NOTE: The optimal NET INCOME of $176,998 will be obtained when

              above two steps are repeated four times at which the

              Advertising Cost will be $48,000.

  6.    Turn in your USB containing the two spreadsheet files, as usual:

               ORIGINAL.XLSX

               OPTIMAL.XLSX

  Some useful shortcut keys:

        CTRL+Home:    The Cell A1             Tab: Next (Right) cell of same row    

        CTRL+End:     Make the rightmost, lowermost active corner of the spreadsheet

                      (the intersection of the last column and row that contains a data

                      value) the active cell. Does not move to XFD1048576 unless that cell

                      contains a data value. 

        Home:         Move the active cell to the column A of the current row   

        CTRL+R-Arrow: Last column (XFD, 16384th) of current row

        CTRL+L-Arrow: First column of current row

        CTRL+U-Arrow: Previous entry of the same column up to the first row

        CTRL+D-Arrow: Next entry of the same column up to the last row, 1048576th.

        Dragging "Fill Handle": Copying a cell to any number of adjacent cells

        F2:           Entering "Edit" mode

  Some useful functions:

        SUM(), AVERAGE(), MAX(), MIN()

        COUNT()        // counting numbers and dates.

        IF(Condition, TrueValue, FalseValue)

        SUMIF(range, criteria, sum_range)

        STDEV()

        PMT(rate,nPer,pv,fv,type)      //last two arguments optional

        FV(rate,nPer,Pmt,pv,type)      //last two optional

        DDB, SYD(cost,salvage,life,period)    //DDB can have fifth argument.

        SLN (cost,salvage,life)