Excel Case 1 Case 1 instructions Complete Comprehensive Case-SportZ Lt.-on page 330 of your math textbook. You may work alone or in a group of two. Value 10% Criteria: complete in Excel use formulas and cell references answer all questions label everything so it can be understood by anyone looking at it create new sheets for each question (i.e. 1, 2, 3 etc.) and name the sheet correctly if data requires a table, create one and label correctly present data clearly with correct number formatting, shadings, and borders where appropriate Submission status Submission No attempt a. SportZ sponsors an evening session of skating at the local arena. A family pass is offered, whereby a parent and up to two children are admitted for $12. Free hot chocolate is provided to those with the family pass. The rental of the arena for each of these sessions costs $1000. Hot chocolate costs $0.50 per person. Estimated capacity for the skating rink is 500 people. i. If only family passes are sold, and each includes a parent and two children, how many family passes must be sold to break even? ii. At break-even, what would be the total revenue? iii. At break-even, what percent of capacity would result? b. Along with the development of the new helmet for ice hockey, Sportz is also making changes to the street hockey helmets it offers. For one of the most popular models, the cost of the moulds is $49 920 and the selling price would be $39.99. On the basis of sales forecasts, the company estimates that it needs to sell 1600 units to break even. i. What is the maximum variable cost that can be spent to make each helmet, and still break even? ii. If the variable cost per unit changes, and the contribution rate is 85%, how many units need to be sold to break even? c. To round out its inventory, SportZ outlet store buys and resells sports tape. For a package Part 2 Comprehensive Case SportZ Ltd. units need to be sold to break even? c. To round out its inventory, SportZ outlet store buys and resells sports tape. For a package of eight rolls of standard tape, the store has been paying $3.48 to its supplier. The regular selling price for this package is $7.99. Advertising and promotional material for the product costs $2340. i. How many packages need to be sold to break even? ii. The supplier is offering a special promotional discount of 20% if an order of at least 1000 units is placed for each of the three winter months. If SportZ outlet store were to place this order, what is the minimum sale price it could offer and still break even? iii. What would be the maximum percent discount that could be offered in a sale flyer that would still allow SportZ to break-even? iv. If the packages of tape were ordered with the special promotional discount, a "25% off" sale was offered, and the store sold 950 packages of tape, how much profit or loss would SportZ outlet store make? C Part 2 Comprehensive Case SportZ Ltd. 2. Chapter 6- Questions a. SportZ. Ltd. has offered a series of discounts to their retail outlets. The basic wholesale discount is 30% off the suggested retail price. If the retailer purchases more than $5000 from Sportz, a further 20% discount is applied. As an introductory offer on the new mask, if two masks are ordered, a further 5% discount is given for the total order. Lethbridge Sports has placed an order for $9800, at the suggested retail price. How much will Lethbridge Sports have to pay for the order? b. Sportz has received an invoice from one of its suppliers, dated April 2, for $30 120, terms 2/10. All or part of the amount owing will be paid on April 10. i. How much would have to be paid to fully pay the invoice? ii. How much would have to be paid to reduce the debt by $20 000? c. A specific type of leather is needed to make gloves for a hockey player. The leather for each glove costs $12.00. To cut and style the leather, expenses of 45% of cost are incurred. The company targets a profit of 60% of cost. i. What is the targeted selling price for the leather glove? ii. What is the resulting markup based on cost? iii. If the profit was reduced to 50% of cost, how much would be the sale price? ii. What is the resulting markup based on cost? iii. If the profit was reduced to 50% of cost, how much would be the sale price? d. At their outlet store, SportZ has a supply of soccer balls. To make room for other equipment, the balls are being marked down to sell quickly. The retail price is $22.99, and the store has advertised a sale of 30% off. i. What is the sale price for the balls? ii. Due to a special promotion given to a school nearby, an additional $5.00 discount is given for each ball purchased. What is the resulting markdown rate? 3. Chapter 7-Questions SportZ Ltd. purchases materials and services from various vendors. Money has been borrowed from the Bank of Alberta. Payments to the vendors and the bank are being planned. a. Sportz has invoices for materials purchased from Platinum Steel Inc. The invoices are for $4242 due 60 days ago, $12 567 due in 30 days, and $18 451 due in 140 days. If SportZ pays all of these invoices today, how much cash is needed? Money is worth 6% per annum. 3. Chapter 7-Questions SportZ Ltd. purchases materials and services from various vendors. Money has been borrowed from the Bank of Alberta. Payments to the vendors and the bank are being planned. a. Sportz has invoices for materials purchased from Platinum Steel Inc. The invoices are for $4242 due 60 days ago, $12 567 due in 30 days, and $18 451 due in 140 days. If SportZ pays all of these invoices today, how much cash is needed? Money is worth 6% per annum. b. Bank of Alberta loan payments of $11 000 due 35 days ago and $16 000 due in 68 days are to be replaced by a payment of $6000 today and a further payment in 90 days. If interest on these loans is 7.2% per annum, what is the size of the final payment? c. Prairie Plastics Ltd. is owed payments of $18 000 due in 60 days and $16 000 due in 120 days. Instead, Sportz has negotiated a new payment agreement in which three equal payments are to be made in 75 days, 100 days, and 200 days. If interest of 9% is charged, what is the size of the equal payments? d. Creative Inc., a design company, has sent SportZ an invoice for services provided in the amount of $15 000, due today. Alternatively, they will accept payment of $15 150 in 50 days. What interest rate is being charged? 329 RK 4. Chapter 8-Questions a. A 5% promissory note for $10 000 was issued 183 days ago. How much cash is needed today to pay the note? b. Sportz has secured a line of credit with the Bank of Alberta. The following transactions were made for the last month. The account receives daily interest of 1.5% p.a. on positive balances and pays daily interest of 8% p.a. on negative balances. The limit on the line of credit is $10 000. Overdraft interest of 19% p.a. is charged on the daily amount exceeding the line of credit limit. For each transaction causing an overdraft or adding to an overdraft, a service charge of $10 is applied. Calculate the interest, overdraft interest, service charges, and the month-end balance for the line of credit. Date Transaction Description March 1 Balance 5 Cheque 437 329 Deposit Withdrawal Balance -4000.00 2185.00 -6185.00 Part 2 Comprehensive Case SportZ Ltd. 7 Cheque 438 12 Deposit 15 15 Cheque 439 221 23 23 22 27 311 Cheque 440 Deposit Deposit Interest charged Overdraft interest Service charge 6000.00 2000.00 5000.00 -6606.00 421.00 -606.00 -5734.00 5128.00 -11867.00 6133.00 ? ? 2 -9867.00 -4867.00 Overdraft interest ? Service charge c. SportZ has negotiated a loan of $25 000 with interest at 7.6% per annum, to be paid as month-end payments of $2200.00 over the next year. Construct a loan amortization schedule to answer the following questions. i. How much interest is paid over the first two months? ii. How much of the principal is paid by the end of the first two months? iii. How much interest is paid over the term of the loan? iv. What is the amount of the final payment? 15 ty 329 A