Integrated Computer Application - Budget Projection

Objective:

Before each fiscal year, Nike prepares a budget. Every department creates a proposed budget of income and expenses. A preliminary, high-level budget project will help determine if the departmental totals are in-line with expected changes.

Instructions:

1. Set Up the Worksheet:

   • Open Microsoft Excel and create a new workbook.
   • Save the workbook as "Nike_Budget_LastName_FirstName.xlsx".

2. Format:

   • Entering data and labels, change row height and column width, create column labels for headings, formulas: absolute, relative, and mixed format, goal seek formatting merge & center, font color and size, fill color, formatting styles.
3. Enter Data:

• • Set the tab name of the worksheet to budget projection.

  • Merge and center the worksheet title, Nike, across cells A1:D1, and use the title style. 

  • Merge and center the subtitle, budget projection (millions), in A2:D2, with the heading 3 style.

  • Enter the column headings current budget, projected increase, and new budget in B3, C3, and D3. 
  • Enter the data for the rest of the worksheet from the table below:

• • For the current budget column:

    • Enter the formula to calculate the gross profit, revenue – cost of sales.

    • Enter the formula to calculate the income before income taxes, gross profit – all expenses.

    • Enter the formula to calculate the effective tax rate, income taxes divided by income before taxes.

    • Enter the formula to calculate the net income from operations, income minus taxes.

    • Enter the formula to calculate the net income.

    • Net income from operations + discontinued operations.

• • Enter the formula to compute the projected increase (current budget * % change) cell for revenue, costs of sales, administrative expense, interest expense, other expense, income taxes, and net (LOSS) income from discontinued operations. To copy the formula, remember to use absolute references instead of relative references. 

  • Calculate the new budget column by creating a formula for each of these rows by adding the current budget to the project increase.

  • In the new budget column create formulas to calculate the gross profit, income before income taxes, Effective tax rate, net income from operations, and net income. These cells should total the new budget cells above them like the current budget total formulas. 

The blue arrows in this picture illustrate that the projected increase should only be used for some of the values in the new budget column. It should not be used for the totals: gross profit, Income before income taxes, effective tax rate, net income from operations, and net income.

• • Set the width of column A to 26 and use the wrap text where neied to display the entire label text.

  • Format the numeric values to account number format for revenue and net income.

  • Format all of the effective tax rate and % change cells to percentage style with four decimal places and the remaining numeric values to comma style.

  • Auto-fit the column width for columns B, C, and D.

  • Now we want to determine the % change if we wanted to have a new budget net income of $2,450. One way to do this is to change B20 until B18 (new budget net income) is $2,450. You can try a few times….this would be hard! We are going to use goal seek as a feature of Excel what-if analysis to answer our question.

  • Use goal seek to set the value of the net income in cell D18 to 2450 by changing the % change cell.

  • Save and close your workbook.

  • Submit your Excel file to the assignment space on Canvas.

By following these steps, you'll create a detailed budget projection and analysis using Excel, demonstrating your ability to apply financial functions and effectively present budget analysis.