1. Open a new Excel workbook and enter the list of revenues and expenses provided.

May June July august

Sales Revenue: $ $ $ $ Regular 4800 6000 9120 10800 Special 4500 6000 12000 15000 Expenses: Cost of Ingredients 3720 4800 8448 10320 Supplies 930 1800 3168 3870 Shop rental 1500 1500 1500 1500 Salaries and Wages 6000 6000 6000 6000 Telephone 100 100 100 100 Internet 90 90 90 90 Electricity 279 360 634 774 Water 230 180 317 387 Marketing & Advertising 1000 700 500 500 Insurances 180 180 180 180

Explanation: 3. Add a $ sign to all amounts by selecting the column(s) and formatting the cells as currency. 4. Convert the list into a table by selecting the range of data and clicking "Insert Table" from the "Insert" tab.

5. Use the following formulas/functions to calculate the required data: 5.1 Total sales (Regular+ Special) for each month: use the SUM function for each month's total. 5.2 Total expenses for each month: use the SUM function for each month's total.

Explanation: 5.3 Net income (Total sales- Total Expenses) for each month: subtract the total expenses from the total sales for each month. 5.4 Format your table so that it highlights the important figures and looks presentable by using appropriate fonts, font sizes, colors, and borders.

6. Create a bar chart for sales from May to August by selecting the range of data for the months and their corresponding total sales, and clicking "Insert Chart" from the "Insert" tab. Choose a suitable chart type and design, and customize the chart's appearance as desired.

Explanation: 7. Create a pie chart for May expenses by selecting the range of data for May's expenses and clicking "Insert Chart" from the "Insert" tab. Choose a suitable chart type and design, and customize the chart's appearance as desired. 8. Create a line chart for net income from May to August by selecting the range of data for the months and their corresponding net income, and clicking "Insert Chart" from the "Insert" tab. Choose a suitable chart type and design, and customize the chart's appearance as desired. Life

Expert Q&A

Find solutions to your homework

Question

(0)

1 Term Mar. 23 Computer Fundamentals- Final Assignment (40%) Your final assessment is based on the following scenario. Polar Frozen Pops opens its first store in Calgary in May 2022. It sells two types of fresh fruit-based ice cream (regular & special) to Calgarian kids. Below is the list of revenue and expenses for the stores first four months of operation (May- August 2022). May June July August Sales Revenue: $ $ $ $ Regular 4800 6000 9120 10800 Special 4500 6000 12000 15000 Expenses: Cost of Ingredients 3720 4800 8448 10320 Supplies 930 1800 3168 3870 Shop rental 1500 1500 1500 1500 Salaries and Wages 6000 6000 6000 6000 Telephone 100 100 100 100 Internet 90 90 90 90 Electricity 279 360 634 774 Water 230 180 317 387 Marketing & Advertising 1000 700 500 500 Insurances 180 180 180 180 Instruction: Your task is to record and analyse the stores revenue and expenses using Excel and summarise your findings in a Word Document. Below are the steps to complete the tasks. Part A- Excel 1. Open a new Excel workbook and enter the list of revenues and expenses above on an Excel sheet. 2. Label your sheet "Polar May - August". 3. Add a $ sign to all amounts. 4. Convert the list into a table. 5. Use the appropriate formula/functions to calculate the following: 5.1 Totals sales (Regular+ Special) for each month. 5.2 Total Expenses for each month 5.3 Net income (Total sales- Total Expenses) for each month. 5.4 Format your table so that it highlights the important figures and looks presentable. 6. Create a bar chart for sales from May to August 2 7. Create a pie chart for May expenses. 8. Create a line chart for net income from May to August. Note: o The charts should appear on the same sheet where the table is. You may position the charts below or beside the table. o Add a title for each chart, for example, "Polar Sales" for sales, "Polar Expense", and "Polar Net Income". o Add elements and labels to your chart. o Feel free to design your chart. For example, you can change the colours or design. Your goal should be to make your chart meaningful and presentable. 9. Save your workbook with the filename: First Name, Last Name - Final Assignment- Excel (You can also do this at the beginning) Part B- Word 1. Open a new Word document. 2. Write your complete name in the Header. 3. Write the document title Polar Frozen Pops Summary of Revenue and Expenses (May to August) 4. Present a summary of your Excel calculations a. Copy and paste the charts from the Excel sheet. (There will be three charts). b. Below each chart, write down brief descriptions/highlights or the key information contained in the chart. For example, you can describe the trend (increasing or decreasing?), the highest amount or the month of the highest amount, etc. You should provide at least two highlights for each chart. You can use the following questions as your guide in identifying which information to highlight. Of course, feel free to go beyond these. i. What is the trend of sales, expenses, or net income in the first four months of operation? ii. Which expense is the highest/lowest for May? iii. Which ice cream type sells more? 5. Format your document according to the following: a. The margin should be 2.54 cm all around. b. Font size is 14 for the title and 12 for the rest of the texts. c. Font style- Calibri or Arial d. Bold headings and subheadings if any. e. The title should be centred, and the rest of the texts aligned to the left. f. Feel free to use other formatting options in the paragraph command groups. For example, you can use the number, bullets, and spacing options if you wish. Your goal is to make the document presentable and readable. g. The whole document should be a maximum of two pages. 6. Save the word document with the filename: First Name, Last Name - Final Assignment- Word. (You can also do this at the beginning) Marking Guide Total Marks 100 marks (40% of your final grade in the course). Note: You should get at least 70 marks to pass this assessment. Items for Marking Mark Allocated MarkAwarded Comments Excel (60 marks) Sheet label 2 Workbook filename 3 Dollar signs for all figures 3 Total Sales 4 Total Expenses 4 Net income 4 Table (inc. design and presentation) 10 Bar chart (inc. design and presentation) 10 Pie Chart (inc. design and presentation) 10 Line Chart (inc. design and presentation) 10 Chart & Table Design marking Outstanding (shows creativity) 9-10 Basic (minimum or above requirement) 7-8.5 Poor (below minimum requirement) 0-7 WORD (40 marks) Correct filename 2.5 Header 2.5 Title 5 Charts 10 Chart Highlights/ Description 10 Formatting of the entire document 10 Outstanding (shows creativity) 9-10 Basic (minimum or above requirement) 7-8.5 Poor (below minimum requirement) 0-7 Expert Answer

This solution was written by a subject matter expert. It's designed to help students like you learn core concepts.

Step-by-step

1st step All steps Answer only Step 1/6

Part A - Excel: 1. Open a new Excel workbook and enter the list of revenues and expenses provided. 2. Label your sheet "Polar May - August".

Explanation: 3. Add a $ sign to all amounts by selecting the column(s) and formatting the cells as currency. 4. Convert the list into a table by selecting the range of data and clicking "Insert Table" from the "Insert" tab.

Step 2/6 5. Use the following formulas/functions to calculate the required data: 5.1 Total sales (Regular+ Special) for each month: use the SUM function for each month's total. 5.2 Total expenses for each month: use the SUM function for each month's total.

Explanation: 5.3 Net income (Total sales- Total Expenses) for each month: subtract the total expenses from the total sales for each month. 5.4 Format your table so that it highlights the important figures and looks presentable by using appropriate fonts, font sizes, colors, and borders.

Step 3/6 6. Create a bar chart for sales from May to August by selecting the range of data for the months and their corresponding total sales, and clicking "Insert Chart" from the "Insert" tab. Choose a suitable chart type and design, and customize the chart's appearance as desired.

Explanation: 7. Create a pie chart for May expenses by selecting the range of data for May's expenses and clicking "Insert Chart" from the "Insert" tab. Choose a suitable chart type and design, and customize the chart's appearance as desired. 8. Create a line chart for net income from May to August by selecting the range of data for the months and their corresponding net income, and clicking "Insert Chart" from the "Insert" tab. Choose a suitable chart type and design, and customize the chart's appearance as desired.

Step 4/6 Part B - Word: 1. Open a new Word document and write your complete name in the Header. 2. Write the document title "Polar Frozen Pops Summary of Revenue and Expenses (May to August)".

Explanation: 3. Present a summary of your Excel calculations by copying and pasting the three charts from Excel to Word. Below each chart, write brief descriptions/highlights or the key information contained in the chart. Provide at least two highlights for each chart, using the guide questions provided.

4. Format your document as follows:

Explanation: - Set the margin to 2.54 cm all around. - Use font size 14 for the title and 12 for the rest of the text. - Use font style Calibri or Arial. - Use bold headings and subheadings if any. - Center the title and align the rest of the text to the left. - Use other formatting options in the paragraph command groups, such as numbered or bulleted lists, and spacing options if desired. - Keep the document to a maximum of two pages.

5. Save the Word document with the filename: First Name, Last Name - Final Assignment- Word.