Question1 (40 marks) Refer to Table 1. Write the Excel formula for each cell marked with "" in column C such that formula could be copied and pasted into columns D and E using Microsoft Excel without further editing. There is no need to explicitly write the Excel formula for cells marked with "copy & paste". New Age Dolls makes the very successful line of Micky and Mimi dolls, which rapidly gained popularity with children and teenagers after only a few years on the market. New Age Dolls produces dolls at two locations: St. Louis (STL) and Boston (BSN), and has been leasing warehouses at both locations. Warehouses are used for storage and for distribution to retail stores. New Age management is not happy with the terms of the leases, which are too expensive. The lease expires at the end of 2021. New Age management has decided to build its own warehouses and has three possible locations: Portland(PT), Atlanta (AT) and Phoenix(PX) (which is entered in cell C22), but New Age would construct warehouses at only two of these locations. To choose these two locations, New Age would like to forecast the net income, debt owed and cash flow for the following three years (2022 to 2024) based on 2021's data. You are asked to help New Age and write Excel formulas in cells C25 to C76 for this forecast by performing a what-if analysis using Microsoft Excel. One major factor that affects the forecast is the expected state of economy over the three years (2022 to 2024) in row 21: Flat (F)- a steady economic outlook for a year Hot (H) - a heated-up economic outlook for a year The expected state of economy can vary from year to year. If the expected state of economy is H for year 2022, F for year 2023, and F for year 2024, then the pattern HFF would be entered in cells C21 to E21. Assume that there are no input errors (or typing mistakes) in row 21, or c22 cell. New Age management expects to sell all the dolls they produce in Boston and St. Louis in the three years (2022 to 2024). Production of the two types of dolls will be divided evenly between the two locations. The expected production (and sales) levels are given below: Year 2022 Year 2023 Year 2024 Micky-St. Louis 500,000 (cell C4) 750,000 (cell D4) 1,000,000 (cell E4) Mimi-St. Louis 750,000 (cell C5) 850,000 (cell D5) 1,000,000 (cell E5) Micky-Boston 500,000 (cell C6) 750,000 (cell D6) Mimi Boston 750,000 (cell C7) 850,000 (cell D7) 1,000,000 (cell E6) 1,000,000 (cell E7) To reduce distribution and storage risks (from strikes, natural disasters etc.), management plans to send half of each plant's production to each of the two selected warehouse sites. Shipping costs from each plant to each of the three warehouse sites differ. Storage costs at each of the three warehouse sites differ as well. The shipping and storage costs per doll are given below: From) Factory (To)Warehouse Year 2022 Year 2023 4.5 (cell C9) 4.95(cell D9 Year 2024 5.45(cell ES St. Louis Portland St. Louis Atlanta St. Louis Phoenix Boston Portland Atlanta 5.5 (cell C106.05(cell D106.66(cell E10 3.5 (cell C113.85(cell DIT4.24(cell EIT 6.0 (cell C126.6 (cell D12) 726cell E12 4.5 (cell C134.95fcell D135.45fcell E13 3.5 (cell C14)6.05(cell D146.66(cell E14 Boston Boston Phoenix The following constants (rows 15 to 17) for the forecast are described below: Tax rate (row 15): The corporate tax rate is expected to increase from 29% for year 2022 to 31% for year 2024. - Minimum cash needed to start next year (row 16): A New Age's policy is to have at least US$10,000 cash on hand at the end of each year, in order to start next year's business. It is assumed that New Age's banker will lend whatever is needed at the end of a year to begin the next year with US$10,000. - Administrative costs will be fixed at US$1,200,000 each year. Calculations (rows 25 to 49) are described below: - Interest rate on debt (row 25): If the expected state of economy is flat (F) in a year, the interest rate paid on debt owed will be 8%, but it will be 10% if the expected stated of economy is heating up in a year. Number of dolls shipped from a factory location to a warehouse location (rows 27 to 32): If a warehouse site is not selected, then no dolls are shipped, otherwise a factory's doll production number is divided evenly between the two selected warehouse sites. Shipping and warehousing costs for dolls shipped from a factory location to a warehouse location (rows 34 to 39): this is a function of the number of dolls shipped, and the shipping and storage costs per doll. New Age management does not consider cents as cents do not worth much and do not affect costs much. Selling prices of Micky and Mimi (rows 40 and 41): In 2021, the selling price for a Micky doll is US$11.25 (cell B40), and for a Mimi doll, it is US$12.25(cell B41), as Mimi is more popular than Micky. If the economy remains steady, then the selling prices for Micky and Mimi are not expected to change from years 2022 to 2024. However, if the economy inflates or "heats up", then the selling prices for Micky and Mimi would rise 4% from one year to the next. Sales revenue for Micky and Mimi dolls (rows 42 to 43): revenues for each type of dolls would be a function of the number of dolls sold and the selling price of each doll. New Age management does not consider cents as cents do not worth much and do not affect revenue much. Production and marketing costs for each doll made at St. Louis and Boston (rows 45 to 46): In 2021, it costs US$5.0 (cell B45) to make and market a St. Louis-made doll and USS5.50 (cell B46) to make and market a Boston-made doll. Production and marketing costs are expected to increase in the following three years whether the economy could be flat or heat up; as New Age management sees no chance of an economic downturn in the following three years. The expected year-to-year percentage increase in production and marketing costs are given below: Dells made in St. Louis 1% per year Dolls made in Boston 2% per year Flat Economy Economy Heats up 15% per year 5% per year Total production and marketing costs for all the dolls made at each factory site (rows 48 and 49): these costs are a function of the number of dolls made at that factory site and the production and marketing cost for each doll made at that factory site. New Age management does not consider cents as cents do not worth much and do not affect these costs much. Income & Cash Flow Statements (rows 53 to 70) are described below: .Beginning of year cash on hand (row 53); this is the cash at the end of the previous year. Revenue sales (row 55): this is the total revenue from sales of all the dolls made at both factory sites. Production and marketing costs (row 57): this is the total production and marketing costs for all the dolls made at both factory sites. - Shipping and warehouse costs (row 58): this is the total shipping and warehouse costs for all the dolls shipped from both factory sites to all three warehouse locations. Fixed administrative costs (row 59): this is the constant administrative costs for each year. Total costs (row 60): these costs include production, marketing. shipping, warehousing and fixed administrative costs for all the dolls made and sold during that year. - Pre-interest expense margin (row 61): Before considering tax and interest expense, this is the difference between total revenue and total costs. Interest expense (row 62): this is a simple interest based on the year's interest rate and the debt owed at the beginning of that year. . Income before tax (row 63): Before considering tax, but after Interest expense (row 62): this is a simple interest based on the year's interest rate and the debt owed at the beginning of that year. . Income before tax (row 63): Before considering tax, but after considering interest expense, this is the difference between pre- interest expense margin, and interest expense. Income tax expense (row 64): This is zero if income before tax is zero or less; otherwise, apply the tax rate for the year to the income before tax. Net income after tax (row 65): This is the difference between income before tax and income tax expense. .Net Cash Position (NCP) (row 67): NCP at the end of a year equals the cash beginning of a year, plus the year's net income, assuming that there are no receivables or payables. Assume that New Age's bankers will lend enough money (row 68) at the end of a year to get to New Age's minimum cash target (see row 16). If the NCP is less than the minimum cash at the end of a year, New Age must borrow enough to reach the minimum cash target. Borrowings increase cash on hand, of course. . If the NCP is more than the minimum cash and there is outstanding debt from previous year(s), then some or all of the debt should be repaid, but not to take your company below the minimum cash level (row 69). Cash at the end of the year equals the NCP, plus any borrowings and less any repayments (row 70). Debt Owed (rows 73 to 76) is described below: The amount of US$3 million (cell B76) is already owed to bankers and bondholders at the end of 2021. Debt owed at the beginning of a year equals the debt owed at the end of the previous year. Amounts borrowed and repaid that have been calculated before can be echoed to this section. The amount owed at the end of a year equals to the debt owed at the beginning of the year plus any borrowings, and less any repayments. Table 1: 'NA' = Not Applicable, meaning no entry is required in the cell. B 3 CONSTANTS 2021 2022 2023 2024 4 Micky PRODUCTION - STL 500000 750000 1000000 5 Mimi PRODUCTION - STL NA 750000 850000 1000000 6 Micky PRODUCTION - BSN NA 500000 750000 1000000 7 Mimi PRODUCTION - BSN NA 750000 850000 1000000 PER UNIT SHIPPING AND 8 WAREHOUSING COSTS (FROM-TO) 9 STL --> PT NA 45 4.95 5.45 10 STL --> AT NA 5.5 6.05 6.66 11 STL --> PX NA 3.5 3.85 4.24 12 BSN PT NA 6 6.6 7.26 13 BSN -> AT NA 4.5 4.95 5.45 14 BSN --> PX 5.5 6.05 6.66 15 TAX RATE EXPECTED NA 0.29 0.3 0.31 NA 10000 10000 16 MIN CASH RORD AT END OF YR 17 FIXED ADMINISTRATIVE COSTS 20 INPUTS NA 1200000 1200000 10000 1200000 2024 2022 2023 EXPECTED STATE OF ECONOMY: 21 F = FLAT, H = HOT H F WAREHOUSE LOCATION NOT PT NA USED: PT = PORTLAND; 22 AT = ATLANTA; PX = PHOENIX 24 CALCULATIONS 2023 copy & paste 25 INTEREST RATE FOR YEAR 26 NUMBER OF DOLLS SHIPPED FROM STL TO PT copy & paste FROM STL TO AT 29 FROM STL TO PX 30 FROM BSN TO PT 31 FROM BSN TO AT copy & paste copy & paste copy & paste copy & paste 32 FROM BSN TO PX copy & paste 33 SHIPPING & WAREHOUSING COSTS 34 FROM STL TO PT copy & paste 35 FROM STL TO AT 36 FROM STL TO PX copy & paste copy & paste copy & paste 37 FROM BSN TO PT 38 FROM BSN TO AT copy & paste 39 FROM BSN TO PX copy & paste copy & paste 40 UNIT SELLING PRICE -Micky 41 UNIT SELLING PRICE - Mimi 42 SALES REVENUE-Micky copy & paste copy & paste 43 SALES REVENUE - Mimi copy & paste 44 UNIT PRODUCTION & MARKETING COSTS. 45 ST LOUIS (BOTH DOLLS) 5 copy & paste 46 BSN (BOTH DOLLS) 5.5 copy & paste 47 TOTAL PRODUCTION & MARKETING COST! 48 ST LOUIS (BOTH DOLLS) copy & paste 49 BOSTON (BOTH DOLLS) copy & paste 278885 2021 NA NA C NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1 mark] NA ?[1 mark] NA ?[1 mark] NA ?[1 mark] NA ? [1 mark] NA ? [1 mark] 11.25 ?[1.5 marks] 12 25 ?[1.5 marks] NA ?[1 mark] NA ?[1 mark] ? [1.5 marks] ?[1.5 marks] ?[1 mark] ?[1 mark] B 2021 2022 NA NA IL NA E 2024 copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste B C D INCOME STATEMENT AND 52 CASH FLOW STATEMENT 2024 2022 ? [0.25 mark] 2023 copy & paste 53 BEGINNING OF YEAR CASH ON HAND copy & paste 55 REVENUE (SALES) ? [0.5 mark] copy & paste copy & paste 56 COSTS AND EXPENSES 57 PRODUCTION AND MARKETING COSTS copy & paste copy & paste 58 SHIPPING AND WAREHOUSE COSTS copy & paste copy & paste 59 FIXED ADMIN COSTS copy & paste copy & paste 60 TOTAL COSTS copy & paste copy & paste 61 PRE-INTEREST EXPENSE MARGIN ? 10.5 mark] ? 10.5 mark] ? [0.25 mark] ? [0.5 mark] ? [0.5 mark] ? [1 mark] ? [0 25 mark] ? [1 mark] ? [0.5 mark] copy & paste copy & paste 62 INTEREST EXPENSE copy & paste copy & paste copy & paste 63 INCOME BEFORE TAX copy & paste 64 TAX EXPENSE copy & paste copy & paste copy & paste copy & paste 65 NET INCOME NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENTS OF 67 DEBT (BEG CASH PLUS NET INCOME) 68 ADD: BORROWINGS FROM BANK copy & paste copy & paste copy & paste copy & paste ? [0.5 mark] 2 [1.5 marks] ? [4 marks] ? [0.5 mark] 69 LESS: REPAYMENTS TO BANK copy & paste copy & paste 70 EQUALS: END OF YEAR CASH ON HAND copy & paste copy & paste 72 DEBT OWED 73 OWED TO BANK AT BEGINNING OF YEAR NA copy & paste copy & paste 74 ADD: BORROWINGS FROM BANK NA ? [0.25 mark] ? 10.25 mark] ? 10.25 mark] copy & paste copy & paste 75 LESS: REPAYMENTS TO BANK copy & paste copy & paste 76 EQUALS: OWED TO BANK AT END OF YEAR 3000000? [0.5 mark] copy & paste copy & paste Question 2 Below is a database with 2 tables - Marina, Technician: Marina table: primary key = Marina Number, foreign key = Tech Number, Technician table: primary key = Tech Number, no foreign key Relationships Marina Technician Marina Number Tech Number Marina Name Last Name Address First Name City Address State City Zip Code State Warranty Non-warranty Tech Number Zip Code Hourly Rate YTD Earnings The above is a relational schema or entity relationship diagram for 2 tables: Marina and Technician. Explain why the above relational schema does NOT indicate the type of relationship (one-to-one, one-to-many, many-to-many) between these 2 tables, and describe potential problems that may occur. (3 marks) 2021 NA NA NA NA NA 555555555 NA NA NA NA NA NA NA NA NA E 10000 Question 3: A High school recently held an art exhibition showing all the art works done by the school's students, and would like to set up a database recording which student created which piece of art work etc. Each student has a unique student ID and each piece of art work has a unique artworkID, and the following attributes are found for student and artwork: STUDENT: studentID, surname, first name, birthdate, class-code, sex ARTWORK: artworkID, completion date, height, width, depth, art type An art teacher has a list of art works that are completed by each student, see below for some samples: studentID artworkID 20140000 X123, P234, C883, D678 20171111 X234, P334, D222, C883 20192222 X243, P123, C883, D888 20153333 X111, D123 20164444 X222, P234 **** You are responsible to help design this database for the school. a) What relationship (one-to-one, one-to-many, many-to-many) exists between STUDENT and ARTWORK? Explain your answer or draw an entity relationship diagram. (2 marks) b) How many tables would be needed to implement the relationship as indicated in (a)? List out the attributes in each table, indicating which field(s) form the primary key, and which field is the foreign key (if foreign key exists). (5 marks) Question 4: Given the following transaction database of a supermarket: (TID = transaction ID) Transaction ID Items bought 001 {A, B, C) 002 {A, D, C) 003 004 {A, B, C) 005 (A, D, B, C) 006 {A, D, F} 007 {A, F, B, C) 008 {F, B} 009 {D, F, C 010 {D, B, C) a) Assuming minimum support-0.6, use the Apriori algorithm to generate all the association rules for the frequent 2-itemsets. Calculate the confidence for these rules (Round to 3 decimal places). (4.5 marks) b) For the rules obtained in (a), calculate the lift for the rules with the highest confidence. (0.5 mark) A. D. F. C 24 25 1261 (27 28 29 30 31 32 33 34 35 36 37 38 39 40 141 42 43 45 46 47 148) 49 50 151 52 53 54 55 156 57 58 59 160 61 62 63 164 65 66 67 68 69 70 71 72 Question 1 (40 marks) 2022 2022 Q1 Total: 1.5 marks 5 marks 1.5 marks 1.5 marks 1.5 marks 1.5 marks 1.5 marks mark I mark I mark mark I mark mark 15 marks 5 marks mark 1 mark 1.5 marks 1.5 marks mark I mark 0.25 mark 0.5 mark 0.5 mark 0.5 mark 0.25 mark 0.5 mark 0.5 mark mark 0.25 mark I mark 0.5 mark 0.5 mark 1.5 marks 4 marks 0.5 mark 0.25 mark 0.25 mark 0.25 mark 0.5 mark Question 2 (3 marks) Question 3 (7 marks) a) (2 marks) b) (5 marks) Q2 Total: Q3 Total: Question 4 (5 marks) a) (4.5 marks) Note: You may add or delete row(s) in the tables below if necessary. Frequent Itemsets Support 1) 6) Note: You may add or delete row(s) in the tables below if necessary. e.g. Confidence (X Y) Support(X,Y/Support(X) -0.3/0.7 - b) (0.5 mark) e.g.Lift (X Y) Support (XY)/(Support(X)* Support(Y) = 0.3/(0.7*0.5) -0.86 1) = 2 End of Answer Book Q4 Total: Question1 (40 marks) Refer to Table 1. Write the Excel formula for each cell marked with "" in column C such that formula could be copied and pasted into columns D and E using Microsoft Excel without further editing. There is no need to explicitly write the Excel formula for cells marked with "copy & paste". New Age Dolls makes the very successful line of Micky and Mimi dolls, which rapidly gained popularity with children and teenagers after only a few years on the market. New Age Dolls produces dolls at two locations: St. Louis (STL) and Boston (BSN), and has been leasing warehouses at both locations. Warehouses are used for storage and for distribution to retail stores. New Age management is not happy with the terms of the leases, which are too expensive. The lease expires at the end of 2021. New Age management has decided to build its own warehouses and has three possible locations: Portland(PT), Atlanta (AT) and Phoenix(PX) (which is entered in cell C22), but New Age would construct warehouses at only two of these locations. To choose these two locations, New Age would like to forecast the net income, debt owed and cash flow for the following three years (2022 to 2024) based on 2021's data. You are asked to help New Age and write Excel formulas in cells C25 to C76 for this forecast by performing a what-if analysis using Microsoft Excel. One major factor that affects the forecast is the expected state of economy over the three years (2022 to 2024) in row 21: Flat (F)- a steady economic outlook for a year Hot (H) - a heated-up economic outlook for a year The expected state of economy can vary from year to year. If the expected state of economy is H for year 2022, F for year 2023, and F for year 2024, then the pattern HFF would be entered in cells C21 to E21. Assume that there are no input errors (or typing mistakes) in row 21, or c22 cell. New Age management expects to sell all the dolls they produce in Boston and St. Louis in the three years (2022 to 2024). Production of the two types of dolls will be divided evenly between the two locations. The expected production (and sales) levels are given below: Year 2022 Year 2023 Year 2024 Micky-St. Louis 500,000 (cell C4) 750,000 (cell D4) 1,000,000 (cell E4) Mimi-St. Louis 750,000 (cell C5) 850,000 (cell D5) 1,000,000 (cell E5) Micky-Boston 500,000 (cell C6) 750,000 (cell D6) Mimi Boston 750,000 (cell C7) 850,000 (cell D7) 1,000,000 (cell E6) 1,000,000 (cell E7) To reduce distribution and storage risks (from strikes, natural disasters etc.), management plans to send half of each plant's production to each of the two selected warehouse sites. Shipping costs from each plant to each of the three warehouse sites differ. Storage costs at each of the three warehouse sites differ as well. The shipping and storage costs per doll are given below: From) Factory (To)Warehouse Year 2022 Year 2023 4.5 (cell C9) 4.95(cell D9 Year 2024 5.45(cell ES St. Louis Portland St. Louis Atlanta St. Louis Phoenix Boston Portland Atlanta 5.5 (cell C106.05(cell D106.66(cell E10 3.5 (cell C113.85(cell DIT4.24(cell EIT 6.0 (cell C126.6 (cell D12) 726cell E12 4.5 (cell C134.95fcell D135.45fcell E13 3.5 (cell C14)6.05(cell D146.66(cell E14 Boston Boston Phoenix The following constants (rows 15 to 17) for the forecast are described below: Tax rate (row 15): The corporate tax rate is expected to increase from 29% for year 2022 to 31% for year 2024. - Minimum cash needed to start next year (row 16): A New Age's policy is to have at least US$10,000 cash on hand at the end of each year, in order to start next year's business. It is assumed that New Age's banker will lend whatever is needed at the end of a year to begin the next year with US$10,000. - Administrative costs will be fixed at US$1,200,000 each year. Calculations (rows 25 to 49) are described below: - Interest rate on debt (row 25): If the expected state of economy is flat (F) in a year, the interest rate paid on debt owed will be 8%, but it will be 10% if the expected stated of economy is heating up in a year. Number of dolls shipped from a factory location to a warehouse location (rows 27 to 32): If a warehouse site is not selected, then no dolls are shipped, otherwise a factory's doll production number is divided evenly between the two selected warehouse sites. Shipping and warehousing costs for dolls shipped from a factory location to a warehouse location (rows 34 to 39): this is a function of the number of dolls shipped, and the shipping and storage costs per doll. New Age management does not consider cents as cents do not worth much and do not affect costs much. Selling prices of Micky and Mimi (rows 40 and 41): In 2021, the selling price for a Micky doll is US$11.25 (cell B40), and for a Mimi doll, it is US$12.25(cell B41), as Mimi is more popular than Micky. If the economy remains steady, then the selling prices for Micky and Mimi are not expected to change from years 2022 to 2024. However, if the economy inflates or "heats up", then the selling prices for Micky and Mimi would rise 4% from one year to the next. Sales revenue for Micky and Mimi dolls (rows 42 to 43): revenues for each type of dolls would be a function of the number of dolls sold and the selling price of each doll. New Age management does not consider cents as cents do not worth much and do not affect revenue much. Production and marketing costs for each doll made at St. Louis and Boston (rows 45 to 46): In 2021, it costs US$5.0 (cell B45) to make and market a St. Louis-made doll and USS5.50 (cell B46) to make and market a Boston-made doll. Production and marketing costs are expected to increase in the following three years whether the economy could be flat or heat up; as New Age management sees no chance of an economic downturn in the following three years. The expected year-to-year percentage increase in production and marketing costs are given below: Dells made in St. Louis 1% per year Dolls made in Boston 2% per year Flat Economy Economy Heats up 15% per year 5% per year Total production and marketing costs for all the dolls made at each factory site (rows 48 and 49): these costs are a function of the number of dolls made at that factory site and the production and marketing cost for each doll made at that factory site. New Age management does not consider cents as cents do not worth much and do not affect these costs much. Income & Cash Flow Statements (rows 53 to 70) are described below: .Beginning of year cash on hand (row 53); this is the cash at the end of the previous year. Revenue sales (row 55): this is the total revenue from sales of all the dolls made at both factory sites. Production and marketing costs (row 57): this is the total production and marketing costs for all the dolls made at both factory sites. - Shipping and warehouse costs (row 58): this is the total shipping and warehouse costs for all the dolls shipped from both factory sites to all three warehouse locations. Fixed administrative costs (row 59): this is the constant administrative costs for each year. Total costs (row 60): these costs include production, marketing. shipping, warehousing and fixed administrative costs for all the dolls made and sold during that year. - Pre-interest expense margin (row 61): Before considering tax and interest expense, this is the difference between total revenue and total costs. Interest expense (row 62): this is a simple interest based on the year's interest rate and the debt owed at the beginning of that year. . Income before tax (row 63): Before considering tax, but after Interest expense (row 62): this is a simple interest based on the year's interest rate and the debt owed at the beginning of that year. . Income before tax (row 63): Before considering tax, but after considering interest expense, this is the difference between pre- interest expense margin, and interest expense. Income tax expense (row 64): This is zero if income before tax is zero or less; otherwise, apply the tax rate for the year to the income before tax. Net income after tax (row 65): This is the difference between income before tax and income tax expense. .Net Cash Position (NCP) (row 67): NCP at the end of a year equals the cash beginning of a year, plus the year's net income, assuming that there are no receivables or payables. Assume that New Age's bankers will lend enough money (row 68) at the end of a year to get to New Age's minimum cash target (see row 16). If the NCP is less than the minimum cash at the end of a year, New Age must borrow enough to reach the minimum cash target. Borrowings increase cash on hand, of course. . If the NCP is more than the minimum cash and there is outstanding debt from previous year(s), then some or all of the debt should be repaid, but not to take your company below the minimum cash level (row 69). Cash at the end of the year equals the NCP, plus any borrowings and less any repayments (row 70). Debt Owed (rows 73 to 76) is described below: The amount of US$3 million (cell B76) is already owed to bankers and bondholders at the end of 2021. Debt owed at the beginning of a year equals the debt owed at the end of the previous year. Amounts borrowed and repaid that have been calculated before can be echoed to this section. The amount owed at the end of a year equals to the debt owed at the beginning of the year plus any borrowings, and less any repayments. Table 1: 'NA' = Not Applicable, meaning no entry is required in the cell. B 3 CONSTANTS 2021 2022 2023 2024 4 Micky PRODUCTION - STL 500000 750000 1000000 5 Mimi PRODUCTION - STL NA 750000 850000 1000000 6 Micky PRODUCTION - BSN NA 500000 750000 1000000 7 Mimi PRODUCTION - BSN NA 750000 850000 1000000 PER UNIT SHIPPING AND 8 WAREHOUSING COSTS (FROM-TO) 9 STL --> PT NA 45 4.95 5.45 10 STL --> AT NA 5.5 6.05 6.66 11 STL --> PX NA 3.5 3.85 4.24 12 BSN PT NA 6 6.6 7.26 13 BSN -> AT NA 4.5 4.95 5.45 14 BSN --> PX 5.5 6.05 6.66 15 TAX RATE EXPECTED NA 0.29 0.3 0.31 NA 10000 10000 16 MIN CASH RORD AT END OF YR 17 FIXED ADMINISTRATIVE COSTS 20 INPUTS NA 1200000 1200000 10000 1200000 2024 2022 2023 EXPECTED STATE OF ECONOMY: 21 F = FLAT, H = HOT H F WAREHOUSE LOCATION NOT PT NA USED: PT = PORTLAND; 22 AT = ATLANTA; PX = PHOENIX 24 CALCULATIONS 2023 copy & paste 25 INTEREST RATE FOR YEAR 26 NUMBER OF DOLLS SHIPPED FROM STL TO PT copy & paste FROM STL TO AT 29 FROM STL TO PX 30 FROM BSN TO PT 31 FROM BSN TO AT copy & paste copy & paste copy & paste copy & paste 32 FROM BSN TO PX copy & paste 33 SHIPPING & WAREHOUSING COSTS 34 FROM STL TO PT copy & paste 35 FROM STL TO AT 36 FROM STL TO PX copy & paste copy & paste copy & paste 37 FROM BSN TO PT 38 FROM BSN TO AT copy & paste 39 FROM BSN TO PX copy & paste copy & paste 40 UNIT SELLING PRICE -Micky 41 UNIT SELLING PRICE - Mimi 42 SALES REVENUE-Micky copy & paste copy & paste 43 SALES REVENUE - Mimi copy & paste 44 UNIT PRODUCTION & MARKETING COSTS. 45 ST LOUIS (BOTH DOLLS) 5 copy & paste 46 BSN (BOTH DOLLS) 5.5 copy & paste 47 TOTAL PRODUCTION & MARKETING COST! 48 ST LOUIS (BOTH DOLLS) copy & paste 49 BOSTON (BOTH DOLLS) copy & paste 278885 2021 NA NA C NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1.5 marks] NA ?[1 mark] NA ?[1 mark] NA ?[1 mark] NA ?[1 mark] NA ? [1 mark] NA ? [1 mark] 11.25 ?[1.5 marks] 12 25 ?[1.5 marks] NA ?[1 mark] NA ?[1 mark] ? [1.5 marks] ?[1.5 marks] ?[1 mark] ?[1 mark] B 2021 2022 NA NA IL NA E 2024 copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste B C D INCOME STATEMENT AND 52 CASH FLOW STATEMENT 2024 2022 ? [0.25 mark] 2023 copy & paste 53 BEGINNING OF YEAR CASH ON HAND copy & paste 55 REVENUE (SALES) ? [0.5 mark] copy & paste copy & paste 56 COSTS AND EXPENSES 57 PRODUCTION AND MARKETING COSTS copy & paste copy & paste 58 SHIPPING AND WAREHOUSE COSTS copy & paste copy & paste 59 FIXED ADMIN COSTS copy & paste copy & paste 60 TOTAL COSTS copy & paste copy & paste 61 PRE-INTEREST EXPENSE MARGIN ? 10.5 mark] ? 10.5 mark] ? [0.25 mark] ? [0.5 mark] ? [0.5 mark] ? [1 mark] ? [0 25 mark] ? [1 mark] ? [0.5 mark] copy & paste copy & paste 62 INTEREST EXPENSE copy & paste copy & paste copy & paste 63 INCOME BEFORE TAX copy & paste 64 TAX EXPENSE copy & paste copy & paste copy & paste copy & paste 65 NET INCOME NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENTS OF 67 DEBT (BEG CASH PLUS NET INCOME) 68 ADD: BORROWINGS FROM BANK copy & paste copy & paste copy & paste copy & paste ? [0.5 mark] 2 [1.5 marks] ? [4 marks] ? [0.5 mark] 69 LESS: REPAYMENTS TO BANK copy & paste copy & paste 70 EQUALS: END OF YEAR CASH ON HAND copy & paste copy & paste 72 DEBT OWED 73 OWED TO BANK AT BEGINNING OF YEAR NA copy & paste copy & paste 74 ADD: BORROWINGS FROM BANK NA ? [0.25 mark] ? 10.25 mark] ? 10.25 mark] copy & paste copy & paste 75 LESS: REPAYMENTS TO BANK copy & paste copy & paste 76 EQUALS: OWED TO BANK AT END OF YEAR 3000000? [0.5 mark] copy & paste copy & paste Question 2 Below is a database with 2 tables - Marina, Technician: Marina table: primary key = Marina Number, foreign key = Tech Number, Technician table: primary key = Tech Number, no foreign key Relationships Marina Technician Marina Number Tech Number Marina Name Last Name Address First Name City Address State City Zip Code State Warranty Non-warranty Tech Number Zip Code Hourly Rate YTD Earnings The above is a relational schema or entity relationship diagram for 2 tables: Marina and Technician. Explain why the above relational schema does NOT indicate the type of relationship (one-to-one, one-to-many, many-to-many) between these 2 tables, and describe potential problems that may occur. (3 marks) 2021 NA NA NA NA NA 555555555 NA NA NA NA NA NA NA NA NA E 10000 Question 3: A High school recently held an art exhibition showing all the art works done by the school's students, and would like to set up a database recording which student created which piece of art work etc. Each student has a unique student ID and each piece of art work has a unique artworkID, and the following attributes are found for student and artwork: STUDENT: studentID, surname, first name, birthdate, class-code, sex ARTWORK: artworkID, completion date, height, width, depth, art type An art teacher has a list of art works that are completed by each student, see below for some samples: studentID artworkID 20140000 X123, P234, C883, D678 20171111 X234, P334, D222, C883 20192222 X243, P123, C883, D888 20153333 X111, D123 20164444 X222, P234 **** You are responsible to help design this database for the school. a) What relationship (one-to-one, one-to-many, many-to-many) exists between STUDENT and ARTWORK? Explain your answer or draw an entity relationship diagram. (2 marks) b) How many tables would be needed to implement the relationship as indicated in (a)? List out the attributes in each table, indicating which field(s) form the primary key, and which field is the foreign key (if foreign key exists). (5 marks) Question 4: Given the following transaction database of a supermarket: (TID = transaction ID) Transaction ID Items bought 001 {A, B, C) 002 {A, D, C) 003 004 {A, B, C) 005 (A, D, B, C) 006 {A, D, F} 007 {A, F, B, C) 008 {F, B} 009 {D, F, C 010 {D, B, C) a) Assuming minimum support-0.6, use the Apriori algorithm to generate all the association rules for the frequent 2-itemsets. Calculate the confidence for these rules (Round to 3 decimal places). (4.5 marks) b) For the rules obtained in (a), calculate the lift for the rules with the highest confidence. (0.5 mark) A. D. F. C 24 25 1261 (27 28 29 30 31 32 33 34 35 36 37 38 39 40 141 42 43 45 46 47 148) 49 50 151 52 53 54 55 156 57 58 59 160 61 62 63 164 65 66 67 68 69 70 71 72 Question 1 (40 marks) 2022 2022 Q1 Total: 1.5 marks 5 marks 1.5 marks 1.5 marks 1.5 marks 1.5 marks 1.5 marks mark I mark I mark mark I mark mark 15 marks 5 marks mark 1 mark 1.5 marks 1.5 marks mark I mark 0.25 mark 0.5 mark 0.5 mark 0.5 mark 0.25 mark 0.5 mark 0.5 mark mark 0.25 mark I mark 0.5 mark 0.5 mark 1.5 marks 4 marks 0.5 mark 0.25 mark 0.25 mark 0.25 mark 0.5 mark Question 2 (3 marks) Question 3 (7 marks) a) (2 marks) b) (5 marks) Q2 Total: Q3 Total: Question 4 (5 marks) a) (4.5 marks) Note: You may add or delete row(s) in the tables below if necessary. Frequent Itemsets Support 1) 6) Note: You may add or delete row(s) in the tables below if necessary. e.g. Confidence (X Y) Support(X,Y/Support(X) -0.3/0.7 - b) (0.5 mark) e.g.Lift (X Y) Support (XY)/(Support(X)* Support(Y) = 0.3/(0.7*0.5) -0.86 1) = 2 End of Answer Book Q4 Total
