COMPSYS is a start-up computer company. This year, the first year of operations, the company expects to reach a Sales Revenue of $750,000. The company expects its sales to grow at a rate of 15% per year. Its Cost of Goods Sold (COGS) is running at 34% of Sales Revenue, and is expected to remain at that rate. Its Selling Costs are 12% of Sales in the first year and are expected to increase by an additional 3% per year after that. Its General and Administrative Costs (including Research and Development) are $410,000 this year, and are scheduled to rise at 4% every year after that. Earnings Before Taxes (EBT) is Sales Revenue less Cost of Goods Sold, Selling Costs, and General and Administrative Costs. Taxes are 25% of Earnings Before Taxes, if the Earnings Before Taxes are greater than zero but less than or equal to $50,000. If earnings are greater than $50,000 per year, then the tax rate is 35%. Use IF statements for computing taxes.

a)        Design and implement a worksheet to make a 5-year income statement projection for COMPSYS starting from this year. First, plan the format and layout of your worksheet areas. You should have separate areas for documenting the spreadsheet, indicating areas of the worksheet, and identifying assumptions with well-labeled separate cells for each of the growth rates and proportional factors. You should have a separate row for each item in the income statement and a separate column for each year. The final row should be for Earnings After Taxes. Use the Fill operation wherever possible.

b)        Calculate the Net Present Value (NPV) of the Earnings After Taxes for the 5 years. Use a Discount Rate of 7%. Provide a label to indicate the results and place the value at the bottom of your worksheet.

c)         Format your spreadsheet in an attractive manner. The first sheet should contain a brief documentation of the software package that you develop. The second sheet should be the EXCEL model. Here, the assumptions (given in the initial paragraph) should be first stated followed by the actual spreadsheet. The third sheet should contain the graphs you generate.

Optional (the following sections: d and e are optional but will be awarded bonus points if completed correctly)

d)        The management feels that the estimate for the first year's Sales Revenue, Cost of Goods Sold, Selling Costs, and General and Administrative Expenses may not be as certain. Since these four items are critical to the success of the operations, the management would like you to perform a sensitivity analysis to see what would the NPV look like when these numbers fluctuate within a range of +/- 20% of the estimate. That is, provide estimates of costs and revenues for this range which should be 80, 85, 90, 95, 100, 105, 110, 115, and 120% of the standard estimates.

(Hint: When you are formulating for these four items, add a certainty factor to the formulas. The certainty factor should not be hardcoded in the formulas. Always keep the input assumptions in a separate area in your worksheet. Use the Create Data Table under Data to perform the sensitivity analysis.)

e)        Produce the following graphs to present to the management:

• Sales and total expenses over the five-year period. Mark where the business starts to make a profit;

• The impact of fluctuations of the initial estimates for the cost of goods sold percentage; sales revenue; and the general and administrative expenses on the NPV.

(Hint: The horizontal axis should be from 80% to 120% of the original estimates, whilst the vertical axis should be the NPV. Remember, the 100% point is your original estimates. The curves for sales, cost of goods sold and the general and administrative expenses should be in the same graph.)

Using financial function to answer the following problems

1. 1) Calculate the present value of an annuity that pays $2,000 each two months for 4 years. The interest is 10% per year and each payment is made at the start of the period.

2. 2) Calculate the interest rate required to save $20,000, over 2 years, with a starting value of $1000, and monthly savings of $800. The payments are to be made at the start of each month.

3. 3) Calculate the number of months required to pay off in full, a loan of $50,000 at a rate of $1,000 per month. Interest is charged at a rate of 4% per year, and the payment to the loan is to be

   made at the end of each month.

4. 4) Calculate the monthly payments required to increase an investment from $2000 to $5,000 over

   a period of 2 years. Interest is paid at a rate of 3.5% per year and the payment into the

   investment is to be made at the beginning of each quarter.

5. 5) A loan of $50,000 which is to be paid off in full after 5 years. Interest is charged at a rate of 5% per year and the monthly payment to the loan is to be made at the end of each month.
   - What is the monthly payment.
   - What is the amount paid in the sixth month only from the loan.

   - What is the interest paid in the sixth month only from the loan. 2

-What is the amount paid during the first six months from the loan. -What is the interest paid during the first six months from the loan.

6) We bought a machine factory at a cost of NIS 20,000 at the end of the second quarter of 2019 and the life span of 6 years and have a scrap value at the end of life NIS 3,500. Calculate the depreciation of this machine within one year after purchase.

7) A machine price of NIS 500,000 and its value in the last useful life (12 years) is estimated at 150,000 NIS. This machine was purchased in the fifth month of 2018. Calculate the depreciation of this machine at the end of its first year (ie the end of 2018 year).

8) The price of a machine is $14000 and the default life is 7 years and the scrap value( salvage) is $2300. Calculate the depreciation of this machine after three years by using sum of years digits depreciation.

I need the answer in excel file :)

*PLEASE SHOW SOLUTIONS USING MICROSOFT EXCEL FORMULAS ONLY, thank you!*

Sophie is 30 years old. However, she is already planning for retirement. She plans on retiring in 35 years when she will be 65 years old. Sophie believes she will live until she is 95.

In order to live comfortably, she thinks she will need to withdraw $15,000 every month during retirement. These monthly withdrawals will be made at the beginning of each month during retirement. Being a lover of animals, Sophie has pledged to donate $500 each quarter to “World Wide Fund for Nature” during her retirement. The first donation will occur one quarter after she retires. In addition, she would like to establish a scholarship at Ryerson. The first payment from the scholarship would be $50,000. Thereafter scholarship payments will be made every year. The first scholarship payment would be made 5 years after she retires. In order to keep pace with inflation, Sophie would like the amount of scholarship payments to increase by 2% each year. She wants the payments to continue after her death, which will last forever. During retirement, Sophie expects to earn 5% per year compounded semi-annually.

She currently has $10,000 in her investment account that earns 7% interest per year compounded monthly. Sophie currently contributes $100 every week to her RRSP. These contributions are made at the end of each week until she retires at 65. Sophie expects to earn 8% per year compounded annually on her RRSP contributions prior to retirement.

a) How much money does she need when she retires at the age of 65?

b) How much money will she have when she retires?

c) In order to finance any shortfall, Sophie will make monthly contributions into a new retirement account. This new retirement account will earn 9% per year compounded semi-annually. The contributions will be made at the end of each month until she retires at 65. How much must she contribute each month to the new retirement account?

Variable and Absorption Costing
Summarized data for 2016 (the first year of operations) for Gorman Products, Inc., are as follows:

a. Prepare an income statement based on full absorption costing.
Only use a negative sign with your answer for net income (loss), if the answer represents a net loss. Otherwise, do not use negative signs with any answers. Round answers to the nearest whole number, when applicable.

b. Prepare an income statement based on variable costing.
Only use a negative sign with your answer for net income (loss), if the answer represents a net loss. Otherwise, do not use negative signs with any answers. Round answers to the nearest whole number, when applicable.

c. Assume that you must decide quickly whether to accept a special one-time order for 1,000 units for $15 per unit.

Which income statement presents the most relevant data? Answerabsorption costingvariable costing

Determine the apparent profit or loss on the special order based solely on these data.
Use a negative sign with your answer if the special order creates an apparent loss. Round answer to the nearest whole number.

$Answer  

d. If the ending inventory is destroyed by fire, which costing approach would you use as a basis for filing an insurance claim for the fire loss? Why?
Select the most appropriate statement.

Absorption costing approach because the cost should include a reasonable portion of fixed manufacturing costs.

Variable costing approach because the cost should include a reasonable portion of fixed manufacturing costs.

Variable and Absorption Costing
Summarized data for 2019 (the first year of operations) for Gorman Products, Inc., are as follows:

a. Prepare an income statement based on full absorption costing.
Only use a negative sign with your answer for net income (loss), if the answer represents a net loss. Otherwise, do not use negative signs with any answers. Round answers to the nearest whole number, when applicable.

b. Prepare an income statement based on variable costing.
Only use a negative sign with your answer for net income (loss), if the answer represents a net loss. Otherwise, do not use negative signs with any answers. Round answers to the nearest whole number, when applicable.

c. Assume that you must decide quickly whether to accept a special one-time order for 1,000 units for $25 per unit.

Which income statement presents the most relevant data? Answerabsorption costingvariable costing

Determine the apparent profit or loss on the special order based solely on these data.
Use a negative sign with your answer if the special order creates an apparent loss. Round answer to the nearest whole number.

$Answer

d. If the ending inventory is destroyed by fire, which costing approach would you use as a basis for filing an insurance claim for the fire loss? Why?
Select the most appropriate statement.

Absorption costing approach because the cost should include a reasonable portion of fixed manufacturing costs.

Variable costing approach because the cost should include a reasonable portion of fixed manufacturing costs.

Summarized data for 2016 (the first year of operations) for Gorman Products, Inc., are as follows:

a. Prepare an income statement based on full absorption costing.
Only use a negative sign with your answer for net income (loss), if the answer represents a net loss. Otherwise, do not use negative signs with any answers. Round answers to the nearest whole number, when applicable.

b. Prepare an income statement based on variable costing.
Only use a negative sign with your answer for net income (loss), if the answer represents a net loss. Otherwise, do not use negative signs with any answers. Round answers to the nearest whole number, when applicable.

c. Assume that you must decide quickly whether to accept a special one-time order for 1,000 units for $15 per unit.

Which income statement presents the most relevant data? Answerabsorption costingvariable costing

Determine the apparent profit or loss on the special order based solely on these data.
Use a negative sign with your answer if the special order creates an apparent loss. Round answer to the nearest whole number.

$Answer

d. If the ending inventory is destroyed by fire, which costing approach would you use as a basis for filing an insurance claim for the fire loss? Why?
Select the most appropriate statement.

Absorption costing approach because the cost should include a reasonable portion of fixed manufacturing costs.

Variable costing approach because the cost should include a reasonable portion of fixed manufacturing costs.

Variable and Absorption Costing
Summarized data for 2016 (the first year of operations) for Gorman Products, Inc., are as follows:

a. Prepare an income statement based on full absorption costing.
Only use a negative sign with your answer for net income (loss), if the answer represents a net loss. Otherwise, do not use negative signs with any answers. Round answers to the nearest whole number, when applicable.

b. Prepare an income statement based on variable costing.
Only use a negative sign with your answer for net income (loss), if the answer represents a net loss. Otherwise, do not use negative signs with any answers. Round answers to the nearest whole number, when applicable.

c. Assume that you must decide quickly whether to accept a special one-time order for 1,000 units for $15 per unit.

Which income statement presents the most relevant data? Answerabsorption costingvariable costing

Determine the apparent profit or loss on the special order based solely on these data.
Use a negative sign with your answer if the special order creates an apparent loss. Round answer to the nearest whole number.

$Answer  

d. If the ending inventory is destroyed by fire, which costing approach would you use as a basis for filing an insurance claim for the fire loss? Why?
Select the most appropriate statement.

Absorption costing approach because the cost should include a reasonable portion of fixed manufacturing costs.

Variable costing approach because the cost should include a reasonable portion of fixed manufacturing costs.

Please answer all parts of the question.

PreviousSave AnswersNext

1) A company has an opportunity to invest in a project which will earn cash flows of $100,000 in the first year, $200,000 in the second year and $350,000 in the third year. If their investor's required return is 12%, what is the most the company should pay for the project? (Enter only numbers and decimals in your response. Round to 2 decimal places.)

2) A business has an project that will bring in annual cash flows of $250,000 for 8 years. The company's investor's require a return of 12%. What is the most the company should pay for the project? (Enter only numbers and decimals in your response. Round to 2 decimal places.)

Part 1: Draw a loanable funds graph in initial equilibrium (show demand and supply intersecting then trace out an equilibrium interest rate and quantity of loanable funds). Clearly label all the parts of the graph. We’ll assume that the demand for loanable funds comes from investment demand and from government budget deficits, and that the supply of funds come from private and foreign savings. (Grade criteria—correct labeling)

Part 2: Suppose that due to the expectation of a slowdown in the economy from COVID - 19, investment spending decreases, private savings remains constant and public savings remains constant. Use the investment-savings identity to predict what will happen to net exports. Show your work and provide a plausible economic explanation (not math explanation) for your answer. (Grade criteria—use of the I = S identity and use of economics in the explanation).

Part 3: Demonstrate on your original graph (i.e. shift either the demand for loanable funds, the supply of loanable funds, or both) what happens to the real interest rate and the quantity of loanable funds when investment spending decreases. Clearly indicate the direction of the shifts and label the final equilibrium interest rate and quantity of loanable funds. Use the concept of scarcity to explain why the interest rates changes as it does. (Grade criteria—correct shift, labeling, economic explanation)

Part 4: Once again, draw a loanable funds graph in initial equilibrium (show demand and supply intersecting then trace out an equilibrium interest rate and quantity of loanable funds). Clearly label the parts of the graph. On this graph demonstrate what happens to the interest rate if investment spending falls and government spending increases to offset the fall in investment spending. Are you certain of your answer? Why or why not?

Part 5: In the real world, it is rare that a single variable will change while all the others are held constant. For each element of the investment-savings identify (I, Sp, Sg, Srow), predict the likely short-run impact of social distancing then use economic concepts to explain. It might help to write out the longer form for Sp, Sg, and Srow. Your answers will take the form of the example below, where you have filled in the blanks. • Investment spending will _____________(increase or decrease) because __________(explanation)

1. If indifference curves cross, this violates the assumption:

A. that consumer preferences are complete.

B. that more of a good is better.

C. of transitivity.

D. that the more a consumer has of a particular good, the less she is willing to give up of something else to get even more of that good.

2. The concept of utility makes it possible to calculate.

A. how much happier one bundle of goods makes a person than some other bundle of goods.

B. which consumer values a particular good more highly.

C. how expensive a bundle is

D. how rational the consumer is

3. Diminishing Marginal Utility represents the concept:

A. Consumers can compare bundles of goods and rank them

B. Non-satiation

C. consumers are rational

D. The more of a particular you have, the less you are willing to give up of something else to get even more of that good

4. What does the Marginal Rate of Substitution describe?

A. How much of one good are you willing to give up to be better off.

B.The utility value when a bundle is consumed.

C.How much of one good are you willing to give up for one more unit of another good and be equally happy.

D. Marginal utility.

5. What kind of goods are most likely to be perfect complements?

A. Orange and apple

B.Pizza and soda

C.Right shoe and left shoe

D. Car and bicycle