Information Management Assignment 2 Assessment Weighting: 15% of the course total Total marks = 50 marks Due date: 26^th April, 2023

• group of 4 to 6 students submissions. Write each students FULLNAME, class code, studentID clearly

• Only a single student per group need to upload a clear copy (in pdf, word format) to SOUL account.

• Paper copies of your answers MUST be submitted in the assignment box.

• Dont email your assignment to your lecturer.
• Marks are deducted if your answer is unclear or missing.

Penalty details:

Lateness	Penalty
1 day	50% off
2 days	100% off

Part 1 (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. Label each formula clearly with cell reference position. SSS Motor Company has been successfully making autos and trucks for more than 100 years in the United States (U.S.), and was one of the dominant car companies in the U.S. automobile industry. Recent years, however, have been very difficult for SSS. Indeed, they have been difficult years for all automobile companies. The automobile industry is now a highly competitive business globally. Twenty years ago, SSS competed against only two other auto companies in the domestic market, but there are now a dozen large foreign auto companies to compete against. The emerging Chinese automobile companies that are subsidized by the Chinese government have very cheap labour costs. Competition from the Chinese vehicles is expected to drive down the selling price of all types of autos, and SSSs sales and profit margins would be reduced dramatically in future. SSS, currently, has very high debts as it owes US$30 billion at the end of year 2022 (cell B52), and interest expense is high. In the last two years, SSS has just about broken even net income after tax has been close to zero. SSSs management is considering dissolving the company if large profits cannot be made in the next three years (2023 to 2025) before government policy makers allow Chinese car models to be sold massively in the U.S. SSSs management reasons that SSSs better models could be sold to other car companies and the proceeds distributed to the shareholders. This would be better than a slow march to bankruptcy, resulting in shareholders getting nothing. In year 2022, SSSs management would like to forecast its financial situation such as the net income, debt owed and cash flow for the next three years (2023 2025) based on 2022s data so that SSSs management can decide what to do next. You are asked to help SSSs management and write Excel formulas in cells C19 to C52 to do these forecasts by performing a what-if analysis using Microsoft Excel. Most automobile industry executives also think there is an oversupply of autos and trucks relative to consumer demand. When supply exceeds demand, there will be downward pressure on selling price, and this forces SSSs management to introduce two kinds of incentive programs: cash-back and special low rate financing. Cash-back incentive means offering discounts on the listed price of a car; for example offering US$5000 off a US$26,000 car. Special low-rate financing means lending money to car buyers at below-bank interest rates. SSS may offer a combination of both kinds of incentive programs. Incentives are assumed a permanent feature of automobile marketing; and there are two levels of incentives (row 10):

• Stable (S) if competition is not fierce such that incentives are expected to be at normal levels.
• Up (U) if competition is intense such that aggressive incentives (that means high price discounts and/or very low interest rate financing) are expected.

Levels of incentives can vary from year to year. If incentives level is expected to be S for year 2023, S for year 2024 and U for year 2025, then the pattern SSU would be entered in cells C10 to E10. SSS has its own finance unit whose activity has been lending money to car buyers. This division borrows money at low interest rates in credit markets or from the companys bankers, and can make profits when lending this money to car buyers at higher interest rates. For example, the finance unit borrows money at 5% interest rate from its bank and then make loans to car buyers at 6% to 8% interest rate, thus SSS makes money in the form of interest earned on car loans to car buyers. However, in periods of aggressive incentives with low-rate financing being offered to car buyers, SSSs finance unit loses money because it lends money at a rate less than the rate SSS borrows at. SSSs finance unit also handles general corporate borrowings as well. For example, SSS may want to borrow US$100 million to build a new manufacturing plant, then it would negotiate with the companys bankers to borrow money, or it would go to Wall Street to sell bonds to raise money. All bond issuers are rated by independent credit analysis agencies. The ratings are intended as a measure of how likely a company is to pay off existing debts (interest and principle). A poor rating would reduce investor confidence and would mean that SSS would have to borrow money at higher interest rates. A high rating would increase investor confidence in SSS and means SSS can borrow money at lower interest rates. Thus, credit agency rating greatly affects interest rates in the bond market, and it has two values for SSS (cell B12):

• Weak (B) if competition is fierce, and SSS is in great debt such that the rating is not expected to improve in the near future.
• Junk (J) this is the lowest rating in the bond market if SSS worsens nearly to default and requires government bailout.

The credit agency rating entered in cell B12 applies to all three forecast years 2023 to 2025. Increased competition also has effect on the number of car unit sales. The unit car sales increase factor in row 11 allows a decimal percentage change in unit car sales in a year to be entered. For example, if unit car sales were expected to go up 5%, then 0.05 would be entered for that year. If sales were expected to fall 7%, then -0.07 would be entered for that year. The constants (rows 4 to 7) for the forecast are described below:

• Tax rate: The corporate tax rate is expected to be steady at 20% (row 4) for the next three years (2023 2025).
• Minimum cash needed to SSS next year: SSSs policy is to have at least US$20 million cash on hand at the end of each year, in order to SSS next years business (row 5), and thus this is the minimum cash needed at the end of each year.
• Unit manufacturing cost reduction factor (row 6): Each car requires direct costs, which include raw materials and direct labour during assembly. The average value of a car units direct costs is expected to go down 1% in 2023, go down 2% in 2024, and go down 3% in 2025 (Note that go down means 1% less than previous year).
• Fixed costs (row 7): Research-and-development costs, advertising and promotion costs and other general administrative costs are considered as fixed costs because these costs do not vary much with the number of autos sold. They are expected to be steady in the next three years at US$8 billion a year.

Calculations (rows 19 to 24) are described below:

• Direct cost to make a car unit (row 20): The average direct cost to make an automobile in a year is based on the prior years direct cost to make a car unit, and on the unit manufacturing cost reduction factor.
• Interest earned per car unit sold (row 21): Majority of car buyers will finance the purchase through SSSs finance unit. Financing is a source of income (or loss) to SSS. If the incentives are stable in a year, SSS can expect to make on average about US$150 in interest revenue on a unit sold. However, if incentives are up, only about US$20 in interest revenue is earned per unit sold, on average.
• Average selling price per unit (row 22): If the level of incentives is expected to be stable (S) in a year, then SSS can be expected to raise the average unit selling price per unit 1% over the prior years price. However, if the level of incentives is expected to be up (U) in a year, the average unit selling price per unit in a year will be 5% less than the prior years selling price.
• Average number of car units sold (row 23): The average number of car units sold in a year is based on the prior years sales units and the unit car sales increase factor (see row 11).

• Interest rate on debt (row 24): If the credit agency rating is weak (B) in the next three years, the interest rate paid on debt owed will be 5% in each of the next three years. If the rating is junk-bond (J) status, the interest rate will be 10% in each of the next three years.

Income & Cash Flow Statements (rows 26 to 41) are described below:

• Revenue from auto sales (row 30): This is based on the average number of car units sold in that year and on the average selling price per car unit in that year.
• Revenue from interest earned (row 31): This is based on the average number of car units sold in that year and on the interest earned per car unit sold in that year.
• Total revenue (row 32): This is the total revenue from auto sales and interest earned.
• Total direct costs of autos sold (row 34): This is based on the average number of car units sold in that year and on the average cost to make a car unit in that year.
• Fixed costs (row 35): Fixed costs do not vary much with the number of autos sold, and they include research-and-development costs, advertising and promotion costs and other general administrative costs.
• Total costs (row 36): This is the total of the direct costs and fixed costs in that year.
• Income before interest and tax (row 37): Before considering tax and interest expense, this is the difference between total revenue and total costs.
• Interest expense (row 38): This is a simple interest based on the years interest rate and the debt owed at the beginning of that year.
• Income before tax (row 39): Before considering tax, but after considering interest expense, this is the difference between income before interest and tax, and interest expense.
• Income tax expense (row 40): 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 41): This is the difference between income before tax and income tax expense.
• Cash at the beginning of a year (row 42): this is the cash at the end of previous year.
• Net Cash Position (NCP) (row 43): NCP at the end of a year equals the cash beginning of a year, plus the years net income, assuming that there are no receivables or payables.
• Assume that SSSs bankers will lend enough money (row 44) at the end of a year to get to SSSs minimum cash target (see row 5). If the NCP is less than the minimum cash at the end of a year, SSS 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 45).
• Cash at the end of the year equals the NCP, plus any borrowings and less any repayments (row 46).

Debt Owed (rows 48 to 52) is described below:

• The amount of US$30 billion (cell B52) is already owed to bankers and bondholders at the end of 2022.
• Debt owed at the beginning of a year equals the debt owed at the end of 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. Part 2 (10 marks) The following is a database on the art work (e.g. paintings, photos, sculptures) completed by different students in a high school. It has three tables: art work, student and student-Artwork; and their table design structure and actual data are below: Table structure and design for art work as below: Actual data for art work table as below: Table structure and design for student as below: Actual data for student table as below: Table structure and design for student-Artwork as below: Actual data for student-Artwork table as below:

studentID	artWorkID	participation	mark
10010	A0001	30	50
10010	A0002	50	60
10010	A0003	20	35
10011	A0001	20	66
10011	A0002	50	75
10011	A0004	100	28
10012	A0003	80	56
10012	A0005	10	45
10013	A0005	90	39
10013	A0006	20	25

2. Write a QBE (Query By Example) to find all those art work(s) participated by student with ID 10010 with mark = 50 or above. Display the name of art work, student surname, student first name, student mark and participation percentage for each art work.

[5 marks]

2. There is an error in the database shown above. Identify and describe this error.

[5 marks] Information Management Assignment 2 Assessment Weighting: 15% of the course total Total marks = 50 marks Due date: 26^th April, 2023

• group of 4 to 6 students submissions. Write each students FULLNAME, class code, studentID clearly

• Only a single student per group need to upload a clear copy (in pdf, word format) to SOUL account.

• Paper copies of your answers MUST be submitted in the assignment box.

• Dont email your assignment to your lecturer.
• Marks are deducted if your answer is unclear or missing.

Penalty details:

Lateness	Penalty
1 day	50% off
2 days	100% off

Part 1 (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. Label each formula clearly with cell reference position. SSS Motor Company has been successfully making autos and trucks for more than 100 years in the United States (U.S.), and was one of the dominant car companies in the U.S. automobile industry. Recent years, however, have been very difficult for SSS. Indeed, they have been difficult years for all automobile companies. The automobile industry is now a highly competitive business globally. Twenty years ago, SSS competed against only two other auto companies in the domestic market, but there are now a dozen large foreign auto companies to compete against. The emerging Chinese automobile companies that are subsidized by the Chinese government have very cheap labour costs. Competition from the Chinese vehicles is expected to drive down the selling price of all types of autos, and SSSs sales and profit margins would be reduced dramatically in future. SSS, currently, has very high debts as it owes US$30 billion at the end of year 2022 (cell B52), and interest expense is high. In the last two years, SSS has just about broken even net income after tax has been close to zero. SSSs management is considering dissolving the company if large profits cannot be made in the next three years (2023 to 2025) before government policy makers allow Chinese car models to be sold massively in the U.S. SSSs management reasons that SSSs better models could be sold to other car companies and the proceeds distributed to the shareholders. This would be better than a slow march to bankruptcy, resulting in shareholders getting nothing. In year 2022, SSSs management would like to forecast its financial situation such as the net income, debt owed and cash flow for the next three years (2023 2025) based on 2022s data so that SSSs management can decide what to do next. You are asked to help SSSs management and write Excel formulas in cells C19 to C52 to do these forecasts by performing a what-if analysis using Microsoft Excel. Most automobile industry executives also think there is an oversupply of autos and trucks relative to consumer demand. When supply exceeds demand, there will be downward pressure on selling price, and this forces SSSs management to introduce two kinds of incentive programs: cash-back and special low rate financing. Cash-back incentive means offering discounts on the listed price of a car; for example offering US$5000 off a US$26,000 car. Special low-rate financing means lending money to car buyers at below-bank interest rates. SSS may offer a combination of both kinds of incentive programs. Incentives are assumed a permanent feature of automobile marketing; and there are two levels of incentives (row 10):

• Stable (S) if competition is not fierce such that incentives are expected to be at normal levels.
• Up (U) if competition is intense such that aggressive incentives (that means high price discounts and/or very low interest rate financing) are expected.

Levels of incentives can vary from year to year. If incentives level is expected to be S for year 2023, S for year 2024 and U for year 2025, then the pattern SSU would be entered in cells C10 to E10. SSS has its own finance unit whose activity has been lending money to car buyers. This division borrows money at low interest rates in credit markets or from the companys bankers, and can make profits when lending this money to car buyers at higher interest rates. For example, the finance unit borrows money at 5% interest rate from its bank and then make loans to car buyers at 6% to 8% interest rate, thus SSS makes money in the form of interest earned on car loans to car buyers. However, in periods of aggressive incentives with low-rate financing being offered to car buyers, SSSs finance unit loses money because it lends money at a rate less than the rate SSS borrows at. SSSs finance unit also handles general corporate borrowings as well. For example, SSS may want to borrow US$100 million to build a new manufacturing plant, then it would negotiate with the companys bankers to borrow money, or it would go to Wall Street to sell bonds to raise money. All bond issuers are rated by independent credit analysis agencies. The ratings are intended as a measure of how likely a company is to pay off existing debts (interest and principle). A poor rating would reduce investor confidence and would mean that SSS would have to borrow money at higher interest rates. A high rating would increase investor confidence in SSS and means SSS can borrow money at lower interest rates. Thus, credit agency rating greatly affects interest rates in the bond market, and it has two values for SSS (cell B12):

• Weak (B) if competition is fierce, and SSS is in great debt such that the rating is not expected to improve in the near future.
• Junk (J) this is the lowest rating in the bond market if SSS worsens nearly to default and requires government bailout.

The credit agency rating entered in cell B12 applies to all three forecast years 2023 to 2025. Increased competition also has effect on the number of car unit sales. The unit car sales increase factor in row 11 allows a decimal percentage change in unit car sales in a year to be entered. For example, if unit car sales were expected to go up 5%, then 0.05 would be entered for that year. If sales were expected to fall 7%, then -0.07 would be entered for that year. The constants (rows 4 to 7) for the forecast are described below:

• Tax rate: The corporate tax rate is expected to be steady at 20% (row 4) for the next three years (2023 2025).
• Minimum cash needed to SSS next year: SSSs policy is to have at least US$20 million cash on hand at the end of each year, in order to SSS next years business (row 5), and thus this is the minimum cash needed at the end of each year.
• Unit manufacturing cost reduction factor (row 6): Each car requires direct costs, which include raw materials and direct labour during assembly. The average value of a car units direct costs is expected to go down 1% in 2023, go down 2% in 2024, and go down 3% in 2025 (Note that go down means 1% less than previous year).
• Fixed costs (row 7): Research-and-development costs, advertising and promotion costs and other general administrative costs are considered as fixed costs because these costs do not vary much with the number of autos sold. They are expected to be steady in the next three years at US$8 billion a year.

Calculations (rows 19 to 24) are described below:

• Direct cost to make a car unit (row 20): The average direct cost to make an automobile in a year is based on the prior years direct cost to make a car unit, and on the unit manufacturing cost reduction factor.
• Interest earned per car unit sold (row 21): Majority of car buyers will finance the purchase through SSSs finance unit. Financing is a source of income (or loss) to SSS. If the incentives are stable in a year, SSS can expect to make on average about US$150 in interest revenue on a unit sold. However, if incentives are up, only about US$20 in interest revenue is earned per unit sold, on average.
• Average selling price per unit (row 22): If the level of incentives is expected to be stable (S) in a year, then SSS can be expected to raise the average unit selling price per unit 1% over the prior years price. However, if the level of incentives is expected to be up (U) in a year, the average unit selling price per unit in a year will be 5% less than the prior years selling price.
• Average number of car units sold (row 23): The average number of car units sold in a year is based on the prior years sales units and the unit car sales increase factor (see row 11).

• Interest rate on debt (row 24): If the credit agency rating is weak (B) in the next three years, the interest rate paid on debt owed will be 5% in each of the next three years. If the rating is junk-bond (J) status, the interest rate will be 10% in each of the next three years.

Income & Cash Flow Statements (rows 26 to 41) are described below:

• Revenue from auto sales (row 30): This is based on the average number of car units sold in that year and on the average selling price per car unit in that year.
• Revenue from interest earned (row 31): This is based on the average number of car units sold in that year and on the interest earned per car unit sold in that year.
• Total revenue (row 32): This is the total revenue from auto sales and interest earned.
• Total direct costs of autos sold (row 34): This is based on the average number of car units sold in that year and on the average cost to make a car unit in that year.
• Fixed costs (row 35): Fixed costs do not vary much with the number of autos sold, and they include research-and-development costs, advertising and promotion costs and other general administrative costs.
• Total costs (row 36): This is the total of the direct costs and fixed costs in that year.
• Income before interest and tax (row 37): Before considering tax and interest expense, this is the difference between total revenue and total costs.
• Interest expense (row 38): This is a simple interest based on the years interest rate and the debt owed at the beginning of that year.
• Income before tax (row 39): Before considering tax, but after considering interest expense, this is the difference between income before interest and tax, and interest expense.
• Income tax expense (row 40): 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 41): This is the difference between income before tax and income tax expense.
• Cash at the beginning of a year (row 42): this is the cash at the end of previous year.
• Net Cash Position (NCP) (row 43): NCP at the end of a year equals the cash beginning of a year, plus the years net income, assuming that there are no receivables or payables.
• Assume that SSSs bankers will lend enough money (row 44) at the end of a year to get to SSSs minimum cash target (see row 5). If the NCP is less than the minimum cash at the end of a year, SSS 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 45).
• Cash at the end of the year equals the NCP, plus any borrowings and less any repayments (row 46).

Debt Owed (rows 48 to 52) is described below:

• The amount of US$30 billion (cell B52) is already owed to bankers and bondholders at the end of 2022.
• Debt owed at the beginning of a year equals the debt owed at the end of 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. Part 2 (10 marks) The following is a database on the art work (e.g. paintings, photos, sculptures) completed by different students in a high school. It has three tables: art work, student and student-Artwork; and their table design structure and actual data are below: Table structure and design for art work as below: Actual data for art work table as below: Table structure and design for student as below: Actual data for student table as below: Table structure and design for student-Artwork as below: Actual data for student-Artwork table as below:

studentID	artWorkID	participation	mark
10010	A0001	30	50
10010	A0002	50	60
10010	A0003	20	35
10011	A0001	20	66
10011	A0002	50	75
10011	A0004	100	28
10012	A0003	80	56
10012	A0005	10	45
10013	A0005	90	39
10013	A0006	20	25

2. Write a QBE (Query By Example) to find all those art work(s) participated by student with ID 10010 with mark = 50 or above. Display the name of art work, student surname, student first name, student mark and participation percentage for each art work.

  

2. There is an error in the database shown above. Identify and describe this error.