Final Computer Applications Project / Excel Spreadsheet

INSTRUCTIONS

• This sheet includes all of your sales data/figures starting in January and going through the present. Each company is only listed once and initially sorted alphabetically.
• First, name the table Customers
• Next, sort the table first by Payment Due Date and then secondarily by Payment Made Date (both Oldest to Newest)
• In Column H, you will use a function to determine if the payment was made on time (by the due date) – have the returning values be “On Time” or “Late”
• In Column L, you will use a function to calculate the total of the three items you are selling to each company then add Blue Gradient Fill Data Bars to Column L
• You have decided to run a promotion for your customers in which they must meet two criteria and if they meet both, they will receive a 15% discount on their next order. To receive this discount, they must have Paid On Time and purchased at least $1,500 worth of total merchandise from you. Use a function in Column M to determine if each has paid on time and spent at least $1,500 - Return either 15% or No Discount as the result
• Add a Total Row to the table and use it to calculate the grand total of the Total Sales (Column L) and a count of how many discounts have been awarded (Column M).

Next, you will create a PivotTable on Sheet3 using the information on your Customers table – Name your PivotTable Total Sales.

• On your PivotTable, add State, County, and Company Name to the Rows (in that order) then add Total Sales to Values.
• Update the Value Field Settings for your values.
• Change the order of the PivotTable to display TN first, GA second, and NC last
• Change the Report Layout to Tabular and remove all Subtotals
• Collapse all County fields
• Insert a Clustered Column Chart to demonstrate the total sales by county and then move the chart to the Dashboard worksheet A2:I18 (be sure to name your chart)
• While on the Dashboard worksheet, add a Slicer to the PivotChart based on State (place Slicer in J2:K8)

Return to the sheet with your Customers table and create another PivotTable using this data on Sheet4. Name the new PivotTable Sales by Month.

• Add the Payment Due Date to the Rows and each of your sales figures to the Values in order (Jacket, Hat, Bag, and then Total)
• Update the Value Field Settings for your values
• For the Payment Due Dates, Group the Field by month if this did not happen automatically (it should already be grouped if done correctly)
• Create a Line PivotChart using the data in the PivotTable then move the chart to the Dashboard worksheet A19:N36 (be sure to name your chart).
• While on the Dashboard, add a linear trendline to this new chart that forecasts Total Sales 2 periods forward

Move on to Sheet5. On this sheet, you are planning a purchase of a delivery van which you will be entirely financing through the dealership (i.e. the entire cost will be a loan).

• First, based on the figures given in C2:C6, determine your monthly payment using the appropriate function in C7.
• Next, fill in C14:G14 and C15:G15 with the appropriate functions to determine the cumulative payments toward interest and principal (use 0 for Type)
• Next, add functions to H14, H15, and H18/19 to show the total of interest payments, principal payments, and grand total of payments respectively.
• Lastly, you are determined to pay no more than $40,000 total between interest and principal. The only thing that can change is the total price of the van (Loan Amount) that you could negotiate with the dealer. Use Goal Seek to determine what price/loan would allow you to pay a total of $40,000 total between interest/principal. Add the result of the Goal Seek to G2; Leave C2 as $35,995.

Sheet6 is where you will use your past sales to project future growth and to set sales goals for each month moving forward.

• First, using the sales figures from January through April, autofill the rest of the months to see what could be expected for sales through December (Row 2)
• Next, you are determined to “beat the projection” and have $200,000 worth of sales by December. In Row 4, use a Linear Trend Interpolation to fill in the missing data
• Finally, in Row 6, use a Growth Trend Interpolation to fill in the missing data.
• --Create a Line Chart using the data in Row 6 from April to December
• --Move this chart to the Dashboard M2:T18. Format the chart to look professional, including changing the Bounds of the vertical axis to a minimum of 70,000.

On the Dashboard you will be adding a few final touches to make the workbook more approachable/functional for investors and/or buyers.

• Add your company name (and slogan if desired) using WordArt to the space in Row 1
• In cell(s) L11/12, add a reference to cell M2 in Sheet6 to demonstrate the expected monthly sales by December for your company
• Add VLOOKUP functions into cells L15, L16, and L17 to indicate the company name, if the company (in L14) paid in full, and their total sales.
• Record a Macro that takes the viewer to Cell B66 in the sheet with your Customers table (Sheet 2). Add a Button (Form Control) labeled “Our most important customer” to the range L3:L7 on the Dashboard with the Macro assigned to run when you click it.