# Project For Management and Accounting

QUESTION 1 (40 marks)

You have been approached to invest in an online store that rents DVD and delivers them

to customers. You have researched the business and want to forecast profits for the next

four years.

The online store offers the following plans for the customers.

1. 10.95\$ per month. (1 DVD out at a time) – Plan A

2. 18.95\$ per month. (2 DVD out at a time) – Plan B

3. 24.95\$ per month. (3 DVD out at a time) – Plan C

4. 36.95\$ per month. (6 DVD out at a time) – Plan D

The cost of having a customer for each monthly plan is expected to be as follows:

Plan Customers

A 2.45\$

B 4.90\$

C 7.00\$

D 12.00\$

In the first year, you expect to be able to attract the following number of customers to

each plan:

Plan Customers

A 2000

B 1500

C 1000

D 500

In each of the following 3 years, you expect the number of customers to increase

according to the following table.

Plan Year 2 Year 3 Year 4

A 20% 15% 15%

B 20% 15% 20%

C 15% 10% 10%

D 5% 5% 3%

The average cost for all the plans per customer will decrease each year by 5%.

Your office rent in the first year is \$30,000. However, since you have to expand to

support the growth of business your rent will increase at least 5% per year.

least 4000\$ per year to upgrade your servers so you can handle the extra traffic of your

website. Your clerical costs will be \$10,000 in the first year and increase 5% in each of

the subsequent years. Office supplies will cost you about 5,000\$ per year.

You plan to invest heavily in advertising in the first year, spending an amount equal to

25% of sales. Thereafter, you will spend \$4,000 plus 12% of sales. All other costs will

amount to \$16,000 plus 3% of sales in each year.

Income tax is payable only if your annual profit exceeds \$75,000. If it does, the tax

payable is 55% of the amount of profit exceeding \$75,000.

1a.

&middot; Include your first and last names as part of the company name in the title of the

worksheet.

&middot; Immediately after the title, specify the number of customers of each plan in the

first year and the annual growth rate for the next 3 years as assumptions whose

values are referenced in the remainder of the worksheet. Therefore, when you

change either or both of these values, all subsequent references to them will also

be changed.

&middot; When calculating the number of accounts per plan sold in years subsequent to the

first, round the result to the nearest integer.

&middot; Note that you are being marked on the appearance, as well as the accuracy, of

your worksheet. Make sure that it has a meaningful title, meaningful row and

column labels, suitably formatted cells and printed gridlines.

1b. A revised worksheet that shows the results of the number of accounts in the first year

and growth according to the following tables. This scenario reflects the optimistic

Plan Customers

A 2500

B 2000

C 1000

D 900

Plan Year 2 Year 3 Year 4

A 25% 18% 18%

B 25% 18% 22%

C 20% 14% 12%

D 10% 8% 5%

1c. A revised worksheet that shows the results of the number of accounts in the first year

and growth according to the following tables. This scenario reflects the pessimistic

Plan Customers

A 1500

B 1000

C 500

D 200

Plan Year 2 Year 3 Year 4

A 10% 10% 12%

B 20% 10% 15%

C 5% 8% 6%

D 2% 3% 1%

1d. A worksheet resulting from the use of Goal Seek to determine how many accounts

must be sold in the first year of the most likely forecast to yield an after tax profit of

\$500,000. To reduce the number of possibilities and uncertainty about number of

accounts sold per plan always consider that number accounts sold per plans relative to

plan D are:

A = 4D

B = 3D

C = 2D

QUESTION 2 (10 marks)

Mr Jones who is a software analysist at IBM has N number of shares of Apple stock. The

apple stock is currently worth \$M. The share cost might increase/decrease Y% during a

year.

Write an algorithm that gets N, M and Y as input and calculates Mr. Jones’ total worth of

Apple shares and his benefit (or loss).

Inputs

Number of Shares

Current price

Growth Rate

Outputs:

Loss_Benefit

QUESTION 3 (10 marks)

Translate the following algorithm into Visual Basic.

Name: myCompanyProfit

Givens: Name, Employee1_Salary, Employee2_Salary, Employee3_Salary,

Intermediate: Total

Results: Profit_Loss

Definition: Profit_Loss = myCompanyProfit(Name,Employee1_Salary,

Method

Get Name

Get Employee1_Salary

Get Employee2_Salary

Get Employee3_Salary

Get OfficeCost

Get Revenue

Profit_Loss = Revenue – (Employee1_Salary + Employee1_Salary + Employee1_Salary

If Profit_Loss &gt; 0

If Profit_Loss &gt; 70% of Revenue

Give Name “, your company is awesome!!”

Else

Give Name “, your profit is modest”.

Else

Give Name “, you are not making any profit!!”

Deliverable List

1 Excel Work Book (7 Worksheets, 2 of them are Module Sheets)

Title Page with Signature file from Assignment 0

Question 1a

Question 1b

Question 1c

Question 1d

Question 2 algorithm (module sheet)

Question 3 in VB (module sheet)

( 0 comentários ) Amritsar, India

ID do Projeto: #528301

## 5 freelancers estão ofertando em média \$111 para este trabalho

Khurramz

\$50 USD in 2 dias
(16 Comentários)
3.9
mantripti

\$125 USD in 3 dias
(9 Comentários)
3.3
punsha71

i am keen to do this project for you ..

\$30 USD in 3 dias
(0 Comentários)
0.0
jabinjack

Experience wise I am used to do this kind of work since 1998. If you give me a chance to do this job I am very much ready.

\$250 USD in 5 dias
(0 Comentários)
0.0
Shikha80in

Hi Vijay, I am an online tutor for these subjects and can manage it quite easily. If interested, please contact me for further details. Thanks, Shikha.

\$100 USD in 4 dias
(0 Comentários)
0.0