- Details
- Parent Category: Programming Assignments' Solutions

# We Helped With This Excel Programming Assignment: Have A Similar One?

Category | Programming |
---|---|

Subject | Excel |

Difficulty | College |

Status | Solved |

More Info | Excel Assistance |

## Short Assignment Requirements

## Assignment Description

N**ew Perspectives** Excel 2016 | Module 3:
SAM Project 1a

Snackin-Pak

PERFORMING CALCULATIONS WITH FORMULAS AND FUNCTIONs

### GETTING STARTED

· Open the file **NP_EX16_3a_ FirstLastName_1.xlsx**, available
for download from the SAM website.

· Save the file as **NP_EX16_3a_ FirstLastName_2.xlsx **by
changing the “1” to a “2”.

o If you do not see the **.xlsx **file extension in the Save As
dialog box, do not type it. The program will add the file extension for you
automatically.

· With the file **NP_EX16_3a_ FirstLastName_2.xlsx** still
open, ensure that your first and last name is displayed in cell B6 of the

*Documentation*sheet.

o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

### PROJECT STEPS

1. Keith Herman works for Snackin-Pak, a recent start-up that sends boxed sets of packaged snacks to its subscribers on a weekly basis. He keeps track of the company’s sales and inventory, as well as relevant product information, in an Excel worksheet that is updated and distributed weekly.

Switch to the *Orders* worksheet. In cell A2, insert the **NOW** function to record the current
date and time.

2. The data section called Transaction Growth shows how many boxed sets were sold each month. Keith entered a formula into the range O6:O10 to calculate the highest amount of each set sold over the course of the year. He notices that all of the values in the range are 1,408, even though more boxed sets than this were sold in some months.

Edit the formula in cell O6 so
that it uses **relative** references instead of absolute references for the
range B6:M6, and then copy the formula from cell O6 to fill the range O7:O10
without formatting.

3. In cell P6, create a formula using the **MIN** function to
find the lowest number of the Fit Bits boxed set ordered (the range **B6:M6**).
Copy the formula from cell P6 to fill the range P7:P10 without formatting.

4. The worksheet will also show the average number of orders fulfilled for each column in the section above.

In cell B12, create a nested
formula using the **AVERAGE** and **ROUND** functions to first find the
average number of boxed sets sold in January (the range **B6:B9**), and then
round the answer to **0** digits.

Copy the formula from cell B12 to the range C12:N12.

5. The data section called Inventory Tracker shows how many of each
boxed set component has been ordered by the company each month. Use **AutoFill** with the values in the range B15:D15 to extend the series of months to the
range E15:M15.

6. In cell O16, create a formula using the **MAX** function to
find the peak inventory number for the Bars/Clusters component (the range **B16:M16**).
Copy the formula from cell O16 to fill the range O17:O23 without formatting.

7. Switch to the *Product Lookup* worksheet. This worksheet
shows information on the boxed sets and the components that come in each set.

The last person to work on the
workbook mistakenly pasted “one hundred” into cell B10, which created an error
in cell B14. Change the value in cell B10 from “one hundred” to **100** to
eliminate the error message in cell B14.

8. The data section called Boxed Set Information is incomplete.

In cell B16, create a formula
using the **SUM** function to find the total calories in the Munchie Madness
boxed set by totaling the calories from Baked Goods (cell **B5**), Bars/Clusters
(cell **B6**), Granola (cell **B9**), and Popcorn (cell **B11**).

9. In cell C14, create a formula using the **COUNTA** function to
count the number of components in the Fit Bits boxed set (the range **B20:F20**).
Copy the formula from cell C14 to fill the range C15:C17 without formatting to
count the number of components for the rest of the boxed sets.

10. The data section called Product Information Lookup uses a mix of user input cells, output cells, and calculated cells to display various information for a single boxed set. Format the section as follows:

a. Apply the **Input** cell style to cell I5.

b. Apply the **Output** cell style to the range I6:I8.

c. Apply the **Calculation** cell style to cell I9.

11. In cell I6, create a formula using the **VLOOKUP** function to
display the number of calories in the selected boxed set. Use the boxed set
listed in cell **I5** as the lookup value, the range **A14:D17** as the
table array, column number **2** as the column index number, and **FALSE** as the range lookup value. Use **absolute** references for cell I5 and the
range A14:D17.

12. In cell I9, create a formula without using a function that first
adds the selected boxed set’s net weight (cell **I8**) to the packing weight
(cell **E5**), and then divides the value by **16** to show the shipping
weight in pounds.

13. The delivery service used by the company charges a higher rate for packages weighing over 3 lbs.

In cell I10, create a formula
that uses the **IF** function to determine if a boxed set is overweight by
checking if the shipping weight (cell **I9**) is greater than **3**. If
this condition is true, the cell should display **Yes**, and if it is false,
the cell should display **No**. (*Hint*: Use “Yes” for the value if
true, and “No” for the value if false.)

14. The company can make more profit by keeping shipping costs to a minimum. Keith has been put in charge of the packaging redesign project, and he needs to figure out what weight of packaging can keep even the heaviest boxed set from being overweight.

Perform a Goal Seek Analysis
to find a packing weight that will bring the shipping weight of the Sweet Stuff
boxed set down to 2.9 lbs. Use cell **I9** as the cell to set, **2.9** as
the target value, and cell **E5** as the changing cell. Keep the values
generated by the Goal Seek Analysis as the value for cells E5, I9, and I10.

Your workbook should look like the Final Figures on the following pages. (The value in cell E5 generated by the Goal Seek Analysis has been intentionally blurred out in Final Figure 1.) Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Product Lookup Worksheet

Final Figure 2: Orders Worksheet