Objective:

This assignment assesses students' ability to use critical thinking and MS Excel to solve business problems.

Specifically, students demonstrate effective use of formulas, absolute cell referencing, dynamic charts,

and formatting. It is assumed that students have practical experience with MS Excell $($i$.$e$.,$ IS$52$ Lab$)$ and

an understanding of basic accounting concepts, including cost vs$.$ price, and markup $($i$.$e$.,$ ACCT $4$a and b$).$

Anonymous assessment scores are used as part of CSB$\text{'}$s Student Learning Outcome Plan:

$BA$ Graduates will demonstrate the ability to make data informed judgments utilizing spreadsheets and

other analytical tools and technology.

Concept Refresher:

Cost: the price a business pays for acquiring, producing, or maintaining something. It includes material,

labor, and overhead $($i$.$e$.,$ fixed costs like rent$)$

Cost is not the $($selling$)$ Price your customers pay

Businesses look for ways to lower costs but never to raise costs

Markup: the $\%$ increase over cost a company chooses to impose on the products it sells. Cost $+$ Markup

$=$ Price; Cost $+x\%$ of Cost $=$ Price.

Markup is the difference between a product's selling price and cost as a percentage of the cost. If

a product costs $$100$ to make and you choose a $25\%$ markup the selling price is $$125,$ the price

increase is $$100+($$$10025\%)=$$$125$

Problem Statement:

Your small retail shop specializes in high$-$end mountain bikes. At this time, your store does not have any

other products. You buy them from the distributors and stock them in your shop. You have ONLY ONE

employee besides yourself to help assemble and sell these bicycles. You need to develop a simple

spreadsheet model to help you monitor this business's operations and help you track your projected profit

$($or loss$)$ for the coming year. You would like to know which bikes are the most profitable, and you need

to analyze how other costs such as rent and utilities might affect your overall net profit or loss within

that time frame.

You have decided to develop a model using an MS Excel spreadsheet that will become a part of your

Decision Support System $($DSS$).$ This model should provide an instant answer to the profitability of a given

product at a given cost or selling price. It must also allow you to examine different scenarios whenproduct at a given cost or selling price. It must also allow you to examine different scenarios when

manipulating the product line offered and the set of critical variables such as markup, rent, and utilities.

The model will help you decide the best course of action for next year.

Note: The selling price is based on a percentage markup over the cost and is derived by a formula.

Projected Data:

Your task is to enter that information into a DSS model on MS Excel. The tables below include the input

data you will use to calculate the output data shown as question marks $"?????").$Your Tasks and the Expected Outcome:

Spreadsheet:

Develop a model that will determine the results based on the above data. This must be done by usingChecklist:

Did you enter all input data in each of the tables?

Did you use the absolute cell reference $($e$.$g$.$ using $ sign$)?$

Did you offer three scenario solutions in Excel?

Did you provide a rationale on why your choice of solution is best suited?

In order to receive full credit you had to:

use the correct absolute cell reference formula $($eg$.$ $$A$$$20$ there must be two $ signs, no exception$)$

create a total of four Excel sheets $($original$,$ option $1,$ option $2,$ option $3)$ and in each sheet, there

must be all five tables WITH the correct absolute cell reference formula.

write up a discussion on three alternative options using paragraphs and the final paragraph stating

WHY one option is better than the two other options. The discussion must be elaborated inexplaining why so$,$ not just stating the facts.

Do NOT combine the tables in to one large table