Using MS Excel. Creating simple and complex formulas. Formatting for appeal and emphasis. Creating interpretive charts. Excel functions. VLOOKUP. Help using this info (in the pictures) creating an Excel please.
1. Financials spreadsheet Formulas: Complete the missing information using formulas a. In column R calculate sum of columns F-Q for each row of the sheet, Row 1 of column R name as Total b. Select all data for Financials and go to the Data tab. Use subtotal which is on far right of the data tab. For change in business unit, show the sum of the total column (column R) c. In Column U, Create Summary Year which lists out every year of the financials data. Do not use the forecast scenario rows for 2023. d. In Columns V,W, and X create Software, Advertising, and Hardware. e. Fill in the data with cell references to the results of the subtotals for each year and business unit. You should end up having Software, Advertising, and hardware totals for every year to generate a summary table. f. Use the summary table to insert recommended charts, clustered column. Call the chart "Annual P\&L", the categories are Software, Advertising, Hardware, the horizontal category is labeled with the years g. In Cell X51, enter the best P\&L year for software before this year 2. Formulas spreadsheet Enter a formula in the cells indicated - yellow background - using the formulas listed below and referencing the data for variables in column C. a. X=(X2X1)4+(YY)4 b. X=51hb2 3. VLOOKUP spreadsheet VLOOKUP sheet has theen setup with a pricing table for desks and lamps based on quantity purchased. Create VLOOKUP in the unit cost row to look up price based on quantity entered in row 10. In Row 12 calculate the total price based on quantity and the unit price. References: 1. Chapter 13 of the Fluency7 textbook 2. Recitations and Lectures Page 2 of 2 1. Financials spreadsheet Formulas: Complete the missing information using formulas a. In column R calculate sum of columns F-Q for each row of the sheet, Row 1 of column R name as Total b. Select all data for Financials and go to the Data tab. Use subtotal which is on far right of the data tab. For change in business unit, show the sum of the total column (column R) c. In Column U, Create Summary Year which lists out every year of the financials data. Do not use the forecast scenario rows for 2023. d. In Columns V,W, and X create Software, Advertising, and Hardware. e. Fill in the data with cell references to the results of the subtotals for each year and business unit. You should end up having Software, Advertising, and hardware totals for every year to generate a summary table. f. Use the summary table to insert recommended charts, clustered column. Call the chart "Annual P\&L", the categories are Software, Advertising, Hardware, the horizontal category is labeled with the years g. In Cell X51, enter the best P\&L year for software before this year 2. Formulas spreadsheet Enter a formula in the cells indicated - yellow background - using the formulas listed below and referencing the data for variables in column C. a. X=(X2X1)4+(YY)4 b. X=51hb2 3. VLOOKUP spreadsheet VLOOKUP sheet has theen setup with a pricing table for desks and lamps based on quantity purchased. Create VLOOKUP in the unit cost row to look up price based on quantity entered in row 10. In Row 12 calculate the total price based on quantity and the unit price. References: 1. Chapter 13 of the Fluency7 textbook 2. Recitations and Lectures Page 2 of 2
