Read and complete the case below. You should complete all questions using one Excel worksheet in the Excel document provided on D2L. Complete the 2 tables provided on the Excel worksheet to answer questions #1 and #2. Use the provided three text boxes on the Excel worksheet to answer questions #3-#5. All your analyses will be done using operating income (not net income after taxes) so you can ignore income taxes. Assume you work as a staff member in a large accounting department for a multinational public company. Your job requires you to review documents relating to the company's equipment purchases. Upon verifying that purchases are properly approved, you prepare journal entries to record the equipment purchases in the accounting system. Typically, you handle equipment purchases costing $50,000 or less. This morning, you were contacted by the executive assistant to the chief financial officer (CFO). She says that the CFO has asked to see you immediately in his office. Although your boss's boss has attended a few meetings where the CFO was present, you have never met the CFO during your three years with the company. You are anxious about the meeting. Upon entering the CFO's office, you are warmly greeted with a smile and friendly handshake. The CFO compliments you on the great work that you have been doing for the company. You soon feel a little more comfortable, particularly when the CFO mentions that he has a special project for you. He states that he and the CEO have negotiated significant new arrangements with the company's equipment suppliers, which require the company to make advance payments for equipment to be purchased in the future. The CFO says that, for various reasons that he did not want to discuss, he will be processing the payments through the operating division of the company rather than the equipment accounting group. Given that the payments will be made through the operating division, they will initially be classified as operating expenses of the company. He indicates that clearly these advance payments for property and equipment should be recorded as assets, so he will be contacting you at the end of every quarter to make an adjusting journal entry to capitalize the amounts inappropriately classified as operating expenses. He advises you that a new account, called Prepaid Equipment, has been established for this purpose. He quickly wraps up the meeting by telling you that it is important that you do not talk about the special project with anyone. You assume he does not want others to become jealous of your new important responsibility. A few weeks later, at the end of the first quarter, you receive a voicemail from the CFO stating, The adjustment that we discussed is $2,000,000 for this quarter. Before deleting the message, you replay it to make sure you heard it right. Your company generates over $8 million in revenues and incurs ~$8 million in operating expenses every quarter, but you have never made a journal entry for that much money. So, just to be sure there is not a mistake, you send an e-mail to the CFO confirming the amount. He phones you back immediately to abruptly inform you, There's no mistake. That's the number." Feeling embarrassed that you may have annoyed the CFO, you quietly make the adjusting journal entry For each of the remaining three quarters in that year and for the first quarter (Q1, Year 1) in the following year, you continue to make these end-of-quarter adjustments. The "magic number, as the CFO liked to call it, was $800,000 for the second quarter (Q2, For each of the remaining three quarters in that year and for the first quarter (Q1, Year 1) in the following year, you continue to make these end-of-quarter adjustments. The magic number, as the CFO liked to call it, was $800,000 for the second quarter (Q2, Year 1), $1,500,000 for the third quarter (Q3 Year 1), $600,000 for the fourth quarter (Q4 Year 1), and $400,000 for first quarter of the following year (Q1 Year 2). During this time, you have had several meetings and lunches with the CFO where he provides you with the magic number, sometimes supported with nothing more than a Post-it note with the number written on it. He frequently compliments you on your good work and promises that you will soon be in line for a big promotion. Despite the CFO's compliments and promises, you are growing increasingly uncomfortable with the journal entries that you have been making. Typically, whenever an ordinary equipment purchase involves an advance payment, the purchase is completed a few weeks later. At that time, the amount of the advance is removed from an Equipment Deposit account and transferred to the appropriate equipment account. This has not been the case with the CFO's special project. Instead, the Prepaid Equipment account has continued to grow, now standing at over $4 million. There has been no discussion about how or when this balance will be reduced, and no depreciation has been recorded for it. Just as you begin to reflect on the effect the adjustments have had on your company's fixed assets, operating expenses, and operating income, you receive a call from the vice president for internal audit. She needs to talk with you this afternoon about a peculiar trend in the company's fixed asset turnover ratio and some suspicious journal entries that you've been making." Required (dollars are in thousands): 1. Complete the blank cells in the first table in the Excel document on D2L to determine what the company's accounting records look like after you made the journal entries as part of the CFO's special project. Note that when you made the journal entry, the Property and Equipment, Net account accumulated the magic numbers each quarter. For example, in Q1 Year 1 the entry to Property and Equipment, Net account results in a balance of $31,000 plus $2,000 or $33,000. In Q2 Year 1 the amount would be $36,000 plus $2,000 from the prior quarter plus another $800 for the current quarter or $38,800 and so on. Included in your workbook spreadsheet (row 3) are the adjustment amounts for each of the 5 quarters so you can refer to those cells when doing your calculations. Also note the operating expenses will be reduced each period by the amount capitalized in that period. For example, in Q1 Year 1 operating expenses would be reduced by the $2,000 and in Q2 Year 1 operating expenses would be reduced by $800. 2. Complete the second table in the Excel document on D2L using both before and after the magic entry numbers. You will need to compute the fixed asset turnover ratio (rounded to three decimal places) for the periods ended Q2Q4 of year 1 and Q1 of year 2. Note you cannot answer this for Q1 of Year 1 because you don't have beginning Property and Equipment, Net. For these same 4 quarters also calculate the net profit margin before and after the magic entry. Discuss in 2-4 sentences in a text box what you notice about the two ratios. You must use the power of Excel in computing your ratios to get a 10 on this assignment. Q2 Year 1 Q1 Year 2 Magic entry amounts by quarter Amounts in thousands of US dollars Q1 Year 1 2,000 Q3 Year 1 1,500 Q4 Year 1 600 800 400 Question #1 (fill in the blank cells) Q1 Year 1 (March 31) Before your After your magic entry magic entry $ 31,000 8,300 7,900 400 Q2 Year 1 (June 30) Before your After your magic entry magic entry $ 36,000 8,500 8,400 100 Amounts in thousands of US dollars Property & Equipment, net Sales Revenue Operating Expenses Income from Operations (before taxes) Q3 Year 1 (Sept 30) Before your After your magic magic entry entry $ 34,000 9,300 8,500 800 Q4 Year 1 Q1 Year 2 (Dec 31) (March 31, Year 2) Before your After your magic Before your After your magic magic entry magic entry $ 35,000 $ 36,000 9,100 8,600 8,800 8,100 300 500 entry entry Question #2 (fill in the blank cells) Q2 Year 1 Q3 Year 1 Q4 Year 1 Q1 Year 2 Fixed Asset Turnover before the magic entries using average Property and Equipment, Net, across the applicable quarters Fixed Asset Turnover after the magic entries using average Property and Equipment, Net, across the applicable quarters Net profit margin before the magic the entries (use income from operations before taxes divided by sales) Net profit margin after the magic the entries (use income from operations before taxes divided by sales) Read and complete the case below. You should complete all questions using one Excel worksheet in the Excel document provided on D2L. Complete the 2 tables provided on the Excel worksheet to answer questions #1 and #2. Use the provided three text boxes on the Excel worksheet to answer questions #3-#5. All your analyses will be done using operating income (not net income after taxes) so you can ignore income taxes. Assume you work as a staff member in a large accounting department for a multinational public company. Your job requires you to review documents relating to the company's equipment purchases. Upon verifying that purchases are properly approved, you prepare journal entries to record the equipment purchases in the accounting system. Typically, you handle equipment purchases costing $50,000 or less. This morning, you were contacted by the executive assistant to the chief financial officer (CFO). She says that the CFO has asked to see you immediately in his office. Although your boss's boss has attended a few meetings where the CFO was present, you have never met the CFO during your three years with the company. You are anxious about the meeting. Upon entering the CFO's office, you are warmly greeted with a smile and friendly handshake. The CFO compliments you on the great work that you have been doing for the company. You soon feel a little more comfortable, particularly when the CFO mentions that he has a special project for you. He states that he and the CEO have negotiated significant new arrangements with the company's equipment suppliers, which require the company to make advance payments for equipment to be purchased in the future. The CFO says that, for various reasons that he did not want to discuss, he will be processing the payments through the operating division of the company rather than the equipment accounting group. Given that the payments will be made through the operating division, they will initially be classified as operating expenses of the company. He indicates that clearly these advance payments for property and equipment should be recorded as assets, so he will be contacting you at the end of every quarter to make an adjusting journal entry to capitalize the amounts inappropriately classified as operating expenses. He advises you that a new account, called Prepaid Equipment, has been established for this purpose. He quickly wraps up the meeting by telling you that it is important that you do not talk about the special project with anyone. You assume he does not want others to become jealous of your new important responsibility. A few weeks later, at the end of the first quarter, you receive a voicemail from the CFO stating, The adjustment that we discussed is $2,000,000 for this quarter. Before deleting the message, you replay it to make sure you heard it right. Your company generates over $8 million in revenues and incurs ~$8 million in operating expenses every quarter, but you have never made a journal entry for that much money. So, just to be sure there is not a mistake, you send an e-mail to the CFO confirming the amount. He phones you back immediately to abruptly inform you, There's no mistake. That's the number." Feeling embarrassed that you may have annoyed the CFO, you quietly make the adjusting journal entry For each of the remaining three quarters in that year and for the first quarter (Q1, Year 1) in the following year, you continue to make these end-of-quarter adjustments. The "magic number, as the CFO liked to call it, was $800,000 for the second quarter (Q2, For each of the remaining three quarters in that year and for the first quarter (Q1, Year 1) in the following year, you continue to make these end-of-quarter adjustments. The magic number, as the CFO liked to call it, was $800,000 for the second quarter (Q2, Year 1), $1,500,000 for the third quarter (Q3 Year 1), $600,000 for the fourth quarter (Q4 Year 1), and $400,000 for first quarter of the following year (Q1 Year 2). During this time, you have had several meetings and lunches with the CFO where he provides you with the magic number, sometimes supported with nothing more than a Post-it note with the number written on it. He frequently compliments you on your good work and promises that you will soon be in line for a big promotion. Despite the CFO's compliments and promises, you are growing increasingly uncomfortable with the journal entries that you have been making. Typically, whenever an ordinary equipment purchase involves an advance payment, the purchase is completed a few weeks later. At that time, the amount of the advance is removed from an Equipment Deposit account and transferred to the appropriate equipment account. This has not been the case with the CFO's special project. Instead, the Prepaid Equipment account has continued to grow, now standing at over $4 million. There has been no discussion about how or when this balance will be reduced, and no depreciation has been recorded for it. Just as you begin to reflect on the effect the adjustments have had on your company's fixed assets, operating expenses, and operating income, you receive a call from the vice president for internal audit. She needs to talk with you this afternoon about a peculiar trend in the company's fixed asset turnover ratio and some suspicious journal entries that you've been making." Required (dollars are in thousands): 1. Complete the blank cells in the first table in the Excel document on D2L to determine what the company's accounting records look like after you made the journal entries as part of the CFO's special project. Note that when you made the journal entry, the Property and Equipment, Net account accumulated the magic numbers each quarter. For example, in Q1 Year 1 the entry to Property and Equipment, Net account results in a balance of $31,000 plus $2,000 or $33,000. In Q2 Year 1 the amount would be $36,000 plus $2,000 from the prior quarter plus another $800 for the current quarter or $38,800 and so on. Included in your workbook spreadsheet (row 3) are the adjustment amounts for each of the 5 quarters so you can refer to those cells when doing your calculations. Also note the operating expenses will be reduced each period by the amount capitalized in that period. For example, in Q1 Year 1 operating expenses would be reduced by the $2,000 and in Q2 Year 1 operating expenses would be reduced by $800. 2. Complete the second table in the Excel document on D2L using both before and after the magic entry numbers. You will need to compute the fixed asset turnover ratio (rounded to three decimal places) for the periods ended Q2Q4 of year 1 and Q1 of year 2. Note you cannot answer this for Q1 of Year 1 because you don't have beginning Property and Equipment, Net. For these same 4 quarters also calculate the net profit margin before and after the magic entry. Discuss in 2-4 sentences in a text box what you notice about the two ratios. You must use the power of Excel in computing your ratios to get a 10 on this assignment. Q2 Year 1 Q1 Year 2 Magic entry amounts by quarter Amounts in thousands of US dollars Q1 Year 1 2,000 Q3 Year 1 1,500 Q4 Year 1 600 800 400 Question #1 (fill in the blank cells) Q1 Year 1 (March 31) Before your After your magic entry magic entry $ 31,000 8,300 7,900 400 Q2 Year 1 (June 30) Before your After your magic entry magic entry $ 36,000 8,500 8,400 100 Amounts in thousands of US dollars Property & Equipment, net Sales Revenue Operating Expenses Income from Operations (before taxes) Q3 Year 1 (Sept 30) Before your After your magic magic entry entry $ 34,000 9,300 8,500 800 Q4 Year 1 Q1 Year 2 (Dec 31) (March 31, Year 2) Before your After your magic Before your After your magic magic entry magic entry $ 35,000 $ 36,000 9,100 8,600 8,800 8,100 300 500 entry entry Question #2 (fill in the blank cells) Q2 Year 1 Q3 Year 1 Q4 Year 1 Q1 Year 2 Fixed Asset Turnover before the magic entries using average Property and Equipment, Net, across the applicable quarters Fixed Asset Turnover after the magic entries using average Property and Equipment, Net, across the applicable quarters Net profit margin before the magic the entries (use income from operations before taxes divided by sales) Net profit margin after the magic the entries (use income from operations before taxes divided by sales)