Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

Part A:
 
Introduction:  
 
Christina bought into a pharmacy business that provides pharmaceutical services only for those patients qualifying for the full concession price for medications under the Pharmaceutical Benefits Scheme (PBS). The pharmacy also sells regular retail products complementary to such a business. 
 
Because the Australian government is currently implementing changes to the PBS Christina would like to model the monthly profit/loss position of the business, using approximate figures from the previous year (i.e. rounded). She has asked you to help her out!
 
Requirement:       
 
Your task is to design a spreadsheet projection for Christina using the data she has provided. It will list anticipated income and costs arriving at appropriate totals that show the monthly profit profile for a period of one calendar year.  The profit/loss figure for each month should be find outd as well as a cumulative balance over the twelve months. Allow maximum flexibility so that changes can easily be made. Your layout of the spreadsheet should be in a neat, easy to follow manner.
 
The following lookup table is to be used in your solution and shows the anticipated sales of prescriptions and retail items, the government refunds along with the wholesale costs for prescription and retail products.

895_wholesale costs.jpg

All prescriptions are sold at the full concession PBS price of $5.90. Other income includes a single payment of $2000 at the beginning of the year for provision of Professional Services, the monthly retail sales and the government refunds as provided in the lookup table.

Costs included in the monthly profit profile in addition to the wholesale costs of prescription and retail stock are fixed costs of $12,000 pa and variable costs factored at 5% of the business’ monthly income. When the number of script sales exceeds 5000 in a month an extra staff member is casually hired for the following month costing $2,000. After June this amount will rise by 2.5% when a national wage increase is implemented.

The income and costs to be included in the projections in are summarised below and should be included in a key values lookup table in your solution. 
 
Income
Professional Services                         $2000 per year
Cost of a prescription (Concessional)    $5.90 per medication
 
Costs    
Fixed cost per   annum                         $12,000.00   
Variable costs per month                       5% of total monthly income
Extra staff wages (If sales > 5000)         $2,000.00 per month
Wages increase                                   2.5% of monthly salary 
 
Hand in:
 
1) A copy of the display view of your projection (including the key values and lookup table). This should be printed with gridlines and row & column headings. To do this, go to the Page Layout tab  and under the Print Titles option of Page Setup group, ensure Gridlines and  ‘Row and column headings’ are checked for  the Print option under the Sheet tab.
 
2) A copy of the formula  view  of your  projection (not  including the key and data tables). Choose the Formulas tab and click on the Show Formulas option found in the Formula Auditing group  (You may then need to widen some columns.) You only need to print a section (selection) of your worksheet to show an ex of each of the general formulae used.  
 
3) Two separate graphs, one showing any profit or loss made over the  twelve months, another showing the breakdown of one month’s expenses. 
 
4) The summary sheet for a  ‘What If’ analysis using Excel’s  Scenario Manager to investigate:
 
a) the effects of Fixed costs rising to $20,000 and Variable costs  increasing  to 12%
b) the effects of discovering that Government refunds are about $20, 000 lower than expected and are as given below:

1299_govt refunds.jpg

The resultant table should be formatted so that results can be easily interpreted.

Part B: Calculating Dosages
 
Introduction:

For the antibiotic Gentamicin Mary Boromeo, Community Pharmacist describes:

“Gentamicin is a powerful antibiotic, used almost exclusively in the hospital setting when lesser drugs have failed to control the infection eg in multi resistant bacterial strains. It is powerful but has toxic side effects and only a narrow therapeutic range - ie the gap between ineffective dose and over dose is small, hence it is imperative the correct dose is given for safe effective outcomes.

The individual's kidney function gives us access to determining the appropriate dose for each person. Creatinine is expelled from the body via the kidneys, its level in the blood (serum) will indicate kidney function - good or poor - so its rate of clearance (pharmacokinetics!) can be used to establish safe drug doses. You find out the clearance by that formula and then use the spreadsheet to determine the dose.

The mathematical formula includes a mathematical constant and a serum reading for creatinine (easily measured by blood test) which is then mathematically adjusted for age, weight and sex. (pharmacodynamics at work). In these "nasty" drugs it is imperative the dose suits the individual hence the dosing spreadsheet.”

Requirement:

You are to create the lookup data tables and input output tables for steps 1 and 2 of the process for calculating dosages for the administration of Gentamicin to adults, i.e. calculating the body weight and applying the creatinine clearance estimation formula. The steps (after obtaining the patient’s name, sex and date of birth) are:
 
Step 1: IDEAL BODY WEIGHT CALCULATION

• Obtain patient's weight (kg) and height (feet)
• find out Ideal Body Weight (IBW): IBW = (males: 50 kg, females: 45.5 kg + 2.3 kg for every inch over 5 feet)
• Use ideal body weight (IBW) if the patient is obese, actual body weight if underweight.
 
Step 2: CREATININE CLEARANCE ESTIMATION

•  Obtain serum creatinine (micro mol/L)
•  find out creatinine clearance: CREATININE CLEARANCE ESTIMINATION = (140 - age (years)) * ideal body weight (Kg) * sexFactor (1.23 male, 1.04 female))/Serum Creatinine (micro mol/L)
 
Your input/output tables could look something like (designer’s licence allowed!) below:
 
For your input table apply validation to the input cells to limit the values entered to valid values (30 character limit for name), “Male” or “Female” values for sex and appropriate ranges for age, weight and serum creatinine.
 
You will create two lookup tables: an “Ideal body weight” and a small “Sex factor” table.

Your “Ideal body weight” lookup data table should look like the ex from the lecture.

Your sex factor lookup table should need no explanation! These tables should be placed on a separate sheet of your Excel workbook.
 
Hand in:
 
1) A copy of the display of your data tables for “Ideal body weight” and “Sex factor”. This should be printed with gridlines and row & column headings. To do this, go to the Page Layout tab and under the Print Titles option of Page Setup group, ensure Gridlines and ‘Row and column headings’ are checked for  the Print option under the Sheet tab.
 
2) A copy of the display view of your input and output tables with values displayed in the cells, i.e. manually entered values for the input table, generated values from formulas for the output table. Use the above display view  input/output tables as a guide.
 
3) A copy of the formula view of your input and output tables. Choose the Formulas tab and click on the Show Formulas option found in the Formula Auditing group (You may then need to widen some columns.)  
 
4) Screen shots of validation applied to the input cells: name, sex, date-of-birth, height, weight  and serum creatinine.  For ex, the following shows data validation applied to the cell for entering the sex choice.

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M9941

Have any Question? 


Related Questions in DBMS

Lab 2 modifying a database design in visiothis assignment

Lab 2: Modifying a Database Design in Visio This assignment contains two Sections: Visio Diagram and Design Summary. You must submit both sections as separate files in order to complete this assignment. Use "Appendix A: ...

Use sql management studio to create a database called

Use SQL Management Studio to create a database called ITCO630_P1.MDF. Be sure to store the database in a location that you will remember. Add the following tables and data to the database. Use the appropriate field types ...

Fundamentals of databases assignment 1 -q1 explain the

Fundamentals of Databases Assignment 1 - Q1. Explain the distinction between total and partial constraints. Q2. Explain the difference between a weak and a strong entity set. Q3. Explain the distinction between disjoint ...

Dfd1 draw a context diagram for the fiat for football

DFD 1. Draw a Context Diagram for the FIAT for Football process. 2. Draw a top level Data Flow Diagram for the FIAT for Football method. 3. Blow up any one non-trivial process into a first level Data Flow Diagram.

How up-to-date should data warehouse information

HOW UP-TO-DATE SHOULD DATA WAREHOUSE INFORMATION BE? Information timeliness is a must in a data warehouse-old and obsolete information leads to poor decision making. Below is a list of decision-making processes that peop ...

Database management assignmentyou have been hired by

Database Management Assignment You have been hired by National Bank as their new database administrator. Your first task is to upgrade the bank's outdated computer system and implement a relational database management sy ...

1 draw and explain erm diagram andnbsp explain what is

1. Draw and explain ERM diagram and  explain what is REM 2. Explain what is Normalization . what is 0NF,1NF, 2NFand ,3NF and explain what is in the tables Entity Relationship Modeling and Normalization: 1 System Analysis ...

Suppose that you are the database developer for a local

Suppose that you are the database developer for a local college. The Chief Information Officer (CIO) has asked you to provide a summary of normalizing database tables that the IT staff will use in the upcoming training s ...

Discussionyou have been asked to help a critical

Discussion You have been asked to help a critical infrastructure company in analyzing the privacy and security of a SCADA system. Conduct research on the topic and answer the following: 1. How would you describe general ...

Movie theater database entity-relationship modeldevelop a

Movie Theater Database: Entity-Relationship Model Develop a Microsoft Access database based upon the below Entity Relationship Model. Be sure to include tables, fields, keys, relationships, and data in your database. You ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

WalMart Identification of theory and critical discussion

Drawing on the prescribed text and/or relevant academic literature, produce a paper which discusses the nature of group

Section onea in an atwood machine suppose two objects of

SECTION ONE (a) In an Atwood Machine, suppose two objects of unequal mass are hung vertically over a frictionless

Part 1you work in hr for a company that operates a factory

Part 1: You work in HR for a company that operates a factory manufacturing fiberglass. There are several hundred empl

Details on advanced accounting paperthis paper is intended

DETAILS ON ADVANCED ACCOUNTING PAPER This paper is intended for students to apply the theoretical knowledge around ac

Create a provider database and related reports and queries

Create a provider database and related reports and queries to capture contact information for potential PC component pro