Please Complete an Excel Spreadsheet Based Off the Following Info

Please complete an Excel spreadsheet based off the following info.

The Narrative

A dual mode DE university plans to offer a graduate course in Higher Education Management. The course is a 3 credit course & requires about 150 hrs of study time (15 wks at 10 hrs per wk). The course will be offered once a yr.

Course management:

The course development process extends over 1 yr; during this time it absorbs 1/2 of the per annum staff-time of a course manager & a full time commitment of 1 secretarial staff.

Over the next 8 yrs, in which the course will be presented, it will continue to absorb management time, albeit at the reduced rate of a quarter; it will continue to require the full time commitment of 1 secretarial staff.

Course material:

The course material is developed by a number of consultants who are renowned experts in the field. It consists of ten study guides of about 50 pgs each and 1 additional reader (150 pgs). Layout and design of the study guides and clearance of copy right will be done in-house.

In addition 3 DVDs will be developed. They include real world case studies of HE institutions with descriptions & data, including interviews with administrators, faculty and students; they also include data & scenarios for fictitious case studies with tasks & assignments as well as video-podcasts with quizzes. Development of the assignments is part of the DVD development. The required person/hrs for the respective specialist you find in the list of ingredients below.

Money is set aside for updating 3 study guides. It the budget simulation it is assumed that the material will be developed in yr 4 & presented from yr. 5 onwards.

Student support:

There will be an online forum for which 30 hrs of tutorial time are calculated. Students can ask questions & can get help on exercises or mock assignments.

Students are supported by a tutor. The tutor will extensively comment on & mark 5 assignments during the course. The assignments will be made available online.

Assessment:

The student will be assessed on the basis of the 5 assignments which will be marked by the tutor.

Envisaged enrollment:

The course is expected to attract 180 students per yr. Money is earmarked to update the course in yr 5 & to present the updated version from yr 6 onwards.

Use the following list for your cost-analysis:

Ingredient / cost list

Input Unit of input Amount of input Cost per unitof input

A COURSE OVERHEADS

Course manager per annum salary 1/4 of full-time (ongoing) $62000

Secretarial support per annum salary 1 full-time (ongoing) $25600

B DEVELOPMENT COSTS (OVERHEADS)

Management (during development phase)

Course manager per annum salary 1/2 of full-time post p.a. over 1 yr of development $62000

Secretarial support per annum salary 1 full-time post p.a. over 1 yr of development $25600

C DEVELOPMENT & PRODUCTION COSTS

Development Print

Authoring study guides per study guide (=50 pages) ten $1800

Preparation of course reader per reader (=150 pages) one $1200

Editing and design per unit of 50 pgs thirteen (which includes the reader with 4 x 50 pp) $750

Development of DVDs

Development of content per hr 360$25

Instructional design per hr 225 $20

Production per hr 180 $15

Development of assignment

Development of assignment per assignment five $750

D MAINTENANCE COSTS (PART OF PRINTED MATERIAL ONLY)

Author per study guide (updating) three $1800

Editing and design per study guide (updating) three $1200

E ANNUAL PRESENTATION COSTS (all per student)

Student support

Marking of assignment per assignment five $40

Tutor per hr of seminar of group size of 25 30 $19

Replication and Distribution

Production - study guide per study guide ten $8.80

Production - course reader per reader one $12.00

Production - DVD per DVD three $16.60

Packaging and postage per mailing two $15.20

F INCOME (per student per credit)

per student per credit (3 )points $380

Classify the different cost items as either fixed or variable costs (matching row number to Fixed or Variable as appropriate) AND as capital or recurrent costs. Give a short explanation of the 2 distinctions.

Those cost to be happened every year is called recurrent, the example is secretarial salary and the capital cost are development cost which is going to happened once and the benefit to be taken for more than a year. All fixed and variable cost are listed in excel sheet.

Calculate the Recurrent Fixed Costs of course overheads (management & secretarial support).

41100

Calculate the aggregate Fixed Costs of Development (FD) & the aggregate Fixed Costs of Maintenance (FM).

143750

Calculate the variable cost per student (V).

115.4

7.Summarize in a short paragraph the reasons for & against annualization

The cost which I recurring and the capital cost to be spread over the number of years to be annualized but the cost which is going to be incurred if the activity happens such as variable cost not to be annualized.

8.Calculate the equation of total costs (TC=F+VxN) using the annualized figure of fixed costs and the total # of students expected over the lifetime of the course.

See excel sheet

9.Draw the respective graph of the TC function.

10.Calculate the equation of average costs

(AC=F/N+V) using the annualized figure for fixed costs and the total # of students

See excel sheet

11. If the student is charged the per student fee specified calculate the break-even point. (Use the equation TC=F+VxN and the income equation: I=SFxN (Income =Student Fee x No of students). The break-even point is N=F/(SF-V)

See the excel sheet

12. Represent the break-even point graphically (overlaying the graphs of TC and I).

See the excel sheet

13. Summarize in a short paragraph why it is believed that the TC and AC equations and the specific cost structure of DE suggests that DE may be more cost-efficient than conventional modes of educational provision.

AS most of the cost are fixed therefore any increase in activity will reduce the per unit cost, only the variable cost will incur on new activity therefore more profit for more students.