The Situation

Retail Stores Inc. has a finance department to manage its money. The structure of this company allows each department to have its own bank account from which all expenses, purchases, and charges are deducted. This same account has all revenues and interest deposited into it. As a manager of the Sporting Goods Department you are being required by the Finance Department to provide a summary of the financial situation. This will allow them to better understand and assess the financial policies of the company and make any necessary changes for 2022 and beyond.

Additionally, to increase the market share that Retail Stores Inc. currently have, each department has been given two possible investments that can be made to increase sales, but they require an investment. You will have three alternatives to choose from, either one of the two proposals or do nothing.

The Data

Month	Sales Revenue	Cost of Goods Sold	Operating Expenses		Month	T-bill Yields	Prime rate
January	3,017	2,233	151		January	2.85%	4.25%
February	3,479	2,644	139		February	2.90%	4.25%
March	2,785	2,117	111		March	2.87%	4.25%
April	2,693	1,993	108		April	3.05%	4.75%
May	3,644	2,769	182		May	3.10%	4.75%
June	3,002	2,221	120		June	3.10%	4.75%
July	4,196	3,231	168		July	3.05%	4.50%
August	3,232	2,424	162		August	2.95%	4.50%
September	3,154	2,366	158		September	2.92%	4.50%
October	3,990	2,953	160		October	3.40%	5.00%
November	2,784	2,088	139		November	3.45%	5.00%
December	6,845	5,476	175		December	3.45%	5.00%

Project 1

Investment: $500 Investment in Market Research

Expected Results: Growth in sales of 10% each year, for the next 5 years

Project 2

Investment: $2,000 Investment in creating a new website for online sales

Expected Results: Growth in sales for the next 5 years. 5% for the 1^st, 10% for the second, 20% for the third , and 10% the last two years.

If no project is made, the expected growth in sales is 5% in total.

Important Information

The balance in the bank account of your department in December 31, 2020 was $1,800

Your department has to have a minimum balance of $500

The bank pays interest at 0.85% on the first $1,000 in the account and 1.2% only on the portion above $1,000. Interest is deposited on the last day of each month.

On the last day of each month, the finance department purchases T-bills in the market that will mature by the last day of the next month. The face value of T-bills are bought in denominations of exactly $1,000 in a quantity as permitted by the current balance in the bank account. For example, if the bank account has a balance of $3,600 on March 31, three $1,000 T-bills will be purchased with 30 days left to maturity.

Assume all revenues are deposited to the account at the end of the corresponding month.

Assume all cost of goods sold and operating expenses are deducted at the end of the corresponding month.

You can find the gross profit by subtracting the Cost of Goods Sold (COGS) from the Revenue

You can find the net income by subtracting the Operating Expenses from the Gross Profit and adding any additional income (interest from investments)

Your Tasks

Provide a report that summarizes the financial situation of your department with the following information from January 1st, 2021 to December 31, 2021:

The balance of bank account at December 31.

The total interest earned through T-bill investments

The total interest earned from the bank account.

The gross profit and gross profit margin at the end of the year

The net income of your department at the end of the year

The Net Present Value of each alternative

The choice of the project better suited to your department and your recommendation.

1. Determine the face value of T-bills that can be purchased in each month.

2. Calculate the purchase price of the T-bills using the current market yield and the number of days until the end of the next month. The difference between the purchase price and the face value is the total interest earned for the month by T-bills.

3. Deduct the purchase price of the T-bills from the balance in the account.

4. Examine the account balance.

5. If the balance is positive, calculate the interest earned for the month based on the tiered interest rate structure. This is the total interest earned by the investment for the month.

6. If the balance is negative, charge interest to the account for the month using the interest rate charged by the bank. This is the total interest charged by the operating loan for the month.

7. To figure out the balance at the end of the next month, take the balance from step 4, add the face value of the T-bills that are maturing at the end of the month, add any interest earned from the bank account (step 4a), deduct any interest charged on the operating loan (step 4b), add the revenues for the month, and deduct the expenses and cost of goods sold for the month.

8. Go back to step 1 and repeat for the next month.

9. When all months are complete, calculate the required totals

For the investments you must prepare the data for the next 5 years for each scenario to be able to calculate the Net Present Value, for each alternative. For this analysis the data can be presented and calculated for each year, not monthly.

Calculate the sales revenue for each year based on the growth expected in each scenario

Calculate the annual % over sales for the Cost of Goods Sold and consider this percentage to be fixed in the next five years.

The total amount of Operating expenses will grow 5% each year regardless of the scenario

The additional income received by interest can be considered fixed for the next 5 years.

With this projections, you can estimate the net income of each year for the different scenarios.

Calculate the NPV of each scenario considering a 10% interest rate as the cost of capital

Make the best choice for your department.

The Situation Retail Stores Inc. has a finance department to manage its money. The structure of this company allows each department to have its own bank account from which all expenses, purchases, and charges are deducted. This same account has all revenues and interest deposited into it. As a manager of the Sporting Goods Department you are being required by the Finance Department to provide a summary of the financial situation. This will allow them to better understand and assess the financial policies of the company and make any necessary changes for 2022 and beyond. Additionally, to increase the market share that Retail Stores Inc. currently have, each department has been given two possible investments that can be made to increase sales, but they require an investment. You will have three alternatives to choose from, either one of the two proposals or do nothing. The Data Month January February March April May June July August September October November December Sales Revenue 3,017 3,479 2,785 2,693 3,644 3,002 4,196 3,232 3,154 3,990 2,784 6,845 Cost of Goods Sold 2,233 2,644 2,117 1,993 2,769 2,221 3,231 2,424 2,366 2,953 2,088 5,476 Operating Expenses 151 139 111 108 182 120 168 162 158 160 139 175 Month January February March April May June Prime T-bill Yields rate 2.85% 4.25% Project 1 Investment: $500 Investment in Market Research Expected Results: Growth in sales of 10% each year, for the next 5 years 2.90% 2.87% 3.05% 4.75% 3.10% 4.75% 3.10% 4.75% 3.05% 4.50% 4.50% 4.50% 5.00% 5.00% 4.25% 4.25% July August 2.95% September 2.92% October 3.40% November 3.45% December 3.45% 5.00% Project 2 Investment: $2,000 Investment in creating a new website for online sales Expected Results: Growth in sales for the next 5 years. 5% for the 1st, 10% for the second, 20% for the third, and 10% the last two years. If no project is made, the expected growth in sales is 5% in total. Important Information The balance in the bank account of your department in December 31, 2020 was $1,800 Your department has to have a minimum balance of $500 The bank pays interest at 0.85% on the first $1,000 in the account and 1.2% only on the portion above $1,000. Interest is deposited on the last day of each month. On the last day of each month, the finance department purchases T-bills in the market that will mature by the last day of the next month. The face value of T-bills are bought in denominations of exactly $1,000 in a quantity as permitted by the current balance in the bank account. For example, if the bank account has a balance of $3,600 on March 31, three $1,000 T-bills will be purchased with 30 days left to maturity. Assume all revenues are deposited to the account at the end of the corresponding month. Assume all cost of goods sold and operating expenses are deducted at the end of the corresponding month. You can find the gross profit by subtracting the Cost of Goods Sold (COGS) from the Revenue You can find the net income by subtracting the Operating Expenses from the Gross Profit and adding any additional income (interest from investments) Your Tasks Provide a report that summarizes the financial situation of your department with the following information from January 1st, 2021 to December 31, 2021: a. The balance of bank account at December 31. b. The total interest earned through T-bill investments c. The total interest earned from the bank account. d. The gross profit and gross profit margin at the end of the year e. The net income of your department at the end of the year f. The Net Present Value of each alternative g. The choice of the project better suited to your department and your recommendation. 1. Determine the face value of T-bills that can be purchased in each month. 2. Calculate the purchase price of the T-bills using the current market yield and the number of days until the end of the next month. The difference between the purchase price and the face value is the total interest earned for the month by T-bills. 3. Deduct the purchase price of the T-bills from the balance in the account. 4. Examine the account balance. 5. If the balance is positive, calculate the interest earned for the month based on the tiered interest rate structure. This is the total interest earned by the investment for the month. 6. If the balance is negative, charge interest to the account for the month using the interest rate charged by the bank. This is the total interest charged by the operating loan for the month. 7. To figure out the balance at the end of the next month, take the balance from step 4, add the face value of the T-bills that are maturing at the end of the month, add any interest earned from the bank account (step 4a), deduct any interest charged on the operating loan (step 4b), add the revenues for the month, and deduct the expenses and cost of goods sold for the month. 8. Go back to step 1 and repeat for the next month. For the investments you must prepare the data for the next 5 years for each scenario to be able to calculate the Net Present Value, for each alternative. For this analysis the data can be presented and calculated for each year, not monthly. Calculate the sales revenue for each year based on the growth expected in each scenario Calculate the annual % over sales for the Cost of Goods Sold and consider this percentage to be fixed in the next five years. The total amount of Operating expenses will grow 5% each year regardless of the scenario The additional income received by interest can be considered fixed for the next 5 years. With this projections, you can estimate the net income of each year for the different scenarios. . Calculate the NPV of each scenario considering a 10% interest rate as the cost of capital Make the best choice for your department. The Situation Retail Stores Inc. has a finance department to manage its money. The structure of this company allows each department to have its own bank account from which all expenses, purchases, and charges are deducted. This same account has all revenues and interest deposited into it. As a manager of the Sporting Goods Department you are being required by the Finance Department to provide a summary of the financial situation. This will allow them to better understand and assess the financial policies of the company and make any necessary changes for 2022 and beyond. Additionally, to increase the market share that Retail Stores Inc. currently have, each department has been given two possible investments that can be made to increase sales, but they require an investment. You will have three alternatives to choose from, either one of the two proposals or do nothing. The Data Month January February March April May June July August September October November December Sales Revenue 3,017 3,479 2,785 2,693 3,644 3,002 4,196 3,232 3,154 3,990 2,784 6,845 Cost of Goods Sold 2,233 2,644 2,117 1,993 2,769 2,221 3,231 2,424 2,366 2,953 2,088 5,476 Operating Expenses 151 139 111 108 182 120 168 162 158 160 139 175 Month January February March April May June Prime T-bill Yields rate 2.85% 4.25% Project 1 Investment: $500 Investment in Market Research Expected Results: Growth in sales of 10% each year, for the next 5 years 2.90% 2.87% 3.05% 4.75% 3.10% 4.75% 3.10% 4.75% 3.05% 4.50% 4.50% 4.50% 5.00% 5.00% 4.25% 4.25% July August 2.95% September 2.92% October 3.40% November 3.45% December 3.45% 5.00% Project 2 Investment: $2,000 Investment in creating a new website for online sales Expected Results: Growth in sales for the next 5 years. 5% for the 1st, 10% for the second, 20% for the third, and 10% the last two years. If no project is made, the expected growth in sales is 5% in total. Important Information The balance in the bank account of your department in December 31, 2020 was $1,800 Your department has to have a minimum balance of $500 The bank pays interest at 0.85% on the first $1,000 in the account and 1.2% only on the portion above $1,000. Interest is deposited on the last day of each month. On the last day of each month, the finance department purchases T-bills in the market that will mature by the last day of the next month. The face value of T-bills are bought in denominations of exactly $1,000 in a quantity as permitted by the current balance in the bank account. For example, if the bank account has a balance of $3,600 on March 31, three $1,000 T-bills will be purchased with 30 days left to maturity. Assume all revenues are deposited to the account at the end of the corresponding month. Assume all cost of goods sold and operating expenses are deducted at the end of the corresponding month. You can find the gross profit by subtracting the Cost of Goods Sold (COGS) from the Revenue You can find the net income by subtracting the Operating Expenses from the Gross Profit and adding any additional income (interest from investments) Your Tasks Provide a report that summarizes the financial situation of your department with the following information from January 1st, 2021 to December 31, 2021: a. The balance of bank account at December 31. b. The total interest earned through T-bill investments c. The total interest earned from the bank account. d. The gross profit and gross profit margin at the end of the year e. The net income of your department at the end of the year f. The Net Present Value of each alternative g. The choice of the project better suited to your department and your recommendation. 1. Determine the face value of T-bills that can be purchased in each month. 2. Calculate the purchase price of the T-bills using the current market yield and the number of days until the end of the next month. The difference between the purchase price and the face value is the total interest earned for the month by T-bills. 3. Deduct the purchase price of the T-bills from the balance in the account. 4. Examine the account balance. 5. If the balance is positive, calculate the interest earned for the month based on the tiered interest rate structure. This is the total interest earned by the investment for the month. 6. If the balance is negative, charge interest to the account for the month using the interest rate charged by the bank. This is the total interest charged by the operating loan for the month. 7. To figure out the balance at the end of the next month, take the balance from step 4, add the face value of the T-bills that are maturing at the end of the month, add any interest earned from the bank account (step 4a), deduct any interest charged on the operating loan (step 4b), add the revenues for the month, and deduct the expenses and cost of goods sold for the month. 8. Go back to step 1 and repeat for the next month. For the investments you must prepare the data for the next 5 years for each scenario to be able to calculate the Net Present Value, for each alternative. For this analysis the data can be presented and calculated for each year, not monthly. Calculate the sales revenue for each year based on the growth expected in each scenario Calculate the annual % over sales for the Cost of Goods Sold and consider this percentage to be fixed in the next five years. The total amount of Operating expenses will grow 5% each year regardless of the scenario The additional income received by interest can be considered fixed for the next 5 years. With this projections, you can estimate the net income of each year for the different scenarios. . Calculate the NPV of each scenario considering a 10% interest rate as the cost of capital Make the best choice for your department