Your bicycle business Bike City, seems to be doing well.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

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

-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.

-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).

-In the centre footer of the worksheet, insert Firstname Lastname (your first and last names)

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.)

-In row 1, remove and reapply Merge and Center to include the entire range A1:I1.

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.

 

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.

-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.

-Fill the Sum function in cell F10 through the range F10:I10.

-Ensure all columns are wide enough to properly display data.

-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.

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

-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!

 

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)

-Select cell A1.

-Ensure your worksheet is in Normal view.

-Set zoom to 100%.

-Save and close your file.

 

 

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme