Reference no: EM132300334
Business Computing Assignment -
Excel Assignment 2 - Modules 3 and 4
Part 1 (Module 3) -
Your bicycle business Bike City, seems to be doing well. You have stores, in 6 cities in Western Canada, that rent a variety of bikes/stationary bikes, and sell bikes and related clothing and gear. You want to analyze income for the first two quarters of this business year, and calculate projected sales for the upcoming 3rd quarter.
1. Open the starting file Bike City Sales.xlsx
Review the document contents.
Save it with a new filename based upon the following instructions:
Save the file as: 1 Firstname Lastname DescriptiveFilename.xlsx
- Replace Firstname with your first name.
- Replace Lastname with your last name.
- Replace DescriptiveFilename with a suitable name of your own choice. Do not use exactly 'Bike City
Sales' but choose one to three words that clearly describe the purpose of the file.
2. In cell A1, replace Firstname Lastname with your own first name and last name.
Change the sheet tab name from Store Sales to: Firstname Lastname Store Sales (use your own first name and last name).
3. In the centre footer of the worksheet, insert Firstname Lastname (your first and last names).
4. You forgot to include taxes.
-Insert 3 new columns between columns F and G.
-In the range G3:I3, enter the text in the headers as shown in the screenshot.
(Don't worry - you will take care of formatting after a few more steps.)
5. In row 1, remove and reapply Merge and Center to include the entire range A1:I1.
6. Both taxes are payable on rentals and sales.
First calculate the GST as follows: (Note: the GST rate is defined in cell B2)
-In cell G4, create a formula to calculate GST for the first row's total. Fill down the formula in the range G5:G9.
Required: Your formula must include a reference to cell B2, such that the formula will be correct when filled down.
7. Next, calculate the Provincial Sales Tax (PST). The PST rate varies for each province.
The PST rate for each province is found in a table on the sheet 'Lookup Tables'.
Using the province code beside each store, employ the VLookup() function to select the correct PST rate.
-In cell H4, create a formula to calculate PST for the first row's total. Use VLookup() in the formula. Fill down the formula in the range H5:H9.
Required: Your formula must include a reference to the lookup table (i.e. absolute address), such that the formula will be correct when filled down.
8. In cell I4, use a function to calculate the total for the first row, including taxes.
Fill this function down through the range I5:I9. Note formatting.
9. Fill the Sum function in cell F10 through the range F10:I10.
Ensure all columns are wide enough to properly display data.
10. In the range F3:I10, adjust formatting to match the screenshot. Pay close attention to use of borders, bold formatting and dollar signs.
Examine the screenshot to ensure all other formatting in the range A1:I10 is correct.
11. You want to do some projections for the 3rd Quarter.
In cell L23, create a formula that increases the Quarter 2 value (found in cell L13) by the per cent indicated in cell N20.
Example: the result in cell L23 for a 4% increase should be $1,445.07.
Fill the formula across and down to complete the table.
Required: Your formula must include a reference to cell N20, such that the formula will be correct when filled across and down to complete the table.
Tip: Be sure formatting matches the screenshot.
Note: All your numbers will not yet match the screenshot
12. Test your Year 3 projections by changing the value in cell N20 to 5%.
Your results for the Year 3 Projections table should match the screenshot. If your results do not match, then it's time to do some checking!
13. You notice the results in the main table (range A1:H8) are much different than the screenshot!
What could be wrong? Investigate, and fix the problem in the table so the formulas work as intended.
Be sure to maintain all formatting when fixing the error.
Tip: Begin your investigation by examining the formula in cell C4.
Tip: Examine all formatting on the sheet (deductions for formatting that does not match the screenshot)
14. Select cell A1.
Ensure your worksheet is in Normal view.
Set zoom to 100%.
Save and close your file.
Part 2 (Module 4) -
You are thinking of creating a new Bike City store in Brandon, Manitoba. You have located a good building in a good location.
You wish to calculate the mortgage plus the total cost of buying - monthly and annually. The building is selling for $325,000. The seller has provided the building's monthly operating costs: i.e.. property taxes, his insurance costs and his average maintenance costs
1. Open the starting file New Store Purchase - Brandon.xlsx
Review the document contents.
Save it with a new filename based upon the following instructions:
Save the file as: 2 Firstname Lastname New Store Purchase - Brandon.xlsx
Replace Firstname with your first name.
Replace Lastname with your last name.
2. In cell A2, type your Firstname Lastname
3. The building for your possible future store is selling for $325,000. You can make a $65,000 downpayment. You bank offers mortagages at 3.25%. You decide on a 15 year mortgage, making monthly payments, at the end of each month.
Enter the above information in the Mortage Terms table.
In cell B6, calcuate the net purchase price, which will become the amount of your mortgage.
4. In cell E5, calculate the monthly mortgage payment, based upon the mortage terms. Ensure the result is expressed as a positive number.
5. In cells F5 to F8, caculate the annual costs from the monthly costs, and in F9, sum the annual costs.
6. In the centre footer of the worksheet, insert Firstname Lastname (your first and last names).
Part 3 (Module 4) -
As owner of Wood Fired Pizzas restaurants, you need to examine how your sales channels and food menus are performing. To help with your sales analysis, you will perform a number of calculations and create useful charts.
Tip: You are required to match all formatting to the screenshots. You may want to do that as you work your way through each question, or you may want to leave it until the end.
1. Open the starting file Wood Fired Pizzas.xlsx.
Review the document contents.
Save it with a new filename based on the following instructions:
Save the file as: 3 Firstname Lastname DescriptiveFilename.xlsx
Replace Firstname with your first name.
Replace Lastname with your last name.
Replace DescriptiveFilename with a suitable name of your own choice. Do not include the full term
Wood Fired Pizzas but choose one to three words that clearly describe the purpose of the file.
2. Edit the title by typing your Firstname and Lastname in front of the word Wood Fired Pizzas.
3. In cell A1, type your Username. Note that the text only displays in the formula bar, because the font and cell shading are the same color. Keep it that way.
4. Use a function to calculate each Pizza Restaurants' sales total for each menu.
5. Use a function to calculate the total sales from all food menus for each of the three sales channels (be sure to also calculate totals for each of the four restaurants).
Do not copy functions to blank rows.
6. Calculate the TOTAL sales from all three sales channels for each food menu type.
Do not include blank cells in your calculation.
7. Use a function to calculate the average pizza restaurant revenue for each food menu type (be sure to also calculate the average of the restaurant totals in column I).
8. You want to compare total sales for each of the four stores to the overall average in-store sales.
Use a function to insert the word Low if total sales for each store are less than the overall in-store average. Insert the word Okay if this condition is not met.
Required: In your function, you must include a cell reference to the overall in-store average (cell I17) in such a way that the function produces the correct result when filled down.
Note: No marks will be given for simply typing in the words.
9. You want a chart to help you more easily interpret the data.
Insert a 3-D clustered column chart that displays the sales amounts for each channel, broken down by menu type.
Tip: For the restaurant sales channel, use the restaurant totals.
Move and resize the chart to match the screenshot.
Change the chart type to a 2-D clustered column (instead of 3-D).
10. Edit the chart title to read: Menu Sales by Sales Channel.
Apply the chart style that most closely resembles the screenshot.
Decrease the title font size by one
Use the built-in tool to change the chart legend position to Top.
11. Your first chart is so helpful, that you want to create a second chart!
Insert a 2-D clustered column chart that compares the restaurant sales totals for all 4 restaurants.
Move and resize the chart to the position shown in the screenshot.
12. Edit the chart title of the second chart to read: Restaurant Sales Comparison
Apply the chart style that most closely resembles the screenshot.
Change the size of the title to be the same as the title on the chart beside it.
13. Display data labels as shown in the screenshot.
Change the format of the vertical axis values to have a maximum of $125,000, major units of $25,000 and to display $.
14. You would like to drill down on the sales figures to analyze restaurant sales more closely. To help with this, you have decided to create another chart. As you create this third chart, be sure to refer closely to the screenshot entitled Restaurant Sales Chart Sheet.
Using the type of pie chart shown, display the same data.
Apply the chart style that most closely resembles the screenshot.
Edit the chart title to read: Restaurant Sales - by Menu Type.
15. Use the appropriate tool to place the chart on its own chart sheet (not as an embedded object on a new worksheet).
Name the new sheet: Restaurant Sales.
Move the chart sheet so it is the last sheet in the workbook.
16. Format chart to match screenshot, including: Data labels, Legend, Rotation, Exploded piece of pie.
17. On the Sales Breakdown worksheet: Below the two column charts, in the space provided, answer the data analysis questions. Tip: Use the charts as well as the numeric data to find the correct answers.
18. IMPORTANT Final check: all formatting, spelling, other errors. This process should happen every time you create a finished product.
Carefully check your work, referring frequently to the screenshots and instructions.
While no marks are given for this requirement, as much as 10% of the overall mark may be deducted.
19. On the Sales Breakdown worksheet: Select the title cell (B1).Ensure your worksheet is in Normal view.
Set zoom to 100%. Save and close your file.
Excel Assignment 3 - Modules 5 and 6
Part A - Module 5
You will analyze data from a fictional survey that asked 100 people about their favourite gourmet pizza place in Winnipeg.
1. Open the file Assignment 3 Starting File Part A.xlsx and save it as Firstname Lastname Pizza.xlsx
Review the document contents.
2. In cell B3 of the Documentation sheet, type the date you started working on the assignment.
In cell B4, type your Firstname Lastname. (Minimum one mark deduction per cell if not done.)
3. Switch to the Results sheet.
Freeze Panes so that you can still see the first three rows when you scroll to the bottom of the list.
4. Convert the data on the Results sheet to an Excel table.
Select a table style of your choice that is not the default.
Name the table: FirstName_Results.
Adjust column width to match the screenshot. Note how table headers are wrapped.
5. You realize that one response was forgotten when the workbook was first created.
Add the following record to the bottom of the table: Respondent ID 244 of St. James spent $65 at Pizza 21st Century.
6. Use the Remove Duplicates tool to delete any duplicate records from the table. HINT: After completing this task, 101 unique records should remain.
7. Sort the data by Respondent's Part of the City (A to Z), then by Favourite Gourmet Pizza Place (A to Z), then Amount Spent in Past Month (Largest to Smallest).
8. Make a copy of the Results sheet, place it at the end of the workbook, and name it Filter.
Use 2 Slicers to filter the records to show only respondents who live in East Kildonan or River Heights, who prefer Pizza 21st Century. Apply a slicer style of your choice to each.
Next, use the filter arrow to further filter the results to display only those who spent $30 or more.
Required: a number filter must be used.
Use the Total Row tool to display both the number of respondents who meet these criteria as well as the total amount spent.
Adjust functions and labels as required to match the screenshot.
9. You would like to use the Subtotal tool to summarize your data by Favourite Gourmet Pizza Place.
First, copy the Results sheet, rename the copy Subtotals, and place the new sheet at the end of the workbook.
Use the Subtotal tool to find total Amount Spent, broken down by Favourite Gourmet Pizza Place. Use the screenshot as a guide to help you, and remember that you must complete two preliminary steps before you use the Subtotal command!)
Display Level 2: all the Subtotals and the Grand Total.
Resize the columns as needed to ensure no information is cut off.
10. Using the data on the Results sheet, create a PivotTable to show average of Amount Spent by Favourite Gourmet Pizza Place.
Place the PivotTable on a new sheet named Average. Move this sheet to the end of the workbook.
Use the screenshot to help you set up your PivotTable.
Complete all formatting to match the screenshot.
11. Create a PivotChart (type 3-D Pie) which is based on the PivotTable that you created in the previous step.
Size and position the chart on the same sheet, as shown in the screenshot.
Remove the chart title.
Apply Chart Style 3.
Modify chart labels to match the screenshot.
Delete the legend.
Explode the piece of pie representing the largest %.
12. IMPORTANT Final check: all formatting, spelling, other errors. Ensure column width and row height allows for all information to be properly displayed.
This process should happen every time you create a finished product.
Carefully check your work, referring frequently to the screenshots and instructions.
While no marks are given for this requirement, as much as 10% of the overall mark may be deducted.
13. Go to the Documentation sheet, and click in cell A1. Save and close your file.
Part B - Module 6
You own a bike shop with five regional stores in Manitoba. You want to analyze June to September sales of various products from the five stores and create a summary.
1. Open the file Assignment 3 Starting File Part B.xlsx and save it as Firstname Lastname Bikes.xlsx
Review the workbook contents.
2. Group the five worksheets together (Interlake, Northern, Parkland, Pembina Valley, Winnipeg).
In cell B11, indent the word "Total"
For the ranges C11:F11 and G5:G11, use functions to calculate the total sales revenue.
Apply the Accounting number format with 0 decimals to all numeric data.
Use borders to separate the Totals and labels from the rest of the data. See screenshot for clarification if needed.
Make any additional formatting changes to the worksheets to match the screenshot.
Verify that your changes appear on all of the worksheets.
HINT: Consider column width, text alignment, etc.
3. You would like to create a new sheet to summarize the information from the five regional stores.
Copy one of the other worksheets, and place it as the first sheet. Rename the copied sheet as Summary.
Modify the text in rows 2 and 3 of the Summary sheet to match the screenshot.
4. Before creating summary functions, modify the structure of the Summary sheet to match the screenshot: delete the month columns, remove the functions in the right column, adjust labels to match screenshot.
5. On the Summary sheet, beginning in cell C5, use a SUM function with a 3-D reference to calculate total revenue from all stores for each sales item.
Use a function to calculate the grand total.
All results and formatting on the Summary sheet should match the screenshot.
6. Group all six worksheets, and then create a footer as follows:
On the left side of the footer, insert the File Name code.
On the right side of the footer, type your college Username.
Make sure these changes are applied to all sheets.
Ungroup all the sheets.
7. Group all sheets except the Summary sheet (5 sheets grouped)
Create a header as follows:
On the left side of the header, type your own Firstname and Lastname.
On the right side of the header, insert the Current Date code.
Make sure these changes are applied to all five worksheets.
Ungroup all the sheets.
8. IMPORTANT Final check: all formatting, spelling, other errors.
This process should happen every time you create a finished product.
Carefully check your work, referring frequently to the screenshots and instructions.
While no marks are given for this requirement, as much as 10% of the overall mark may be deducted.
9. Group all the sheets and select cell A1.
Ungroup the sheets and go to the Summary sheet. Ensure cell A1 is selected.
TIP: Before closing a file, it is a good habit to display your first worksheet and ensure the top of each sheet is showing.
Save and close your file.
Excel Assignment 4 - Tutorial 7 and 8
Tutorials 7 and 8 -
You work for a travel company that allows people to save money towards vacations. Each Traveler contributes regular amounts to the travel plan. Travelers also receive bonus money based on certain criteria. The workbook is designed to help keep track of Traveler savings.
1. Open the starting file Excel Assignment 4 Starting File.xlsx.
Review the document contents.
Save it as: Firstname Lastname Kcub Vacations.xlsx
The Documentation sheet contains Documentation and a Quick search area.
The Winter Vacations sheet contains a list of the travelers.
The Reference Tables sheet includes two tables: Bonus and Airport.
2. If necessary, switch to the Winter Vacations sheet.
It will be easier to work with the data if it is formatted as an Excel table.
Convert the cell range A3:N32 to an Excel Table.
Apply a table style of your choice.
Use your college username for the Table Name.
Use the AutoFit and Wrap Text tools to make the header row (row 3) match the screenshot.
3. Switch to the Documentation sheet. In cell B2 enter your firstname and lastname.
Customer Quick Lookup area: In Cell C8 create a drop-down menu that appears when users click on this cell.
The options in the drop down list are all the last names listed in the Winter Vacations sheet (username table). HINT: you do not need to type the name.
Users must select from the list of last names. If they do anything else display an appropriate Stop error message informing the user what they have done wrong.
Also create an appropriate Input message for this cell to guide the user.
4. On the Documentation sheet, in cell C9 use the appropriate function to lookup up the corresponding first name that goes with the last name chosen in the dropdown list (cell C8). Hint: select all of the columns in username table as your Table Array Continue this process for cells C10, C11, C12.
The result will be that by choosing the last name all the other information for that traveler comes up automatically.
5. At this point the Age of all travelers will be zero. Why is this happening?
Type your answer in cell C17 on the Documentation sheet.(Don't worry about the zero for now, we will be fixing this later in the assignment).
Also, what will happen if two people have the same last names? How could the designer of the spreadsheet avoided this problem? Type your answer in cell C18 on the Documentation sheet.
6. On the Documentation sheet, protect the worksheet in a way that the user can still edit cells B2 and C8.
Do not adjust the default settings.
Users should be able to modify only these two cells and only select all the other cells in the worksheet.
(Hint: Be sure to test protection)
Do not add a password when you protect the sheet.
7. Switch to the Winter Vacations sheet.
In cell A1 insert a comment that states: "This assignment created by username"
Create the defined name AustBrazilBonus for cell H1.
Create the defined name WinnipegBonus for cell J1.
8. The workbook contains a defined name that is no longer needed. Delete the defined name "Airline_Exemption" from the workbook
9. In cell F1, Use the YEAR function to extract the year from the date in cell D1. HINT: The YEAR function is very similar to the MONTH function that is covered in Tutorial 8.
10. In cell D4 create a formula that will calculate the Age of the Traveler. Fill the formula down. HINT: see page 474 or research the Datedif function.
Once you have completed this formula the Documentation sheet should now contain an age for the traveler (if you recall it was showing as zero).
11. In order to travel with the company the Traveler must be 18 years or over. Use an appropriate Excel tool that will automatically highlight anyone in the entire table who is under 18 years of age. Make sure the formatting allows easy identification of those under 18.
12. Any Travelers born before 1947 will require special authorization to be in the program.
On the Winter Vacations sheet, in the birth date column(C4:C32), create a validation rule that will only accept a birthdate after December 31, 1946.
The error message in the validation rule should clearly tell users what the problem is and to contact their supervisor to gain special permission for this individual.
Your error message must use proper grammar and spelling.
Use the Stop style of error alert.
Test the validation rule with the first record to make sure it works correctly.
13. Column H contains the Departure Airport code.
Using the reference tables sheet and the appropriate function, find the corresponding departure city that goes with the Airport code for all the records. Use structured references where possible.
14. Have a close look at all of the records in your table. Notice the #N/A error in the Departure City column.
You realize that this error is caused by one of the Airport Codes (it does not exist on the reference tables sheet). Since some users might not be familiar with Excel, they won't likely know what is causing the #N/A error.
Modify your function in the Departure City column so that when there is an error, a customized error message appears.
The error message should display: "INVALID Airport Code"
AutoFit the column to ensure the entire message displays.
15. In cell K4, use the reference tables sheet and the appropriate function to find the Bonus Amount for each of the travelers. Use structured references where possible.
Format all Bonus Amounts as Currency with no decimals and right alignment.
16. In cell L4 calculate the Winnipeg bonus. This bonus is given to only those travelers whose Departure Airport is YWG and those who have been members for at least 5 years. The bonus amount is shown J1. HINT: nested function.
You must use the defined name (for J1) in your formula.
17. In cell M4, calculate the Australia/Brazil bonus. Travelers with their favorite country as Australia or Brazil will get the bonus amount shown in H1. Hint: nested function.
Use structured references whenever possible.
Format your results with the Currency style.
18. In cell N4, calculate the total amount available (current savings plus any applicable bonuses).
'Required: You must use structured references in your function - that means that instead of using a cell reference such as D4, use the name of the column or table.
19. Your final task is to complete the Summary Statistics area (Q2:U14).
Use the appropriate functions to find the requested statistics as stated in R3 to U3.
Use structured references.
As you will notice there are some Div/0 errors. Figure out what is happening and fix those columns using the appropriate function. Hint: you have already used this function in a previous question.
Optional Challenge (not for marks): Can you change the zero in the Total amount available column to "No Travelers".
20. Finally, protect the structure of your workbook so that users cannot add, delete, rename, or move sheets. Do not add a password.
21. Select cell A1 on the Documentation sheet. Save and close your file.
Attachment:- Assignment Files.rar